Monday, June 20, 2011

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 DataLine 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:
  • 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.
 SQL Server Management Studio (to view the tables and to set the database security for BDC).

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).

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:
  1. Open the site you want to work with using SharePoint Designer 2010
  2. 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":

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...
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.


  1. Malaysia & Singapore & brunei greatest internet blogshop for wholesale
    & quantity korean add-ons, accessories, earstuds, pendant, rings, trinket, hair & bangle accessories.
    Promotion 35 % wholesale price cut. Ship Worldwide
    My web page: La Fiesta

  2. Woah! I'm really digging the template/theme of this blog. It's simple, yet
    effective. A lot of times it's very hard to get that "perfect balance" between superb usability and visual appearance. I must say that you've
    done a excellent job with this. Additionally, the blog loads super quick for me on Chrome.
    Excellent Blog!
    Here is my webpage ; click through the following web site

  3. Nice post. I learn something new and challenging on
    blogs I stumbleupon everyday. It's always useful to read articles from other writers and practice a little something from other web sites.

    Feel free to visit my site; windows xp registry cleaner

  4. I really love your site.. Very nice colors & theme. Did you create this
    amazing site yourself? Please reply back as I'm attempting to create my own personal site and would like to find out where you got this from or just what the theme is named. Appreciate it!

    My web site :: Fityemek

  5. I got this web site from my friend who informed me concerning this site and now this time I
    am visiting this site and reading very informative articles here.

    Stop by my web page Quick Links

  6. First off I want to say wonderful blog! I had a quick question in
    which I'd like to ask if you do not mind. I was interested to know how you center yourself and clear your mind before writing. I have had a difficult time clearing my thoughts in getting my ideas out there. I do take pleasure in writing but it just seems like the first 10 to 15 minutes are usually wasted simply just trying to figure out how to begin. Any suggestions or hints? Thank you!

    my web blog -

  7. Appreciate the recommendation. Let me try it out.

    Here is my web page;

  8. Woah! I'm really loving the template/theme of this site. It's simple, yet effective.
    A lot of times it's very hard to get that "perfect balance" between usability and visual appearance. I must say you have done a superb job with this. Also, the blog loads very fast for me on Safari. Outstanding Blog!

    Here is my blog post ::

  9. Information was good, I like your post.
    Looking forward for more on this topic.
    SharePoint 2013 Online Training