SP 2010: Getting started with Business Connectivity Services (BCS) in SharePoint 2010

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

Article 1 in the small BCS-series:

1. SP 2010: Getting started with the Business Connectivity Services (BCS)
2. SP 2010: Programmatically work with External Lists (BCS) in SharePoint 2010
3. SP 2010: Programmatically work with External Lists (BCS) using the Client Object Model

BCS in SharePoint 2010 is an awesome refinement of the Business Data Catalog from MOSS 2007. With BCS – or Business Connectivity Services – you get the possibility to connect external data and work with it from SharePoint.

In this article I will not cover the basics of what BCS is all about (MSDN/TechNet does this very well) – I will rather give you a walkthrough of how you 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.

BCS Poster: Business Connectivity Services Poster
BCS Team Blog: http://blogs.msdn.com/bcs/

A sample SQL database

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 Zimmergren_DB:
image

In this sample database, I’ve added a table called ProductList which in theory will represent some products in this database, like this:
image

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:
image

Alright – we have some sample data in our SQL Server. Nothing fancy, just some very simple data. Great, let’s get going with the fun stuff!

Creating an external content type

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:
    image
    Loading this page might take some time, be patient!
  3. Click to create a new External Content Type like this:
    image
  4. Click the link that reads: "Click here to discover external data sources and define operations":
    image
  5. Click "Add Connection"
    image
  6. Select "SQL Server" as your Data Source Type:
    image
  7. Enter the details about your connection to your SQL Server:
    image
  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":
    image 

    You’ll be presented with a wizard-like dialog where you can specify the operations, elements and other properties for your BCS connection.

  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 ProductID just to get on with it:
    image
  11. Click "Finish"
  12. You’ll be presented with a list of operations that your External Content Type can do, like this:
    image

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!

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.

  1. Open your site and choose Site Actions - More Options…
    image
  2. Select the External List template, and click Create
    image
  3. Enter a name for your list, e.g. Product List
  4. You’ll see a field in this list called External Content Type, click the browse-button beside it:
    image

    What is really awesome here, is that you’re now presented with a dialog where you simply can choose the data source for this list. That means, you’ll select the data source you’ve created (mine is called Zimmergren_DB). Then your list will automatically work against the SQL database, but still have the look and feel of a SharePoint 2010 list.

  5. Select your data source and click OK:
    image
  6. Now simply click the button called Create:
    image

Would you look at that! You’re now working with external data, from your (what looks to be) normal SharePoint list! This is brilliant!

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.

Proof of concept – 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:
image

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:
image

Summary

With a few points, followed by a few clicks – you’ve set up your external data connection. Basically it’s that simple.

Of course there’s a lot of things to consider when doing these configurations – and you might not want to auto-generate the CRUD-operations, but rather create them one by one and specify more fine-grained permissions etc.

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!

Enjoy

Follow me

Tobias Zimmergren

Founder / Consultant / Advisor at TOZIT AB
Tobias Zimmergren delivers high-quality articles about business and technology around the Microsoft scene.

Tobias focuses on advisory and consultancy for the Office 365 and SharePoint offerings from Microsoft.
Follow me
  • Reddy

    images are not working.. please update it

    • http://www.zimmergren.net/ Tobias Zimmergren

      Reddy,

      The images are working again.

      Regards,
      Tobias

  • Rick Jenkins

    Very interesting. Thanks for putting this together

    • http://www.zimmergren.net/ Tobias Zimmergren

      Hi Rick,
      You’re most welcome – I’m glad you liked it.
      Cheers,
      Tobias.

  • Raghu

    It’s a nice article on BDC..

    • http://www.zimmergren.net/ Tobias Zimmergren

      Thank you Raghu, I appreciate the kind feedback.
      Tobias.

  • Vtvimal

    Hi ,
    Excellent article, but at the end i’m receiving
    “Access denied by Business Data Connectivity.
    Correlation ID:a29ba2a8-7846-4bb8-9829-0ca5cf1b8f09″

    Error , not sure if there is authentication mismatch

    • http://www.facebook.com/bengt.nilsson.140 Bengt Nilsson

      Central Administration – Application Management – Manage service applications – Business Data Connectivity Service

      Select the external content type you created earlier and click on Set Object Permissions

    • http://www.zimmergren.net/ Tobias Zimmergren

      I wrote a post about this back in 2010: http://zimmergren.net/technical/access-denied-by-business-data-connectivity-solution
      Bengt is on the right track.
      Tob.

  • Vishwajit05

    Hi,
    This article is very nice. But can anyone can tell my requirment is-If i delete any items from list then it should delete from list but should delete from sql table so that I can track which item is deleted from list. Please help me out.

    • http://www.zimmergren.net/ Tobias Zimmergren

      If you want verbose logging on what happens when you delete something from the SQL Server my best guess is you’ll have to look into triggers or some type of event handling on the actual SQL server. BCS and external lists doesn’t support event receivers or events in SharePoint 2010 the same way as it does in normal lists. SharePoint 2013 has some cool new updated features for this though!

  • Charles

    what would be the difference in adding SQL database as a external content type or a data source?

  • pavan

    thanks..nice article…:)

    • http://www.zimmergren.net/ Tobias Zimmergren

      Hi Pavan,
      Thanks – I’m glad you liked it!
      Cheers,
      Tobias.

  • programer

    very helpful article. thank u so much for ur efforts

    • http://www.zimmergren.net/ Tobias Zimmergren

      I’m glad you liked it.
      Cheers,
      Tobias.

  • Caroline
    • http://www.zimmergren.net/ Tobias Zimmergren

      Thanks for the heads up Caroline. A few broken links was the result after migrating the blog from one platform to the other.
      Cheers,
      Tob.

  • Omri

    hi,
    have a small problem.
    i developed ECT among Development Database.
    and now i want to change the connection with the same ECT to Production DB.
    with minimum changes.
    and of course leave the Development environment .

    THanks,

    Omri

    • http://www.zimmergren.net/ Tobias Zimmergren

      Hi Omri,

      Well the changes you need to do depends on where you store your connection string to the database really. If your development DB and your production DB looks the same (which they should do, in a best practice scenario) it should only be a matter of swapping the actual connection string.

      / Tob.

      • Omri

        Thanks for your reply,

        the thing is i need to keep my Dev environment and to create new at production database of course. the Share point only point to different site.

        how i duplicate the setup from development and create it on Production DB .

        Many Thanks,

        Omri

        • http://www.zimmergren.net/ Tobias Zimmergren

          Hi Omri,

          If I understand you corrently you want to keep your development environments (and be connected to Development DB) and have a production environment which uses Production DB.

          There’s various ways to accomplish the different environments and different connection strings. One way that I’ve been successfully doing myself is something similar to this:http://www.silver-it.com/node/92

          Swapping the SiteUrl this way is easy and you can have your way with the other settings and connection strings etc.

          Have a look at that article and see if you can make sense of it – it’s pretty good.

          Cheers,
          Tob.

  • Marc

    Hi Tobias,

    how could I do input validation in this scenario? Is there a way to handover database error messages (e.g. thrown by triggers) to Sharepoint so that it’s being shown to the user?

    Thanks and regards

    • http://www.zimmergren.net/ Tobias Zimmergren

      Hey Marc,

      There’s various ways to take care of validation. The approach I’ve chosen in several implementations is by using jQuery to add client side validation. Should you encounter error messages anyway, you could handle it programmatically but it should really be taken care of before it heads on over to the data source.

      Check out this article I wrote on BCS and customizing the forms: http://zimmergren.net/misc/sp-2010-customizing-the-forms-for-external-lists-bcs-in-sharepoint-2010-by-using-custom-field-controls-and-jquery-2

      See the section about modifying the display forms. You can use the same type of approach for modifying New- and EditForms and hence add your own custom client side logic to them.

      Cheers,
      Tob.

  • Pingback: SP 2010: Programmatically work with External Lists (BCS) in SharePoint 2010 | SharePoint interests()

  • Santosh K Garg

    Thanks To Share This Article
    But Could you suggest me how to change DB schema by external list. for example i want to add one new column in external list which reflect in DB Schema also, But i am unable to add..because there is no option to add new column in external list…

  • Dinesh

    Very Nice article on BCS.. Helpful.. Thanks a lot…

    • http://www.zimmergren.net/ Tobias Zimmergren

      Hi Dinesh,

      Thank you for your comment. Glad it could help you :-)

      Cheers,
      Tobias.