SharePoint 2010 External lists with BCS for external LOB systems

By: Hesham Saad   |   Updated: 2011-09-08   |   Comments   |   Related: > SharePoint

Problem

Solution

Business Connectivity Services (BCS) in SharePoint 2010 is all about connecting to external data. BCS enhances the SharePoint platform’s capabilities with out-of-box features, services and tools that streamline development of solutions with deep integration of external data and services.

A quick overview about key components that comprise BCS is shown in the "BCS Architecture" diagram below:

Let's see how to connect to a SQL Server LOB system and create full CRUD operations via BCS:

Assume that we have a database called "EmpDep" with related tables "Departments & Employees":

Open SharePoint Designer 2010, then open the SharePoint Site:

Click on the "External Content Types" item in the left hand side tool pane, then click on New -> "External Content Type" in the Ribbon and type "Name: EmpDep , Display Name: EmpDep & Click on the 'Click here to discover external data sources and define operations'  link of the "External System" field":

Select "SQL Server" as a "Data Source Type" at the "External Data Source Type Selection" dialog :

Type "DataBase Server & DataBase Name" values and keep the "Connect with User's Identity" option selected and click OK:

We can see now our database tables "Departments & Employees" listing under the "Data Source Explorer" tab. Let's create our full CRUD operations by right clicking on the "Departments" table and select "Create All Operations":

Now we can see the following operations "Create , Read Item , Update , Delete & Read List" , Click Next:

Select the "DepID" column and check "Map to Identifier" to be our identifier. Then click Next. Then click Finish:

Repeat the above steps for the "Employees" table. Let's create our full CRUD operations by right clicking on the "Employees" table and select "Create All Operations":

Now we can see the following operations "Create2 , Read Item2 , Update2 , Delete2 & Read List2" , Click Next:

Select the "EmpID" column and check "Map to Identifier" to be our identifier. Then click Next. Then click Finish:

Now we can see that all objects are created and configured with "External Content Type Operations" :

Now let's create an "External List" that will associate with the "External Content Type" via clicking on the "Create Lists & Form" button at Ribbon:

Type "List Name: EmpDepExternalList , Read Item Operation: Read Item , System Instanse: EmpDep" at the "Create New External List" section then click OK:

Open the SharePoint site and check the created External List "EmpDepExternalList". Click on the "Site Actions" menu then click on "View All Site Content":

Here we can see our created external list "EmpDepExternalList" under Lists section:

When we click on the "EmpDepExternalList" external list we'll see the error message below:

The "Access denied" error is a normal and logic error raised because we need to configure the permissions for the generated data catalog of the External content type.

In order to fix this, open SharePoint Central Administration:

Click "Manage Service applications" at "Service Applications" under the "Application Management" section:

Click "Business Data Connnectivity Service":

Right click on "EmpDep" and select "Set Permissions":

Add "All Authenticated Users" from the people picker and check all permissions as shown below. Then click OK:

If we return back to the "EmpDepExternalList" external list we can see the items without any access denied error:

Also, we can click on any item and see item details in a popup window:

Next Steps

• Using Business Connectivity Services in SharePoint 2010

About the author

Hesham Saad

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips