How to create External Content Type and List using Business Connectivity Service in SharePoint Designer 2010
1: Open your sharepoint website in sharepoint desinger 2010 and click on
External Content Types in left hand side "site objects".
2: Now Click on "External Content Type" on Ribbon.
3: Click on "Click here to discover external data sources and define operations" (showned in picture).
4: Click on Add Connection button. It will pop up "External Data Source Type selection".
5: Select SQL Server as Data source type.Click on OK. It will pop up "SQL Server Connection".
6: Now Enter your SQL Server Name, Database Name, and display name.
select "Connect with Impersonated Custom Identity" and enter Secure Store Application ID(remember this Application ID should be unique, will tell explain how important it is.basically it store sql server username and password). Now click on OK.
7: After click on OK button it will ask "SQL Server username and Password".Enter SQL Server Credentials and Click on "OK".
8: now External content type is created. But we didnt created any operation for content type.
9: Expand Database which in Data Source Explorer.
10: Select your table which use used to fetch data and display in sharepoint list.
11: Right click on table and select "Create All Operations".
12: Click on "NEXT".
13: now set of all column fields will shown here. we can select all or only few. But one column sholud be unique identifier.
Click on "NEXT".
14: Here we can filter table. Otherwise simply click on "Finish".
15: Now we can see all operation in "External Content Type Operations".
16: Now we can create external list.Click on "Create Lists & Form" which located on ribbon.will pop up one box.
17: Give external list name in popup box.Click on "OK".
18: Now we can see newly create external list in "External Lists" panel.
19: Now open your site in browser.
20: Select your external list in leftside navigation.It will show error message like "Access denied by Bussiness Data Connectivity".
While creating external content type we given credential which are not
saved anywhere those are only used for to display database in "Data source Explorer".
21: Now Go To your central administrator and select "manage service applications" which is "Application Management" section.
22: Click on Bussiness Data Connectivity.
23: Now you can find your external content type and select your content type.
24: Now Click on "Set Object Permissions",it will open "Set Object Permissions" pop up.
25: Enter Account name how can use this content type and Click on Add. Select Permissions Check boxes. Then Click on "OK".
26: Now to go user website and click on ur external list. Here You will
Get error message again,but this time error message is different which
is "Unable to display this webpart. To troubleshoot the problem, open
this web page in a microsoft sharepoint Foundation-coompatible HTML
editor such as Microsoft Sharepoint Designer".
27: GO to your central administration site and Select "Application Management" which is in Quick Launch.
28: click on "Manage Service applications" which is in "Service applications" section.
29: Now select "Secure Store Service".
If you are using this first time it will show one message like "Before
creating a new secure store target application, you must first generate a
new key for this Secure store service application from the ribbon".
30: Click on Generate new key from the ribbon.
31: Give any name which should be strong name. Click on "OK".
32: Click on "New" on the ribbon.
33: SPecify a unique Target application id, display name,contact
email(any but should be valid) and Click on "NEXT".(Remember that This
name should be already given while creating External Content Type in
Step 6).
34: Click on "NEXT".
35: Enter Target Application Administrators and Click on "OK".
36: Now you can see newly create Target Application ID.
37: now select newly create Target Application ID and click "set" on the ribbon.
38: Enter Credentials owner(full control user like administrator), SQL server Database username and password. Click on "OK".
39: Now go to your website and click on external list from quick launch.This time u will get list without any error.
40: this step is not require but if you get any error in previous step.
GO to your central administrator–>Application Management–>Service
application–>select your External content type and Click "Configure"
on the ribbon.