In this article we will have look on External List of SharePoint 2010.
Introduction
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
Or
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)