Sunday, June 2, 2013

External Content Types in SharePoint 2010

What is External Content Type:
A core concept of Microsoft Business Connectivity Services (BCS) is the external content type. Used throughout the functionality and services offered by Business Connectivity Services, external content types are reusable metadata descriptions of connectivity information and data definitions plus the behaviors you want to apply to a certain category of external data. External content types enable you to manage and reuse the metadata and behaviors of a business entity such as Customer or Order from a central location, and enable users to interact with that external data and processes in a more meaningful way.
For example, consider a business entity such as Customer. You might want to interact with items of type Customer inside a SharePoint list or work on them offline in Microsoft Outlook. Or, you might want to enable the user to pick a customer from a list of customers in an Orders contract document inside Microsoft Word. You can create an external content type once and then reuse it anywhere you need it.
SharePoint 2010 External Content Type to read data from SQL Server using SQL Authentication and Secure Store Service
SharePoint 2010 External Content Type to read data from SQL Server using SQL Authentication and
Secure Store Service
Most of the articles on BCS are explaining about reading data from database using windows authentication. 
Windows authentication to read data from database won’t work in many of the scenarios. We have to use 
SQL authentication to read or manipulate data from SQL. We have to use secure Store Service to use 
SQL authentication

Here we have following five steps to achieve the same
  • Create a dummy table
  • Configure Secure Store Service
  • Configure BCS service
  • Creating External Content type.
  • Creating External List
  1. Before starting, you should have an SQL server Table. I have created a database with name 
    Contact List.
  2. Right click Table tab of the Contact_List and click on New Table
  3. I have created  a table with the below fields and saved the table with the name Contact_Table

NoField NameType
1Namenchar(10)
2Addressnchar(10)
3Phonenchar(10)

4. Now go to your central Administration Screen. Click on application management an select 
Manage Services from Service Applications


Configuring Secure Service Store

5. From the list of service applications select Secure Store Service
 
6. Click on Generate New Key tab from the top
 
7. Give a Pass Phrase and confirm the Phrase
8. Click on the new from the ribbon once you done with key generation


9. In the next screen give the below details.
 NoOptionsValue
1Target Application IDAny Name
2Display NameAny Name
3Contact EmailAny Email
4Target ApplicationIndividual
10. Click Next. In the screen you got, give the details as shown below
11. Please note, to select Username and password from the filed type


12. Once done with details, click Next
13. Give service account name in the screen and give permission for the user to edit the details if needed later
14. We have almost done with Secure Service Store. Click on the menu SQL_BCS and select set Credentials from the menu


15. Give credentials Owner .SQL user name and password to retrieve the data from SQL

Creating External Content Type

16. Hopes you already have a web application, where we can create external content type.
17. Open your web application in SharePoint designer, Select External Content type from the ribbon
18. Once opened in SharePoint Designer Click on External Content as shown below
19. Give proper name and display name, I have given BCS_Contacts
20. Select Office item type if you want to integrate the same data with Out look
21. Click on “Click here to discover external data source and define operations” link from External System


22. You will get the below screen, click on Add connections. Please note, to select the last option
23. In the screen Give the below screen. Give below details
NoOptionsValue
1Database Server NameYour database server name or IP
2Database r NameYour database  name
3NameAny Name
4Connect with impersonated Custom IdentityThe Secure Store Service name we created in first step
24. You will be prompted with the user name and password. Give the same we entered to connect to SQL server 
database

25. You will get the below screen populated with the database details
26. Right click on the Table and select Create all operations if you want to do all the operations in the database.
27. You will be prompted with the below scree
28. Click Next
29. If you want to add any filter add the same from the below screen.
30. Click Save the BCS from the top
31. Go to manages services and select Business Connectivity service then click Administration


32. Give the ID of the user who can use this service
33. Then click on permission and give permission to who all need to use this service
34. Click on the Secure Store Service ,select the metadata store permission from the ribbon
35. Give permission to all the users who want to use this service to read data from the database

Creating External List
36. Click on View all site content from your web application
37. Click on create
38. Select External list from  the menu
39. Give proper name and click Create
40. Give the name for the External content type and select the externalcontent Type external 
Content Type as the one we created using designer.  Click Create



41. You can see your list populated with the data from database

Reference:


No comments:

Post a Comment

SharePoint Deployment Error : The specified path, file name, or both are too long.

Hi, Sometimes we get surprised with below error while deploying wsp file... Solution : To overcome with this issue, we just ne...