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:

The custom application stores data in a database table

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.

Create an external content type

b. On the left navigation menu, click on “External Content Type.”

External Content Type

c. Click on the “External Content Type” menu item.

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.

Provide a name

e. Click on “Add Connection,” and select “SQL Server” since we are planning to read data from SQL Server database.

Click on “Add Connection"

f. Click on “OK” and then provide database connection information as shown below and click on “OK.”

Provide database connection information

g. The connection to the database is set up as shown below.

The connection to the database is set up

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."

Locate the database table

i. It will open a wizard and you should be able to define the behaviour.

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.

Parameter Configuration screen

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.

“Filter Parameter Configuration” screen

l. You should see a screen as follows. You notice that methods are created.

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.

Create Lists & Form

o. Configure permissions for the BCS service in SharePoint Central Administration.

  • Click on “Manage service applications.”

Manage service applications

  • Select “Business Data Connectivity Service” and then click on “Manage.”

Business Data Connectivity Service

  • 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.

Select the content type

Set Metadata Store 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.

Browse the SharePoint list to verify the external data

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.


Leave a Reply