Here I am going to show a small demo about how to connect to external data source and showing the external data in to sharepoint site using BCS(Business Connectivity Services).
Small Introduction about BCS:
BCS is an upgraded feature for the BDC (Business Data Catalog) of MOSS. BCS allows us to connect the external data(SQL,SAP,Webservices etc) .We can read and edit the data. This provides us the bidirectional (read or write) communication between our SharePoint site/list with the External data. (Where as in BDC only we can retrieve/read the data from external database).
You can find more information on BCS here.
Here my external data source is MS SQL server and database is BCS_DemoEmp. I have table called tblEmployee as shown below.
Now I want to integrate this data in to my sharepoint site.to Integrate External data in to our SharePoint site, first we need to create an external content type. (Info on External Content type here.) We can do this using SharePoint designer and some other ways but using designer is most easy way. Here I am creating the external content type using Sharepoint Designer 2010.
Open the Sharepoint Designer 2010-->Open your site-->Select Site Tab in the Ribbon and Select External Content Types in left navigation
Then Select External Content Types as shown below
Select Office Item Type : Generic List
Office Sync External List : Enabled
Click on Click here to “discover external source and define operations” link as below.
Click Add Connection --> select Data source type : SQL Server
Give your sql connection details
Click Ok. It will connect to your database server and displays the database.Select your table(tblEmployee) and right click-->New Read Item Operation
Operation Properties Wizard will be open. Do the needed settings as below
If any warnings or errors it will show. Here no warnings and errors. Click Next
This is Input Parameters Wizard. Here you will find the Data Source Elements (Table columns) and right side section shows the properties of this element. You should map at least one data source element as Identifier (Check the “Map to Identifier”) if not you will get the error.(see by unchecking it)
Continuation is in next part BCS in Sharepoint 2010-Part2