SP 2010: Programmatically work with External Lists (BCS) using the Client Object Model

Tobias Zimmergren
Tobias Zimmergren

💡 Presently sponsored by: ScriptRunner
Get your free PowerShell Security e-Book!

**Article 3 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

In my previous article in the series, I talked about how easy it is to fetch information using the standard SharePoint server API-approach. In this article I will talk about how you can access data in your BCS data source, by utilizing the Client Object Model.

Client Object Model code to read from the external list

Just like I described in my previous article, you now have the awesome ability to work with data in your External Lists (connected to a data source using BCS) – namely, you can use the standard SharePoint APIs.

Since I’ve shown you how you can do this using the Server Object Model, I thought I could take another spin at it and show you the code for doing basically the same with the Client Object Model.

The underlying data

As with my previous article, I’m still using the same data source as I set up in my first article – the "ProductList" table in my SQL Server database called "Zimmergren_DB"

*As seen in the SQL Server Management Studio:

Let’s fetch the data using a Windows Forms application that utilizes the Client Object Model!

I’ve designed a Windows Forms application to utilize the .NET Client Object model (in contrary to using the Silverlight client object model or JavaScript client object model).

It looks like this:

When you click the fancy button called "Get External Data", it will use the Client Object Model to fetch the records from the external list (from the SQL server) and display them in a DataGridView. Nothing fancy.

The code!

With no further delays or chit-chat, here’s the simple code!

// Define the Client Context (as defined in your textbox)
    SP.ClientContext context = new SP.ClientContext(tbSite.Text);
    SP.Web site = context.Web;

var ProductList = site.Lists.GetByTitle(tbList.Text);

SP.CamlQuery camlQueryAwesomeness = new SP.CamlQuery();

IQueryable productItems =

IEnumerable externalList =

// This is where we actually execute the request against the server!

// Retrieve the products from the product list using some fancy LINQ
    var productListData = from product in externalList
      select new
          // We’re never pointing to the field at the 0-index
          // because it’s used by the BDC Identity itself. Hence our elements start at 1.
          ProductID = product.FieldValues.ElementAt(1).Value.ToString(),
          ProductName = product.FieldValues.ElementAt(2).Value.ToString(),
          ProductDescription = product.FieldValues.ElementAt(3).Value.ToString()

// Simply clear the rows and columns of the GridView

// Add the columns we need (ProductID, Name, Description)
    gvProducts.Columns.Add("ProductID", "ProductID");
    gvProducts.Columns.Add("Name", "Product Name");
    gvProducts.Columns.Add("Description", "Product Description");
    foreach (var product in productListData)
        // For each product in the list, add a new row to the GridView

References and recommended reading

  1. Getting Started with the Client Object Model in SharePoint 2010
  2. Getting Started with Business Connectivity Services in SharePoint 2010

Summary & Download

At this point, I’ve showed you three short articles in which I describe how you can set up a Business Connectivity Services data source – then utilize the Server OM or Client OM to fetch information from that external data storage.

Worth to note is that this is still a Beta product, which of course means that it may differ in functionality and performance in comparison with the final (RTM) version of SharePoint 2010.

Download the complete Visual Studio 2010 project here: [ **Zimmergren.SP2010.ClientOM.BCS.zip **]


Tobias Zimmergren Twitter

Hi, I'm Tobias. I plan, architect and develop software and distributed cloud services. Nice to meet you!