Connecting to SQL Server Using the External Content Type Feature

Abstract: With the External Content Types feature provided in SharePoint 2010, communication between two systems can be achieved easily. By using SQL Server as Data Source, you can minimize writing explicit code for database connectivity and perform CRUD operations too.

As a SharePoint 2010 developer, I have come across various requirements from clients on connecting to external data sources e.g. Direct Connectivity to Database, Windows Communication Service (WCF) and External .NET type from a SharePoint Web Site. By considering its need in portal development, I decided to pen down my thoughts on this topic. This article is a result of the same.

Practically there are many scenarios when a SharePoint site needs to connect to an external system which stores data inside it. E.g. A Payroll system which calculates salary of the Employee in the organization wants to communicate with a Leave Management and Processing portal for calculating total leaves of Employee. To do this in SharePoint 2010, we have been provided with a facility of integrating external data provided by Database, WCF and Web Services and .NET assemblies.

In this article, I have explained the mechanism of integrating with external data provided by Sql Server Database. For this article I have used a sample ‘Customer’ Table in the ‘Company’ Database:

For this article, I already have a Team Site on address ‘http://MyServer/sites/ss’. (Note: You can follow all these steps on the site created by you.) To follow my steps, I recommend you to create aCompany Database in SQL Server and create the Customer table shown above in this database.

Prerequisites:

• Sql Server 2008 R2.
• SharePoint 2010 Server.
• SharePoint 2010 Designer.

Step 1: Browse the site created by you > Select ‘Site Actions’ and select ‘Edit in SharePoint Designer’. This will start the SharePoint Designer.

Step 2: SharePoint 2010 Designer now shows the Site Information. From the left side pane, select ‘External Content Types’ as below:

This step will retrieve the External Content type.

Step 3: To create a new ‘External Content Type’, click on ‘External Content Type’ in the Top left corner of the Ribbon as below:

This will show the window from where you can set the External Content Type Information. Here in this window, you can set the following Information:

1. Name of the External Content Type Information

• Display Name.
• Office Item Type, this is a List type e.g. Contact, Task, Post, Appointment and Generic List.
• External System, using which the Data Source can be discovered, in this case our Data Source will be Sql Server.

2. External Content Operations: This is used to define Operations which can be performed on the External Data Source e.g. Create, ReadList, ReadItem, Update and Delete etc.

Step 4: Set the External Content Type information as below:

Now Click on the ‘Click Here to discover external Data sources and define operations ’ as shown above, you will get the following window using which you can add connections to external content type:

Click on ‘Add Connection’. You will see a window using which you can select ‘External Data Source type’ > Select Sql Server as below:

After clicking OK, you will be asked to enter Database information as shown below:

Click on ‘OK’ and you will get the Database Connection in Data Source Explorer. Expand it and locate the ‘Customer’ table as shown below:

In this step, you have completed the Connection part.

Step 5: Now after establishing the connection withan External Data Source, it’s time for us to define possible operations on the Data Source. So right click on ‘Customer’ and select all Operations from the Context menu as shown below:

The above image shows self-describing operation types. After selecting ‘Create All Operations’, the wizard will start as shown below. Please read the Instructions.

Click on ‘Next’. The next window will show the Parameter Configuration as shown below. Note: Here you can ignore the Warnings.

Click on ‘Next’. Here it is optional for you to set filter parameters which allows you to set the size of the result set. Click on ‘Finish’. The following operations will get generated as shown below:

Step 6: Once it is done, click on the ‘Save’ button on the top-lfet.

Step 7: Now to create the List, click on ‘Create List & Form’ button on the Ribbon

After clicking on ‘Create List & Form’, you will see a form where you can enter List name and other information as shown below. If you want to create an InfoPath form, you can check the CheckBox ‘Create InfoPath Form’

Step 8: Now go back to the Web Site and Refresh. You will see the ‘CustomerInfoList’ in the Quick Launch window as shown below:

Step 9: Click on the ‘CustomerInfoList’ and you will get the following result:

You must be wondering why did this happen? What does the error ‘Access denied by Business Data Connectivity’ mean? The reason for this error is that, BCS is an external web service which is hosted on IIS and to establish connectivity between the Service Hosted on IIS and a database, an explicit authorization is required. So now we need to do some configuration here.

Step 10: Open SharePoint 2010 Central Administration and Select Application Management. From ‘Application Management’ > select ‘Service Applications’ and click on ‘Manage Service Applications’. You will get a list all the services which are running. From this page, click on ‘Business Data Connectivity Service’ > you will get ‘Service Application Information’ as shown below:

Step 11: Select the ‘CustomerListInfo’ and click on ‘Set Object Permissions’ from the Ribbon. You will get a window for Setting Object Permissions. Add the user info as shown below:

Click ‘OK’

Step 12: Now go back to your site and click on the CustomerInfoList from Quick Launch. You will get the following result:

From here, you can perform ‘Create’, ‘Update’, ’Delete’ and ‘Read’ operations.

Conclusion: With the External Content Types feature provided in SharePoint 2010, communication between two systems can be achieved easily. By using SQL Server as Data Source, you can minimize writing explicit code for database connectivity. In cases where you have a local database available with you, you can use the SharePoint 2010 portal directly to perform CRUD operations with your data source.