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.
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.
BCS Architecture
The BCS architecture exists of two parts mainly, exposing external data through lists in SharePoint 2010 and exposing external data through parts in an Office client. BCS uses External Content Types (ECT) and External lists to expose data inside SharePoint 2010. From their using VSTO packages data is exposed through External Business Parts in an Office client.
![]()
External data
can be accessed through a SQL Server connection, WCF, .NET Connector or your own
written custom connection. SharePoint 2010 supports OOB a number of systems
which can be exposed through its environment without writing any line of coding.
Examples are SQL Server and SAP.
When business data from external systems is exposed in SharePoint 2010 as a list it is nothing more than a view on that data. The data itself is not stored in SharePoint 2010. But you are able to browse, view, change and even search through it. Before we go into exposing external data there are some new types of components in SharePoint 2010 we need to understand better.
External Content Types
Solutions based on BCS rely on the use of External Content Types to expose and integrate external data into SharePoint. The External Content Type is just like a Content Type and describes the schema and data access capabilities of an external data source and its behavior inside SharePoint and Office clients. It uses a database connection, web service, .NET connector or custom code to connect to the external data source. External Content Types are created in SharePoint Designer 2010 or with Visual Studio 2010. It is also possible to define an External Content Type in an Xml file and import it into the Visual Studio environment.
External Lists
The data exposed through an External Content Type is shown in an External list. The External list looks just like an ordinary list in SharePoint. Some options you have on ordinary SharePoint lists are not available to the External list. For example adding extra columns, changing existing columns and using Workflow is not allowed.
BCS Configuration
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:
I'll just show you how my sample database is set up - simply create a new database in your SQL Server and have it filled with some example data. In my case, this is the data in my SQL database, called PravahamDB
In this sample database, I've added a table called ProductList which in theory will represent some products in this database, like this:
![]()
The most
effective and easy way to set up a simple BCS connection, is to use SharePoint Designer 2010. You heard me,
we can now get up and running with BCS by using SPD instead of modeling complex
ADF files and things like that.
In order to do this, we need to create a new External Content Type!
Here's how do create our External Content Type and hook it up with our database, step by step:
![]()
Click to create a new External Content Type like this:
![]()
Click the link that reads: "Click here to discover external data sources and define operations":
![]()
Click
"Add Connection"
![]()
Select "SQL Server" as your Data Source Type:
![]()
Enter the details about your Connection to your SQL Server:
![]()
If You Select Connect with User's Identity,Follow below Steps...
Or
If You Select Connect with Impersonated Windows Identity
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":
![]()
Click "Next" to get to the Parameters page
Select the field that you want to act as an Identifier. In my case I've selected my ProductID just to get on with it:
![]()
Click "Finish"
You'll be presented with a list of operations that your External Content Type can do, like this:
![]()
That's it. A few points, a few clicks - and you're done. Let's create an external list (using the Browser to show how simple it is..) and hook up our external content type with it!
SharePoint Server 2010 fully operational.
Creating an External list
There's a few ways to create an external list in SharePoint 2010. We will create it using the Browser UI to show you how simple it can be.
Open your Site and choose Site Actions - More Options…
![]()
Select the External List template, and click Create .
![]()
Enter a name for your list, e.g. Product List
You'll see a field in this list called External Content Type, click the browse-button beside it:
![]()
Select your data source and click OK:
![]()
Now simply click the button called Create:
![]()
You now have the ability to create new items, update existing items, delete items and do all your normal CRUD-operations (CRUD = Create, Read, Update, Delete) straight from the SharePoint 2010 list.
Adding a new product
Let's just for the fun of it add a new product called "Awesome Product 1.0" like the following screenshot
![]()
Now go to your SQL Server and see the changes take effect immediately. The data is NOT stored in SharePoint, it's stored in your SQL Database.
This is what my table now looks like in the SQL Server, after adding a new item in the SharePoint list:
![]()
This is merely a sample to show you how easy it is to actually get up and running with the SharePoint 2010 Business Connectivity Services (BCS) and work with external data.
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.
BCS Architecture
The BCS architecture exists of two parts mainly, exposing external data through lists in SharePoint 2010 and exposing external data through parts in an Office client. BCS uses External Content Types (ECT) and External lists to expose data inside SharePoint 2010. From their using VSTO packages data is exposed through External Business Parts in an Office client.
When business data from external systems is exposed in SharePoint 2010 as a list it is nothing more than a view on that data. The data itself is not stored in SharePoint 2010. But you are able to browse, view, change and even search through it. Before we go into exposing external data there are some new types of components in SharePoint 2010 we need to understand better.
External Content Types
Solutions based on BCS rely on the use of External Content Types to expose and integrate external data into SharePoint. The External Content Type is just like a Content Type and describes the schema and data access capabilities of an external data source and its behavior inside SharePoint and Office clients. It uses a database connection, web service, .NET connector or custom code to connect to the external data source. External Content Types are created in SharePoint Designer 2010 or with Visual Studio 2010. It is also possible to define an External Content Type in an Xml file and import it into the Visual Studio environment.
External Lists
The data exposed through an External Content Type is shown in an External list. The External list looks just like an ordinary list in SharePoint. Some options you have on ordinary SharePoint lists are not available to the External list. For example adding extra columns, changing existing columns and using Workflow is not allowed.
BCS Configuration
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.
I'll just show you how my sample database is set up - simply create a new database in your SQL Server and have it filled with some example data. In my case, this is the data in my SQL database, called PravahamDB
In this sample database, I've added a table called ProductList which in theory will represent some products in this database, like this:
 |
| SqlServer DB |
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:
SharePoint Designer 2010
(to configure the BCS connections).
In order to do this, we need to create a new External Content Type!
Here's how do create our External Content Type and hook it up with our database, step by step:
- Open the site you want to work with using SharePoint Designer 2010
- Select "External Content Types" in the left hand navigation:
Click to create a new External Content Type like this:
Click the link that reads: "Click here to discover external data sources and define operations":
Select "SQL Server" as your Data Source Type:
Enter the details about your Connection to your SQL Server:
If You Select Connect with User's Identity,Follow below Steps...
Or
If You Select Connect with Impersonated Windows Identity
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":
Click "Next" to get to the Parameters page
Select the field that you want to act as an Identifier. In my case I've selected my ProductID just to get on with it:
Click "Finish"
You'll be presented with a list of operations that your External Content Type can do, like this:
That's it. A few points, a few clicks - and you're done. Let's create an external list (using the Browser to show how simple it is..) and hook up our external content type with it!
SharePoint Server 2010 fully operational.
Creating an External list
There's a few ways to create an external list in SharePoint 2010. We will create it using the Browser UI to show you how simple it can be.
Open your Site and choose Site Actions - More Options…
Select the External List template, and click Create .
Enter a name for your list, e.g. Product List
You'll see a field in this list called External Content Type, click the browse-button beside it:
Select your data source and click OK:
Now simply click the button called Create:
You now have the ability to create new items, update existing items, delete items and do all your normal CRUD-operations (CRUD = Create, Read, Update, Delete) straight from the SharePoint 2010 list.
Adding a new product
Let's just for the fun of it add a new product called "Awesome Product 1.0" like the following screenshot
Now go to your SQL Server and see the changes take effect immediately. The data is NOT stored in SharePoint, it's stored in your SQL Database.
This is what my table now looks like in the SQL Server, after adding a new item in the SharePoint list:
This is merely a sample to show you how easy it is to actually get up and running with the SharePoint 2010 Business Connectivity Services (BCS) and work with external data.
