articles

Home / DeveloperSection / Articles / Business Data Connectivity Service in SharePoint 2010

Business Data Connectivity Service in SharePoint 2010

Chris Anderson18248 13-Dec-2011

Microsoft SharePoint Server 2010 and the Microsoft Office 2010 include Microsoft Business Connectivity Services, which are a set of services and features that provide a way to connect SharePoint solutions to sources of external data and to define external content types that are based on that external data. External content types resemble content types and allow the presentation of and interaction with external data in SharePoint lists (known as external lists), Web Parts, Microsoft Outlook 2010, Microsoft SharePoint Workspace 2010, and Microsoft Word 2010 clients. External systems that Microsoft Business Connectivity Services can connect to include SQL Server databases, SAP applications, Web services (including Windows Communication Foundation Web services), custom applications, and Web sites based on SharePoint. By using Microsoft Business Connectivity Services, you can design and build solutions that extend SharePoint collaboration capabilities and the Office user experience to include external business data and the processes that are associated with that data.

In this article I am going to explain how we 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.

create a sample sql database

Create a new database in SQL Server and filled it with some example data. In my case, this is the data in my SQL database, called sampleBDCData:

Business Data Connectivity Service in SharePoint 2010

In this sample database, I've added a table called Student:

Business Data Connectivity Service in SharePoint 2010

I'm filling the database with some sample data, so we will be familiar with this data when we later watch this information from SharePoint:

Business Data Connectivity Service in SharePoint 2010

Creating an external content type

The most effective and easy way to set up a simple BCS connection is to use SharePoint Designer 2010. In order to do this, we need to create a new External Content Type.

Here's how do create our External Content Type and attach it up with our database, step by step:

  1. Open the site you want to work with using SharePoint Designer 2010

Business Data Connectivity Service in SharePoint 2010

Business Data Connectivity Service in SharePoint 2010

2.    Select "External Content Types" in the left hand navigation.

3.    Click to create a new External Content Type as shown in the figure below:

Business Data Connectivity Service in SharePoint 2010

4.       Click the link: "Click here to discover external data sources and define operations":

Business Data Connectivity Service in SharePoint 2010

5.       Click "Add Connection"

6.    Select "SQL Server" as your Data Source Type:

Business Data Connectivity Service in SharePoint 2010

7.    Enter the details about your connection to your SQL Server:

Business Data Connectivity Service in SharePoint 2010

8.    When the connection is made, your Data Source Explorer will be filled with the database you have specified. Now choose the table you want to work with and right-click and select "Create All Operations":

Business Data Connectivity Service in SharePoint 2010

9.    Click "Next" to get to the Parameters page

10. Select the field that you want to act as an Identifier. In my case I've selected my Student_ID as an Identifier:

Business Data Connectivity Service in SharePoint 2010

11. Click "Finish"

12. You'll be presented with a list of operations that your External Content Type can do, as shown in the below figure:

13. Save it.

Business Data Connectivity Service in SharePoint 2010

Creating an external list

1.    Open your site and choose Site Actions Ă More Options…

Business Data Connectivity Service in SharePoint 2010

2.    Select the External List template, and click Create

Business Data Connectivity Service in SharePoint 2010

3.    Enter a name for your list, e.g. StudentList

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

Business Data Connectivity Service in SharePoint 2010

5.    Select your data source and click OK:

Business Data Connectivity Service in SharePoint 2010

configure the bdc access rights

1.    Open Central Administration page, and click on Manage service application.

Business Data Connectivity Service in SharePoint 2010

2.    Click on Business Data Connectivity Service.

Business Data Connectivity Service in SharePoint 2010

3.    Open the context menu of the External Content type (i.e. CustomBDCData in this example). Click on Set permissions.

Business Data Connectivity Service in SharePoint 2010

4.    Add account (Group, or User) and assign the permissions.

Business Data Connectivity Service in SharePoint 2010

5.    Set appropriate permission to the BDC object. Remember that BDC object permission rules the interaction between user and external data source through BDC object.

6.    Click OK.

Business Data Connectivity Service in SharePoint 2010

You should now be able to access the external list as shown below:

Business Data Connectivity Service in SharePoint 2010

Let's we add a new student in our external list (i.e. StudentList):

Business Data Connectivity Service in SharePoint 2010

After adding a student, your list will be as follows:

Business Data Connectivity Service in SharePoint 2010

Thanks for reading this article. I think this will help you a lot.

Updated 07-Sep-2019
hi I am software developer at mindstick software pvt. ltd.

Leave Comment

Comments

Liked By