Business Data Connectivity (BDC) service is one of the service applications of SharePoint 2010 as the innovated version of the Business Data Catalog in Microsoft Office SharePoint Server 2007. It is designed to associate business application data from various sources to SharePoint sites and lists.
This piece will show us on how to setup Business Data Connectivity with External Content Type through Microsoft SharePoint Designer 2010.
Yes, you have heard me right! Through SharePoint Designer. Below are the steps:
1. Let us start by preparing our database. For our purpose, let use the AdventureWorks 2008R2 SR1 sample database.
2. The next step is to assure that our Business Data Connectivity is properly configured and started.
3. We will now open our SharePoint Designer and create an External Content Type.
We might encounter the error below if we have not properly configured our Business Data Connectivity Service.
4. Under the External Content Type Information section, click on the External System link to discover data sources.
5. On the next window, let us click the Add Connection button to define our Data Source.
6. Then, let us define our External Data Source Type and our SQL Server Connection. Since we have used the AdvantureWorks sample database in this piece, we will select SQL Server as our external data source type and AdventureWorksLT2008R2 for our database name.
7. Now in our Data Source Explorer, we will select the Customer table and click Create All Operations. This will provide us read item, read list, create, update, and delete operations.
8. At this point, in our Central Administration > Service Application > Business Data Connectivity Service, we will set the permission of our external content type.
9. Finally, let us open our SharePoint site, add an External Content Type list, and point it to the external content type we have just created. And we will have now a SharePoint list connected to our external data source.
Hope this would help!