While working with SharePoint 2010, sometimes we need to fetch the data from external data source such as SQL Server, Oracle etc. In earlier version of SharePoint (MOSS 2007), BDC (Business Data Connectivity) service is there to help us in this scenario. But BDC service is not present in SharePoint 2010. In SharePoint 2010, BDC service has been replaced with a new service called BCS. This Service provides you same functionality as BDC as well as some other features which are not present in BDC.

In this article I will give you a walkthrough of how you can setup a BCS connection to an external database, and then work with this information directly from a SharePoint list - without the user actually knowing anything about the connection to the database. .

Creating an external content type

The most effective and easy way to connect to the external data is to use SharePoint Designer 2010. You can also use Visual Studio 2010 for this but for a beginner it is better to use SharePoint Designer 2010.
In order to do this, we need to create a new External Content Type. Here are the steps to how do create our External Content Type and hook it up with our database:

 Run the SharePoint Designer 2010 and open the site you want to work with.

 Click on External Content Type option in the left navigation panel.

 In the ribbon click on External Content Type to create a new one.

 Click on the link “Click here to discover external data sources and define operations”.

 Click on the button “Add Connection”

 Select the Data Source Type as SQL Server.

 Specify the connection properties like Database Server, Database Name

 Choose Connect with User’s Identity

 After clicking on OK your Data Source Explorer will show you the database you have specified. 

 Now Right click on the table/Stored Procedure/View you want to work with and select "Create All Operations". 

You can also choose operations one by one. A wizard-like dialog will be appear where you can specify the operations, elements and other properties for your BCS connection.

 Click "Next" to get to the Parameters page

 Select the field that you want to act as an Identifier or key field and Click "Finish"

 You'll be presented with a list of operations that your External Content Type can do, like this:

Creating an external list

Now we are moving towards to create an external list and then hook up our external content type with it. There's different ways to create an external list in SharePoint 2010. We will create it using the Browser UI.

 Open your site and choose Site Actions - More Options…

 Select the External List template, and click Create

 Enter a name for the list, e.g. Product List

 You'll see a field in this list called External Content Type, click the browse-button beside it.

It will show you a dialog where you simply can choose the data source for this list. Then you will need not to worry about how the external list fetch the data from SQL database. It’s all the magic of BCS service. Now your list will automatically work against the SQL database.

 Select your data source and click OK.

 Click on the Create button.

It will show you all the records from the SQL Database. Now you can perform the normal CRUD-operations (CRUD = Create, Read, Update, Delete) directly from the SharePoint 2010 list.

Copyrights 2019,