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

Tobias Zimmergren
Tobias Zimmergren

This is the third post in the series about SharePoint BCS.

  1. Getting started with the Business Connectivity Services (BCS)
  2. Programmatically work with External Lists (BCS) in SharePoint 2010 (this post)
  3. 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

As 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

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

*As seen in the SQL Server Management Studio:

No Image: Picture broken due to migration.

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 (contrary to using the Silverlight client object model or JavaScript client object model).

It looks like this:

No Image: Picture broken due to migration.

When you click the "Get External Data" button, 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!

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 =
                                  ProductList.GetItems(camlQueryAwesomeness); 

IEnumerable externalList =
                                  context.LoadQuery(productItems);

// This is where we actually execute the request against the server!
context.ExecuteQuery(); 

// 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
gvProducts.Rows.Clear();
gvProducts.Columns.Clear(); 

// 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
    gvProducts.Rows.Add(
                             product.ProductID,
                             product.ProductName,
                             product.ProductDescription
                             );
}

Summary

Here are more useful links for recommended reading on relevant subjects:

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

At this point, I’ve shown 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 noting is that this is still a Beta product, which means that it may differ in functionality and performance compared to the final (RTM) version of SharePoint 2010.

SharePoint

Tobias Zimmergren Twitter

Hey, I'm Tobias! I write about my experiences in designing, architecting, securing, and operating distributed cloud services. Nice to meet you 👋

Reactions and mentions