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
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 http://192.168.92.134.
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
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
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
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
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
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
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.