Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
>>> >> > Make the most of SQL Server Integration Services Script Components < << <<<
 

SharePoint 2010 External lists with BCS for external LOB systems


By:   |   Read Comments   |   Related Tips: > 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
all operations

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


Last Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools