SharePoint 2010 External lists with BCS for external LOB systems

By:   |   Comments   |   Related: > SharePoint


Problem
SharePoint 2007 enabled the ability to read external LOB (Line Of Business) systems like SQL Server, Web services or .Net Assemblies, but in SharePoint 2010 we'll see how to create full CRUD (Create - Read - Update & Delete) operations.
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:

business connectivity services

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":

employees

Open SharePoint Designer 2010, then open the SharePoint Site:

open 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":

external content type
content type information

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

sql server

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

sql server connection

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":

data source explorer
create all operations

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

operation properties

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

parameters configuration

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":

create all operations

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

operation properties

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

parameters configuration
filter parameters configuration

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

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:

create lists

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

create list

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

view all site content

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

books

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

business data connectivity

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:

sharepoint

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

web applications

Click "Business Data Connnectivity Service":

business data connectivity service

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

set permissions

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

set object permissions

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

name

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

view item
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Hesham Saad Hesham Saad

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

View all my tips



Comments For This Article

















get free sql tips
agree to terms