SharePoint 2010: Connecting to SQL Server Using the External Content Type Feature
- 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:
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.