Sharepoint 2010 Business Data Connectivity Service 101

If you are looking on how to create a 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 on how to use BDC Services on 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 lets go to the details

Step 1 : Create an External Content Type

Now fire up your Sharepoint Designer 2010 and if you dont have one you can download here, once your 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 is a .Net Type which allows you to connect to a .Net Assembly which might be a third party or you developed 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 wont be slow loading the sharepoint page specially if you have lost 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 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 availabe on the External List Library.  Click your external list to view its properties.

At this point it had created the needed forms which is the Display Item, Display List, Edit and New Form.

Thats it you had 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 on 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 whats 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 its mostly configuration, imagine doing this as a separate application you will definitely code a lot of stuff and you wont 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 compnay 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 is free.  So go ahead give it a try and definitely it will increase your productivity and concentrate on more gruniter tasks.