If you are looking at how to create CRUD Forms easily in Sharepoint without programming, trying to create a Sharepoint List that grabs and saves to a SQL Server Database or even learning how to use BDC Services in Sharepoint 2010, then look no further as this article will discuss the quickest way to do those scenarios.
This will be a visual guide on how to configure and use Sharepoint 2010 Business Data Connectivity Services in the simplest and straightforward way, so Advanced topics might not be covered as this will just run you through the basics, but if you have some questions, I am happy to assist.
So let's go to the details.
Step 1: Create an External Content Type
Now fire up your Sharepoint Designer 2010 and if you don't have one, you can download it here, once you are in, open your Team Site, then choose external content type, then create a new one:
Once you click that, it will open the Content Type Definitions, now place your Content Type Name, then click External System to define your Database Connection:
Now click Add Connection:
It will ask you different data source types, you can choose from .Net Type, WCF Service and SQL Server. We are now interested in SQL Server which lets you connect to a SQL Server Database. But before we move ahead, I will discuss a bit on the other two types as this adds flexibility on the Data Connections you might need later on. First it is a .NET Type which allows you to connect to a .NET Assembly which might be a third party or you developed it on your own and the next type is a WCF Service which lets you connect to a Webservice or a Metadata Exchange.
Ok, back to choosing SQL Server Connection, choosing it will prompt you to set the connection parameters:
Once you set it, your database objects will now be available on the Data Source Explorer.
Now choose the Table you need to expose and right click on it and create the operation you need. In this case, we select all operations so it will do read item, read list, create, update and delete in one shot.
Now, it will ask you some Operation Properties and Parameters, we will just click through next during the whole process.
Now the Parameters Configuration, we will just click next for now, but in short this window will:
- configure the Data Source Elements on that table
- require a field on save
- define the field as a time stamp
- show the field on the picker if you use this as a reference field on another Sharepoint list
- and lots more
Now the Filter Parameter - this will let you filter the results the external list is grabbing from SQL so that it won't be slow loading the Sharepoint page especially if you have lots of rows. But for now, we also click next and let the default values handle it.
Now at this point, you have created the needed operations for the table you have chosen.
Now save your work by clicking that diskette icon on top:
Step 2: Create an External List
Next, you will be exposing that table operations you just configured through an External List. First is you have to go to your navigation and choose List and Libraries, then choose External List
Once you click that, you will be presented with an External Content Type Picker and you will be able to view the Content Type you created a while ago on step 1, choose that and hit OK:
Now name your new external list and give it a description:
At this point, it will now be available on the External List Library. Click your external list to view its properties.
At this point, it has created the needed forms which are the Display Item, Display List, Edit and New Form.
That's it! You have created your forms, but wait, you still need to give permissions to the BDC you had created a while ago to perform the needed operations.
Step 3: Configure Business Data Connectivity Service (BDC) Permissions
Now, you need to assign permissions on the Business Data Connectivity Service you just created, and you can do that by going to Sharepoint 2010 Central Administration. Once there, go to Application Management, then Manage Service Applications.
Choose Business Data Connectivity:
And choose the Content Type you created in Step 1, then set the permission:
It will now open a window to let you choose users to give permission to, it can come from an Active Directory or Forms Authentication User, you can also assign different permisssions per user or group:
Choose what's applicable to you and hit OK. Now, everything is ready.
Step 4: Try Out What You Had Created
Now go to your Team Site, then on Lists, and you should see the External List you just created and this works similar to a normal Sharepoint list where you can add, edit and delete items.
Until this point, you haven’t coded anything and it's mostly configuration, imagine doing this as a separate application. You will definitely code a lot of stuff and you won't just worry about the forms, but as well as the design and security aspects of it. You also need Visual Studio or any IDE that can develop applications like such, but with Sharepoint 2010 everything is handled for you like the security, design, content management and most importantly, the operations you needed for a table and best of all, if you or your company is stingy you can do it all for free as there is a free version for Sharepoint which is the Sharepoint Foundation 2010 and also the Sharepoint Designer. So go ahead, give it a try and definitely it will increase your productivity and you can concentrate on more gruniter tasks.
