In my previous blog post, I explained SharePoint Content types. Taking that one step further, let’s now examine SharePoint’s out of the box capability of defining content types on external data in SharePoint. Once the external data is in SharePoint, we can leverage SharePoint’s standard set of functionality, such as defining an out of the box workflow, or the referential relationship between lists, etc.
As per MSDN, the external content type is a reusable metadata of connectivity information, external data and the behaviour that you want to apply on the data. For any business reason, if we need to surface data in SharePoint from an external data source such as SQL Server, we can use this powerful capability offered by SharePoint to maintain the data.
We can use Business Connectivity Service (BCS) to create an external content type and later, a custom SharePoint list can be created to surface the data using an external content type. At the time of creating the external content type, the behaviour of the data can be defined.
Here is an example of how we can surface external data without writing a single line of custom code. Let us suppose that we have a custom application that maintains a Canada-wide company database in a SQL Server database. The custom application stores data in a database table as shown below:
To bring this data into SharePoint, we need to execute the following steps:
1. Create an external content type. This can be done using SharePoint Designer.
a. Open SharePoint Designer and open the SharePoint site that you want to use for this example.
b. On the left navigation menu, click on “External Content Type.”
c. Click on the “External Content Type” menu item.
d. Provide a name. Display the name of the content type and click on “Click here to discover external data source and define operations.” Link. This is where you will define connection information and intended behaviour on the data.
e. Click on “Add Connection,” and select “SQL Server” since we are planning to read data from SQL Server database.
f. Click on “OK” and then provide database connection information as shown below and click on “OK.”
g. The connection to the database is set up as shown below.
h. Locate the database table and right click on the “Company” table. You will be able to see a few available options that would allow you to define behaviour on data such as CRUD operations. For this example, let’s select "Create All Operations."
i. It will open a wizard and you should be able to define the behaviour.
j. Click on “Next >.” On the Parameter Configuration screen, you can modify data attribute properties such as display name, required, read only, etc.
k. Click on “Next >” and it will display the “Filter Parameter Configuration” screen. SharePoint gives an option to limit your result set by providing appropriate filters. It increases performance and decreases the network traffic. In our example, we are skipping this step as there is not much data. Now click on “Finish” to complete the configuration.
l. You should see a screen as follows. You notice that methods are created.
m. You have two options to create an external list that surfaces data using the external content type that we just created. The first option is to use SharePoint Designer and the second is to use SharePoint Portal. Both options are self-explanatory. In this example, we will use SharePoint Designer to create an external list.
n. Click on “Create Lists & Form,” provide name of list, and click OK.
o. Configure permissions for the BCS service in SharePoint Central Administration.
- Click on “Manage service applications.”
- Select “Business Data Connectivity Service” and then click on “Manage.”
- Now select the content type and click one by one on “Set Object Permissions” and “Set Metadata Store Permissions.” Add the user that is going to view the data and click “OK.” I am logged in using Administrator users hence adding it and providing permissions.
2. Now browse the SharePoint list to verify the external data. As you can see, we are able to surface external data in SharePoint using enterprise content type.
External content types are very powerful features in SharePoint. They were available in SharePoint 2007 as well except they did not allow the data to be edited. As demonstrated above, the most important use of external content is to surface data from various custom and legacy applications as long they have compatible data providers.