This entry is the first of a series of entries that will talk about how to work with the new BCS in SharePoint 2010 to access External data. To kick things off, I’m going to show how to connect to a SQL Server table and use it within SharePoint as an External List.
To begin, you need to define an External Content Type. To do this, we are going to use two tools. The first tool is SharePoint Designer 2010. We will use this tool to create the basic definition of the external content type. Then after saving our new content type, we will switch to Central Administrator to set permissions for the content type so we can use it in our web page.
Although an External Content Type once defined can be used anywhere within your SharePoint farm, you have to start by referencing one of your web sites from within SharePoint Designer. Although I’m going to use my own site and data file, I’m going to list the steps you need to follow so you can try out this simple no-code technique.
Steps to Create an External Content Type
1. First you need to make sure that that the Business Data Connectivity Service is running.
a. Click Manage service application in the Application Management group.
b. Locate the Business Data Connectivity Service and make sure that it is started.
2. Open SharePoint Designer 2010 from your Start menu.
a. From the File menu, click on Sites in the left navigation
b. Click the Open Site button if you haven’t opened the site before in SP Designer
c. Enter the URL of your site in the Site Name text box and click Open.
d. After SP Designer opens the site, click External Content Types in the left navigation
e. If you have not yet defined an External Content Type, the list in the center will be empty. Click External Content Type from the New group of the External Content Types ribbon to begin the definition of a new content type.
f. In the External Content Type Information group, define a name for your content type. Pick a name that clearly identifies the content type. Spaces are allowed when defining the name.
g. The value you enter in the Name text box is automatically copied to the Display Name. However, you can change the Display Name if you want.
h. The only other field we will change here is for the External System. Click on the link to define the connection to the database.
i. Click the Add Connection button in the next screen.
j. You first need to define the External Data Source Type. SharePoint Designer provides three choices. To use a SQL Server database, select SQL Server from the Data Source Type and click OK.
k. The next dialog will ask for the Connection properties including the name of the database server and the database itself. You can then provide a name for the connection. I often use the name of the table or view that I will be using.
l. Finally, you need to specify how you want to connect to the server. As I am running on a laptop without Active Directory, I will use Connect with User’s Identity before clicking OK.
m. SharePoint Designer will validate the connection and display the database name. You can expand this into three items: Tables, Views, and Routines (these are stored procedures).
n. If you want to use a table, click the square with the plus sign to expand the table branch to show all the tables.
o. Locate the table you want and right click on it. At this time you need to define the operations that you want to allow through this connect. You must define at least once operation. In the dropdown list, you will see operations that include:
New Read Item Operation
New Read List Operation
New Create Operation
New Update Operation
New Delete Operation
Create All Operations
p. If you want to add, edit, and delete items from this table through your SharePoint list, you should select Create All Operations from this dropdown list
q. This will open another dialog window, the first will show the selected Operations
r. On the second screen of this dialog, Parameters, you can select or deselect the columns you want to include. You can modify the display names of the columns. But most important, this page will probably display several warnings and perhaps even a few errors. The most common warning is to select a column as the field to be used when this content type is used in a drop down list. Typically the picker needs to uniquely identify each row in the table, but you do not want to use a numeric id because most people will not know how to associate that numeric value with the data they want. Fortunately, you can have a multi-field picker. Simply click on the first field you want to use in the picker and then select the checkbox in the right side of the dialog next to Show in Picker. Then repeat this process for each additional field you want to appear in the picker. You may also get warnings for fields that are read-only and required such as auto-generated id fields. You can safely ignore these warnings.
s. On the third page of this dialog, Filter Parameters, you can field the number of rows that you want to include. Obviously, the more rows you include the more time it can take to display the list or dropdown. However, this is again just a warning and you can ignore it if you are using the content type as a lookup dataset.
t. Click the Finish button to complete the definition.
u. At this point, you can save your new content type by right clicking on the tab of the page in the center section and selecting Save. This will save your definition to the metadata store for the web farm.
v. Right click on the tab of the content type a second time and click Close to exit this portion of SharePoint Designer. You can also close SharePoint Designer at this time.
While you have defined an External Content Type at this point, you still cannot use it. You can try, but you will find that it will probably not retrieve data from your external data source. That is because you have not defined permissions to use the content type yet. To do this, you have to open Central Administration and select Manage Service Applications from the Application Management group again. This time when you click on Business Data Connectivity Service, you will see your external content type listed. To assign permissions to the content type follow these steps:
1. Hover over the external content type name and click the arrow to open the dropdown menu when it appears.
2. Select Set Permissions from the list of options.
3. In the dialog that appears enter the accounts (individual or group accounts) to which you want to initially assign permissions. Like adding users to groups in a SharePoint site, use the Check Names option to make sure that SharePoint recognizes the names or groups that you enter.
4. Click the Add button to add your accounts or groups.
5. If not selected in the second list box, select the accounts or groups that you want to assign permissions to.
6. In the third list box are four permission options: Edit, Execute, Selectable in Clients, and Set Permissions. For an administrator I normally would select all four permissions. Two of the permissions are especially interesting. Selectable in Clients allows you to use the external data in clients running within SharePoint such as Word documents, Outlook, and InfoPath. The Set Permissions option allows the selected user to add permissions to others.
7. Click OK to complete the setting of permissions. You can now also close Central Administration.
Your External Content Type is now ready to use. We will end today by going back to our site and selecting Site Actions More Options.
1. Select List from the Filter By selections in the left menu.
2. Select External List as your list type and click Create.
3. As with all list, you need to define a Name, an optional description, and navigation. You have seen this probably hundreds of times. The new addition is the Data Source Configuration. Here you want to click on the second icon after the text box (with the tool tip: Select External Content Type). From the list displayed, select the external content type you want to use. Note that all external content types defined in the entire farm will appear in this list.
4. Click Create to create the list.
It may take a few seconds, but you will shortly see your list displaying data from the SQL Server table defined in this connection.
Next time, we will cover more about how this list works, but if you have been following along with your own data, you are welcome to continue.