SharePoint 2010 Using External List

How to create External Content Type and External List based on that content type in SharePoint 2010. We will use SharePoint Designer 2010 to do most of the work.

External list is the new list type provided in the release of SharePoint 2010. In previous version of SharePoint i.e. 2007, we were using Business Data Catalog to bring the data of external data source like SQL Server, Oracle or SAP to our SharePoint site and that was also only for display purpose or to bring them in search results. But in SharePoint 2010, we can create External List which uses the external data as its source and using communication with Business Connectivity Services (BCS) it allows us to read, write or modify data in that external data source.

Business Connectivity Services depends on creation of External Content Types to support the applications. In this article example we will see how to create External Content Type and to use BCS to create SharePoint application.

Get Northwind database for this example

First of all we will get the Northwind database, as we are going to use the Products table from this database to create our External Content Type and External List. So, download the Northwind database from here. After downloading, install the application. This will create a new directory on your C: drive named “SQL Server 2000 Sample Databases”, and in that SQL Scripts for northwind and pubs database are provided. Also, database files for Northwind and Pubs are installed. Now we need to attach the Northwind database to our SQL Server to use it in our example.

Open SQL Server Management Studio and there right click the Databases node and select Attach from the context menu. In the Attach Databases dialog, click the Add button. Next, in the Locate Database Files dialog, navigate to the directory said above and select the file Northwind.mdf and click OK to attach the database.

Create External Content Types in SharePoint Designer

Always remember that External Content Type is backbone for Business Connectivity Services applications. Business objects residing in external data sources are represented by External Content Type in SharePoint and those are used in your application. We will create External Content Type based on the Northwind database using SharePoint Designer 2010 (SPD 2010).

1. First of all create a test SharePoint site using SharePoint 2010 Central Administration.

2. Open SharePoint Designer 2010. Click open site button and enter your site URL. For example, I’ve used

3. When you site loaded in SharePoint Designer, from the right task pane named “Site Objects” select “External Content Types”. Wait while SharePoint Designer builds the list of existing External Content Types.

4. When External Content Type entities loading process completes, click on the External Content Type button provided on the ribbon under External Content Types tag.

5. This will create a new tab named “New external content type” in the main design view with different information about that new external content type.

6. In the “External Content Type Information” group, click on link “New external content type”. This will let you enter the name for your external content type. Provide meaningful name for your new external content type. Please be careful that this name will be used later on so provide related name so that you can easily identify your External Content Type among others if you have any. For my example I’ve provided “Northwind Products”.

7. When you move to change the Display name, SharePoint designer automatically sets it to the Name value which you provided in last step. You can also change it and set it as per your need. I’ve set it to “Products”.

8. In the Office Item Type keep selected “Generic List”. Your resulting view of “External Content Type Information” group would look like given below.

9. Now, on the ribbon bar click on the “Operations Design View” button.

10. When you click on the “Operations Design View”, SharePoint Designer will changes the “Products” tab content and allows you to manage Connection to external data sources.

11. Now, on the Products tab click “Add Connection” button.

12. This will display “External Data Source Type Selection” dialog box. For Data Source Type select “SQL Server” and click OK to proceed.

13. Now, the SQL Server Connection dialog box will come up.

14. For Database Server: provide database name. If you are using SQL Server install on local server then provide localhost, otherwise provide the name of the SQL Server.

15. For Database Name: provide Northwind.

16. For Name (optional): provide the name for your reference. I’ve provided “Northwind Connection”. You can also leave this field blank if you wish.

17. Keep selected the option with text “Connect with User’s Identity”. The SQL Server Configuration dialog would look like given below.

18. Click OK to proceed. Wait until the process completes its work.

19. When completed, you can see that under the “Data Source Explorer” tab, the “Northwind Connection” is added.

20. Expand that node and then expand Tables node.

21. Now, right click on the Products table, and select “Create All Operations” from the context menu.

22. This will open the “All Operations” dialog box. There click Next to proceed on next step.

23. Next, on the “Paramaters Configuration” step, uncheck “SupplierID” and “CategoryID” as we do not want them to use for our example.

24. Now, select “ProductName” and from the “Properties” check the “Show In Picker” check box. Your resulting dialog would like the given blow one.

25. Click “Next” to go ahead.

26. We are doing nothing for Filter Parameters Configuration step, so simply click Finish button.

27. When this is completed, you can see that the External Content Type Operations group box would look like given below and describes you different operations supported by your External Content Type.

28. Click the Save button at the top of the SharePoint ribbon in the Quick Access Toolbar to save the External Content Type i.e. Northwind Products.

29. Now, from the ribbon, click “Create Lists & Form” button from the “Lists & Forms” group under “External Content Types” tab.

30. This will show you the “Create List and Form for Products” dialog box.

31. There provide “NorthwindProducts” for the List Name field. Also be sure that the “Create InfoPath Form” check box is unchecked.

32. Click OK to close the dialog and allow the SharePoint designer to create list and forms for our new external list based on our external content type.

33. Now, our External Content Type and external list based on that are ready. But before using that in our SharePoint site, we must need to configure the security settings on them.

34. So, to do that, first open SharePoint 2010 Central Administration.

35. Under Application Management group select Manage service application.

36. On the page, select “Business Data Connectivity Service” under the “Name” column.

37. You can see that our newly created BCS application named “Northwind Products” is listed over there.

38. Hove the name of the entity, and click on the Yellow down side arrow to open the menu.

39. There select “Set Permissions” option.

40. This will open the “Set Object Permission” dialog box.

41. There in the first text box write “All Authenticated User” and click Add button.

42. Next, for “Permissions for All Authenticated User:” list, select “Execute” check box. Remember that “Execute” is the minimum permission which is required for everyone to access the data provided by BCS application. This permission is being given regardless of the lever of access to the site in which this data is used otherwise user will get access denied error.

43. Next add the “localhost\administrator” account to grant SetPermissions rights to the appropriate person. Also remember that you must need to do this otherwise SharePoint will throw you an error complaining that without this the non-manageable object will be created.

44. Now click OK to close the dialog.

45. That’s it. We have done our configuration and settings.

46. Now, browse to your site. There under the Lists group in quick launch menu, you can see that your external list is there.

47. Now click on “NorthwindProducts” or name which you provided to navigate to that list items. You can see that the products from Northwind database are listed out in SharePoint site.

48. Here you can test the configuration by performing different operations like add new item, edit existing item same like other list operations.

49. For our test let us add a new item to the Products table. Now try to add new product.

50. You can see that you can add the new product to your external database using this Business Connectivity Services and External List and External Content Type.

So, this way as you have see, we can create External Content Type and then External List based on this and extend our SharePoint site to work with external data sources using Business Connectivity Services provided in SharePoint 2010.

Hope you have enjoyed the article and also hope that this would be helpful to you in your development path of SharePoint.

Thanks. Please provide your review.

By Jatin Prajapati   Popularity  (5257 Views)