Reading Data from External List using the SharePoint 2010 Client Object model

One of the demos I showed this week was the integration of the new SharePoint 2010 client object model (SP COM) and an external list. The SP COM is a way to work with lists where you create and execute batch commands against your SharePoint 2010 server. You can find more information here: https://channel9.msdn.com/learn/courses/SharePoint2010Developer/ClientObjectModel/. The external list is a special type of list that loads external data sources (SAP, Siebel, CRM, etc.) and provides CRUD operations into the data source.

What was key in this demo is how the COM calls and renders the list. Here is the key code that you use to make this happen in a WinForm app—you can equally apply this to a Silverlight app but remember to use the context.ExecuteAsync() method instead of the ExecuteQuery() method. The code below is dependent on a custom class called Customer that replicates the structure of the list, which you then

…

using SPClientOM = Microsoft.SharePoint.Client;
using Microsoft.SharePoint.Client;

namespace TestClientApp
{
public partial class Form1 : System.Windows.Forms.Form
{
IEnumerable<SPClientOM.ListItem> bcsList;
List<Customer> myCustomers = new List<Customer>();

        public Form1()
{
InitializeComponent();
}

        private void button1_Click(object sender, EventArgs e)
{
SPClientOM.ClientContext context = new ClientContext("https://fabrikamhockey");
            SPClientOM.Web site = context.Web;
SPClientOM.ListCollection lists = site.Lists;
var theBCSList = lists.GetByTitle("Customer_List");
SPClientOM.CamlQuery cq = new SPClientOM.CamlQuery();
IQueryable<SPClientOM.ListItem> bcsListItems = theBCSList.GetItems(cq);
bcsList = context.LoadQuery(bcsListItems);
context.ExecuteQuery();

            var bcsCustomerData = from cust in bcsList
select new Customer
{
custID = cust.FieldValues.ElementAt(1).Value.ToString(),
custName = cust.FieldValues.ElementAt(2).Value.ToString(),
custEmail = cust.FieldValues.ElementAt(3).Value.ToString(),
custRegion = cust.FieldValues.ElementAt(4).Value.ToString(),
custFY08Sales = cust.FieldValues.ElementAt(5).Value.ToString(),
custFY09Sales = cust.FieldValues.ElementAt(6).Value.ToString(),
};

            foreach (var x in bcsCustomerData)
{
Customer tempCustomer = new Customer();
tempCustomer.custID = x.custID;
tempCustomer.custName = x.custName;
tempCustomer.custEmail = x.custEmail;
tempCustomer.custRegion = x.custRegion;
tempCustomer.custFY08Sales = x.custFY08Sales;
tempCustomer.custFY09Sales = x.custFY09Sales;
myCustomers.Add(tempCustomer);
}
dataGridView1.DataSource = myCustomers;
}
}
}

Note that in the code sample, the first  thing you do in the event trigger is to create the SP COM context and then code up the methods and properties that you’ll eventually batch process execute through the ExecuteQuery() method. After you execute the query, you can run a simple LINQ query against the data and then iterate through the results to populate the List collection. You then use the List collection to bind to the data grid.

An interesting finding was that in .NET Connector external lists that you’ve created and deployed using VS 2010, there is a BDC Identifier that occupies the first returned element index position (hence the reason I’ve started at ElementAt(1) – i.e. the first value that is actual data). Keep this in mind when you code, deploy and work with these types of lists.

I’ve posted the full code for this here: https://cid-40a717fc7fcd7e40.skydrive.live.com/browse.aspx/SPConn.

Steve