Saturday, February 26, 2011
Creating External Content Types in BCS
We have good idea on how the Business Connectivity Services connect to external data sources from SharePoint 2010 and how they help us to do the job very easy. To get the data from external data sources to SharePoint and maintain the syncing we have to create the external content types in SharePoint 2010. This post is a walkthrough of the complete process.
- We need a test database [in my case I am using Sql Server].
- The user logged into the server should have access to the Sql Server.
- The user should have full access to the SharePoint site and he should be the SharePoint Farm Administrator.
- Install SharePoint Designer 2010
Creating External Content Type:
- Open SharePoint Designer 2010 and connect to your SharePoint 2010 site.
- From the left panel select "External Content Types" option. It retrieves all external content types from the site if it has any. By default it shows empty.
- Now, from the Ribbon select External Content Type option.
- This will get us some UI on the designer. Enter the Name, Display Name, Namespace and then select the item type [By default Generic List selected].
- Now from the Ribbon, select Operations Design View. This is for creating operations on the database like select, insert, update, delete etc.
- Here, we have to create the connection to connect to the database.
- Click on the button "Add Connection".
- Enter the database server name, database name. By default it will use current logged in windows user credentials to connect to Sql Server.
- Click OK.
- Now the database will be shown up in the Data Source Explorer.
- Expand the Database, expand Tables.
- Now, select your table and then right click to select the operations.
- From the Context menu, select "Create All Operations" for testing all operations as this is the first time we are creating.
- From the wizard, click Next, Next… and then Finish.
- OK, We are all done, created External Content Type and it is now connected to the database.
Creating an External List which holds all table data:
- For this, we have to open the SharePoint site from the Browser.
- From the Site Actions, select "More Options".
- From the list of available options choose "External List".
- Click on the "Create" button. It redirects to a page where we have to enter the list name, description and then External Content Type we have to use.
- From the External Content Type browser [the second icon in the row] type the name and then select it.
- Click OK to create the external list.
- Now, the page redirects to the external list page.
That's it. We are done with creating an external list which contains all data from database table. Depends on operations we have selected from the designer, we can perform all of them and both SharePoint List and Database table will be in sync. This way the end user do not know what type of data he is working with. This is the main advantage of BCS.
While we are doing this process, you may encounter some problems.
- Not able to connect to database – Please make sure the user logged in to windows has access to the database server and access to that database.
- Not able to create External Content Types – User should have full access to the SharePoint site.
- Access Denied By Business Data Connectivity – See solution here.
I hope you are feeling it. What a simple way to connect to external data sources and show the content in SharePoint environment. This is really smart work from Microsoft SharePoint 2010 team. Do you Like it?