External List In SharePoint 2010

Posted by in SharePoint category on for Beginner level | Points: 250 | Views : 8411 red flag

In this article we will have look on External List of SharePoint 2010.


Before creating External List let’s understand the basics of Business Connectivity Services of SharePoint 2010. 

What is BCS: Business Connectivity Services is a one of the service application of SharePoint 2010, through this service application SharePoint sites can connect to external database and bring information from database to SharePoint sites.

What is External Content Types: External content types is used to define the external data sources and the data operations that can be performed on the data, such as select, insert, delete, and update. External content types can include definitions of forms for adding and editing data from external data from the SharePoint user interface.

What is External List: External List is an object of external content types. Basically, they contain the actual data, and they appear as SharePoint lists in the SharePoint sites. Through External List, End Users can insert, update, and delete data as like a SharePoint native list (If they have permission to do so) 

After Understanding this Funda, Let’s start with External List.

Before creating External List, we must first create External Content Types.

1) Go to your Team Site, click on Site Actions and then click on “Edit in SharePoint    Designer”.

2) After opening site in SharePoint Designer
A. In Site Objects Section, Click on External Content Types (Highlighted in Red box), and Note: Initially there is no         External Content Types available. 
B. Now Go on External Content Types ribbon and click on External Content Type (Highlighted in Blue box).

3) On New External Content Type page 
A.     Type the name for External Content type; Here I have typed “HR Department” (Highlighted in Blue Box). 
B. Now click on “Click here to discover external data sources and define operations” link (Highlighted in Red Box). 

4) After clicking on “Click here to discover external data sources and define operations” link, Operation Designer Page     would open.
A. On Operation Designer page click on Add Connection.
B. On “External Data Source Type Selection” window, select SQL Server as Data Source Type and click on ok.

5) On SQL Server Connection Window, type your database server name and name of database. Here I am using             AdventureWorks sample database. 

    After providing this information click on ok; Now database connection has established between your database and     SharePoint site.

6) Now go to Data Source Explorer section
A. Expand Adventureworks.
B. Expand Tables Node. 
C. Right click on Department Table and Click on Create All Operations. 

Create All Operations would create Insert, Delete, Update, Select Operation on that table.

7) After clicking on “Create All Operations”, Operation Wizard would open. 
              On Operation Properties window, click on Next

8) On Parameters Configuration window, select DepartmentID (Note: Do not uncheck DepartmentID) 
A. Go to Properties window of “DepartmentID” and Check the “Map to Identifier” Properties. (Select Check Box) 
B. Go to Properties window of “DepartmentID” and Check the “Show In Picker” Properties. (Select Check Box) 

 After providing this information, click on next and on “Filter Parameters Configuration” window click on Finish.

9) Now let’s create External List based on this Content type. 

10) Go to your Team site.

11)  Go to “Create page”. Create Page is a central piece of SharePoint sites from where we can add List, Library,             Pages, Subsites into our current site.

A. Go to Site Actions and then click on “More Options”, Create Page will open 


B. Click on “Lists” link available on quick launch and then click on “Create” link.

12) After Clicking on Create link here or More Options, “Create” Page would open.

A. On Create page, in filter by section click on List.
B. Select “External List” Template. 
C. Click on Create

13) On New Page
A. Type the Name for External List; Here I have typed HR Department.
B. For External Content Type, click on “Select External Content type” Icon .(Second Icon)

C. In “External Content Type Picker” window, 
I. select your external content type i.e. AdventureWorks-HR Department and click on ok.
II. On New Page Click on Create.

14) Now External List – HR Department has created but it is showing error “Access denied by Business Data                   Connectivity”.

This error indicates that current user does not have permission to access external content types.

15) So let’s assign Permission, Go to Central Administration Site of SharePoint 2010. 

A. Go to Application Management Section and click on “Manage service applications” link.

B. On Manage Service Application Page, click on “Business Data Connectivity Service” application link.

C. On “View External Content Types” page, Select the down arrow beside HR Department Content types and click         on Set Permissions.

D. On set Object Permissions window, add “All Authenticated Users” group and assign Edit, Execute, Selectable In         Clients, and Set Permission to that group and click on ok.
Note: As per your security requirement you can add specific users and assign specific permissions to                        that user  here.

16) That’s it, now go to your Team Site and refresh your team site. 

Click on “HR Department” List from Quick Launch and see the record from Department table.

Now you can add new record to table, delete record from table, and update record of table by using this list.

   Hope this would be useful for the readers! Thanks and do let me know your comments or feedback.7) 

Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Nilkant Jagtap
Member Level: Bronze
Member Status: Member
Member Since: 3/19/2011 6:35:11 AM
Country: India

Jagtap Nilkant D. MCSD,MCPD 2.0,MCPD 3.5,MCPD 4.0,MCPD SharePoint,SQL Server , MCT. Working as Technical Trainer.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)