How to create Business Connectivity Services (BCS) using SharePoint Designer 2010

Introduction:

Business Connectivity Services (BCS) is a new service introduced with SharePoint 2010 to allow SharePoint sites to connect to and manipulate external data. SharePoint 2007 had a similar facility in the form of Business Data Catalog (BDC) which made external data available within its site. However, a major problem with BDC was the difficulty in creating solutions as there was no support in the 2007 designer.  Most BDC solutions were simply for accessing external data, manipulating external data sources was extremely difficult.

With SharePoint 2010, BCS ships with out-of-box features such as solutions, services, and tools which may connect to external data an easy task. Whether you want to retrieve Outlook contacts in a list offline or edit the contents of your document file or share your excel sheet online or reuse data from dynamic InfoPath forms or just update your business presentation, BCS enables deep content sharing, editing and integration in SharePoint 2010 with SharePoint Designer and Visual Studio tools.

SQL Database:

First we need to create a sample database in SQL which is going to be connected to the SharePoint. Simply create a new database in your SQL Server and have it filled with some sample data. In my case, I have created a SQL database, called Test. In the database Test I have added one table called Employee_Details.

See the details of the table in the below figure.

Employee_Details

Emp Name	Emp ID	Age	Department	Location
Anandh	206571	24	MOSS COE	Bangalore
Ram	206540	26	TESTING	Pune
Siva	206580	25	CAPTIVA	Chennai

 

Creating an External Content Type:

Before we integrate external data to the SharePoint, we need to create an external content type.  You can use the SharePoint site or SharePoint designer to create an external content type. In this example, we will use SharePoint Designer 2010. The most effective and easy way to set up a simple BCS connection is to use SharePoint Designer 2010. Following are the steps involved to create an External Content Type in SharePoint Designer 2010.

Steps Involved:

1. Open the SharePoint Designer 2010.
   
   
   
   
2. Click Open Site; enter the Site Name in the Open Site dialog box.
   
   
   
   
3. Select External Content Types in the left Navigation.
   
   
   
   
4. Click to create a new External Content Type as shown below.
   
   
   
   
5. The External Content Type dialog will be displayed. 
   
   
   
   
6. Enter the Name and Display Name for the external content type as shown in the above figure.
7. Select the Office Item Type as Generic List from the dropdown list.
8. Next click the link, Click here to discover external data sources and define operations to integrate the existing customer database.
9. The Operation Designer dialog will then be displayed. Click Add a Connection to connect to the database. 
   
   
   
   
10. Select "SQL Server" as your Data Source Type.
    
    
    
    
11. Enter the details about your connection to your SQL Server.
    
    
    
    
12. When the connection is made, your Data Source Explorer will be filled with the database you have specified.
    
    
    
    
13. Now choose the table Employee_Details which we are going to connect to the SharePoint.
    
    
    
    
14. Right click the table and select the option, Create All Operations so that you will be able to read, select, update and delete rows from the database table.
    
    
    
    
15. Now the Operation Properties window will pop up which shows the details of the operations that can be performed in the database table.
    
    
    
    
16. Click Next to get to the Parameters page, Select the field that you want to act as an Identifier. In my case I have selected Emp ID as an identifier.
    
    
    
    
17. Click Finish.
18. You'll be presented with a list of operations that your External Content Type can do, as shown in the below figure.
    
    
    
    
19. After completing all these steps save the External Content Type. 

Creating an External List:

There are few ways to create an External List in SharePoint 2010. Here I am creating an External List using SharePoint Designer 2010. To create the External List follow the below steps.

Steps Involved:

1. Select List and Libraries in the left Navigation.
   
   
   
   
2. Click External List that is available in the top Ribbon.
   
   
   
   
3. External Content Types Picker wizard will pop up select the External Content Type ECT that we have created. See the below figure.
   
   
   
   
4. Click Ok.
5. In the Create external List wizard enter the Name and Description for the External list. See the below figure.
   
   
   
   
6. Click OK.
7. Navigate to the site where you have created the External List.
8. You can able to see the External List - BCS List that we have created using SharePoint Designer 2010. See the below figure.
   
   
   
   
9. Click on the BCS List.
10. You may encounter "Access denied by Business Data Connectivity" error when trying to access the External List BCS List.
    
    
    
    
11. The reason is because External List requires External Content Type and External Content Type are using Business Data Connectivity services proxy to access external Data Source. With the same principle of BDC in MOSS 2007-users are required to have BDC object permission before they can use it.

Configure Business Data Connectivity access rights:

1. Go to Central Administration -> Application Management -> Manage Service Applications.
   
   
   
   
2. Click on Business Data Connectivity Service.
   
   
   
   
3. In the top Ribbon click on Manage.
   
   
   
   
4. In Service Application Information check the External Content Type ECT.
   
   
   
   
5. And in the top Ribbon click the Site Object Permissions.
   
   
   
   
6. Site Object Permissions wizard will pop up add the account (Group or Users) and assign the permissions.
7. Once you have configured Business Data Connectivity access rights navigate to the site and check the External List –BCS List that we have created.

Output:

1. Go to the BCS List you can see the external data as shown in the below figure.
   
   
   
   
2. You now have the ability to create new items, update existing items, delete items and do all your normal CRUD-operations (CRUD = Create, Read, Update, Delete) straight from the SharePoint 2010 list.

Summary:

Thus the External data that we have created using SQL Server is connected using BCS to the SharePoint list. This is one of the simplest ways to connect external data to the SharePoint through BCS using SharePoint Designer 2010.