Try your hand at the Business Connectivity Service – part 2

In part one of the story  we created a business connectivity connection to a SQL database. In this section we will use this connection.

The connection  enables two things: first of all, this connection can now be used to create a metadata field based on “External Data”. And second, we can use SharePoint Designer to create a list within the SharePoint site, based on this external data. I will show both.

Option 1: metadata

Go to your site and the list of document library you want to extend with external data. For this demo, we will use a document library. Go to the library settings and select “Create column”. Give the column a name and select “External Data”.

You can select the newly created connection and click “OK”. Now, you can select the field which is to be displayed when editing the properties of the document. You can also select one or more related field which can be used in views. Select the productid and click OK.

Now upload a document or edit the properties of a document. When selecting the new field, the BSC connection will show you the SQL data. Pretty cool, huh?

Option 2: SharePoint list

This one is more easy. Use the SharePoint Designer for this and use the site which we already used before. Go to External Content Types and select External List. Enter a name and description and SharePoint Designer will build you the list. You can change this list if needed.

Issues

Your SharePoint user might get an error message stating that it does not have the requirement permissions.  You can solve this in two ways (and, again, this is a demo environment):

1. Give the user the requirement permissions on the SQL database level;
2. Check the permissions on the BCS connection (which we already did in part 1).