SP 2010: Programmatically work with External Lists (BCS) in SharePoint 2010

Tobias Zimmergren
Tobias Zimmergren
ScriptRunner Webinar

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

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

In my previous article, I talked about how you can set up a simple BCS configuration to fetch and work with external data in SharePoint 2010. In this article, I will talk about how you can utilize the SharePoint 2010 object model to work with that external data, directly from the SharePoint API. It’s all farily simple!

Working with data in an External List using the SharePoint Object Model

The code in this sample doesn’t really differ from the way you fetch information from any other list in SharePoint (2007 or 2010). This – of course – is very welcomed news, as we do not need to learn any new frameworks or tools to work with the data in our external lists. It simply works like any other SPList, basically.

Retrieving external data, made simple:

When fetching items from an external list, you can simply do that by utilizing the good-old SPList object. We do not need to work with any other types of namespaces or frameworks to do this.

In my SQL Server, I’ve got a table called ProductList.
This list is filled with the following data:

image

Fetching some items from the external list and displaying them in a console app:

// Product List is my external list, that is working with data in the SQL Server!

SPList list = web.Lists["Product List"];
SPQuery q = new SPQuery();
q.Query ="";
q.RowLimit = 100;
SPListItemCollection col = list.GetItems(q);
foreach (SPListItem item in col)
    Console.WriteLine(item["Name"].ToString());

This will render the following result (fetched from the database):

image

The things you see in the console windows are fetched straight from the SQL Server (using a BCS connection through the External List).

Writing data to the External List (hence, writing to the SQL Server)

Similarly, it's straightforward to write data back to the external list.

// Get the external list
SPList list = web.Lists["Product List"];

// Use the traditional approach to create SPListItems and hook it up with the list
SPListItem item = list.Items.Add();
item["Name"] = "Sample Product Wohoo";
item["Description"] = "Sample Description Wohoo";
item.Update();

Upon running this code in your SharePoint application, it will create the SPListItem object and add a Name and Description. Then you make a call to the Update() method it will push this data through the data source connection, to your SQL server.

Here’s what the updated data looks like:

image
SharePoint 2010 with BCS External Lists from C# (.net)

We’re running a Beta-product!

There are many new capabilities to use in SharePoint 2010 – where the BCS is one. This article discusses how you can retrieve information from these lists using the normal API approach.

At the time of this writing (during Public Beta), there aren’t any measures on performance and what impact it has on the server in comparison to alternative ways to fetch and work with the data.

As time goes on, there will probably be some new information on this – I’ll keep you posted when I know more.

Summary

As you can see, working with external data from the SharePoint API isn’t very hard to do. What you need to make sure is to have an external list set up somewhere (see this article for how you can do that) and then you can simply use the normal SPList object from the SharePoint object model to work with the external list and it’s external data from the SQL server (in my case).

So if you haven’t already: Get on the SharePoint 2010 wagon and enjoy the ride!

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


Presently sponsored by:
Hornet Security
Mastodon