Business Connectivity Services (BCS) in SharePoint 2010

Business Connectivity Services (BCS) are the upgraded version of Business Data Catalog from SharePoint Server 2007. BCS allow you to connect your SharePoint based content with external data (often called LOB Data – Line Of Business Data). With this powerful functionality you can read the data from external sources and edit them directly from SharePoint sites or web parts. BCS is also fully integrated with the Office 2010.

The standout feature of BCS is the SharePoint Search capability – now you can take your relational databases with plain unformatted data and present them in a SharePoint farm using powerful Search capabilities, social tagging and all the new features that ship with SharePoint 2010. There is also one very important improvement since 2007 – BCS is available even in the free version of SharePoint Foundation 2010, previously it was not available in WSS 3.0.

In this article I will discuss the overall BCS model and demonstrate the configuration of a connection with BCS in a SharePoint Server 2010 farm.

Business Connectivity Services (BCS) Administration

BCS Services are administered using the Business Data Connectivity Service (BDC). If the BDC shortcut is familiar to you from SharePoint 2007, you should note that BDC no longer means “Business Data Catalog”. BDC is now the service that lets you manage BCS services configuration.

Using the BDC, you can manage external content types (sets of fields from other business applications), external systems (such as SQL Server databases, third party applications, etc) and BDC models (in XML format).

Business Connectivity Services supports two types of XML definitions (similar to those from BDC in SharePoint 2007) – application models and resource files. Application models contains the XML definitions of the external content types. Resource files describe imported or exported properties, permissions or localized names.

If you are familiar with the previous BDC model, you may expect that to setup BCS you will need to create complex XML definitions using Visual Studio. Of course, you still can, but now there is simpler way, since SharePoint Designer 2010 let you do the job with only few mouse clicks.

Before we actually start some configuration, we will quickly examine BCS’s functionality and overall model.

External Data Sources

Business Connectivity Services can connect to the following types of external data:

- SQL Server Databases
- SAP Applications
- Web Services – including Windows Communication Foundation (WCF) Web Services
- SharePoint based web sites
- Third party applications

Business Connectivity Services Architecture:

- BDC Service – stores and secures external content types.
- Secure Store Service – securely stores credentials for external systems and manages the associations with the internal/SharePoint based identities.
- BDC Server Runtime – uses the BDC data on front-end SharePoint servers to access/execute operations on the external systems for web browsers and other thin clients.
- BDC Client Runtime – uses the BCS Service and Secure Store Service to access/execute BCS operations on external systems from within the client environment.
- Metadata Cache – provides caching of the BCS Service data. The cache can be optionally encrypted.

BCS Configuration Walkthrough

In the following scenario, I will connect the BDC service to a SQL Server Database that I created. To follow this walkthrough, you will need:

- SQL Server Management Studio (to view the tables and to set the database security for BDC).
- SharePoint Designer 2010 (to configure the BCS connections).
- SharePoint Server 2010 fully operational.

First, let’s take a look at the example database in SQL Server. I created table Clients in the exampledb database, which now contains some data that I want to show within SharePoint sites.








Clients Table that will be used as the external source

Launch SharePoint Designer 2010 and open the SharePoint site (which is http://sps2010 in this example).
























SharePoint Designer 2010 with the intranet site opened ( http://sps2010 ).

Click the External Content Types option in the navigation pane.



















Navigation Pane with “External Content Types” option selected

In the new tab that opens, click on the External Content Type icon. This will create the new External Content Type that we will use to connect with our SQL Server database.








BCS Ribbon in the SharePoint Designer

Click on the Name field and modify the Content type name and Display Name. I have called it My Clients since it will connect with the client list.






















SharePoint Designer 2010 External Content Type configuration window

Now, to create external connection and define the operations for the BDC service, click on the blue link on the bottom of the ECT configuration window (Click here to discover external data sources and define operations).

Click the Add Connection button and specify the Data Source Type (in our scenario it will be SQL Server).








External Data Source Selection

Next, you need to provide the actual SQL Server connection details in the below window.






















SQL Server Connection settings

Now the SharePoint Designer will validate the connection and you will be shown the results.






















SharePoint Designer 2010 connected to the External Content Type – SQL Database

Next, right click on the clients table and select the Create All Operations option from the menu. This operation will launch the Wizard that for creating all the required operations. In our case, we will have create, read, update, delete and query data operations for our external connection source.

































SQL "Clients" table menu in SharePoint Designer 2010

























Parameters Configuration wizard within the SharePoint Designer 2010

In this demonstration we will accept the default settings, which will actually get all the table information (which is fine since we don’t have lot of data ). The only one step we need to do is to specify the identifier. This is the unique table column that will be different for every field in the database. In our case, the unique identifier will be the Company name, so we only have to check the box next to the Map to Identifier and click the Next button on the bottom.

On the next wizard screen, click Finish. If your configuration was successful, you should see the information in the External Content type window as in the screenshot below.












External Content Type status

Now from the SharePoint Designer ribbon, select Create Lists & Form icon to create our list that will be using the external connection.

External Content Types ribbon in SharePoint Designer

Next we need to specify the List name, read item operation, system instance and List description (optional).












Create new BCS List window

That’s it! Our list is ready and is already deployed on the SharePoint site. Now let’s take a look at the result directly on the SharePoint site.























SQL Database viewed within the SharePoint list using Business Connectivity Services

Note that you can actually edit, delete and create new table fields within the external database and you don’t have to use a single line of code. All you need is the SharePoint Designer and a few mouse clicks.
























Adding new item to the database from within the SharePoint site