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

Introduction

Article 2 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 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 really 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 as 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 in order to do this.

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

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 =
“<Where><IsNotNull><FieldRef Name=’ProductID’ /></IsNotNull></Where>”;
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):

The things you see in the console windows is 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)

Seriously, this is way too easy as well…

// 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. When you hit .Update() it will push this data through the data source connection, to your SQL server.

Here’s what the updated data looks like:

We’re running a Beta-product!

As you can imagine, there’s a ton of new cool things to work with in SharePoint 2010 – where the BCS is one. This article discuss the very basics of how you can retrieve information from these lists using the normal API-approach.

At the time of this writing (during Public Beta) there isn’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 be 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!