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:
In this sample database, I've added a table called Student:
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:
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:
- Open the site you want to work with using SharePoint Designer 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:
4. Click the link: "Click here to discover external data sources and define operations":
5. Click "Add Connection"
6. Select "SQL Server" as your Data Source Type:
7. Enter the details about your connection to your SQL Server:
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":
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:
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.
Creating an external list
1. Open your site and choose Site Actions à More Options…
2. Select the External List template, and click Create
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:
5. Select your data source and click OK:
configure the bdc access rights
1. Open Central Administration page, and click on Manage service application.
2. Click on Business Data Connectivity Service.
3. Open the context menu of the External Content type (i.e. CustomBDCData in this example). Click on Set permissions.
4. Add account (Group, or User) and assign the permissions.
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.
You should now be able to access the external list as shown below:
Let's we add a new student in our external list (i.e. StudentList):
After adding a student, your list will be as follows:
Leave Comment