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

Comments

• Anonymous
  January 19, 2010
  Hi Steve, Couple of questions about the whole BCS thing:

1. Why is it that you've got to write a wrapper around a webservice to expose those CRUD methods? As it was with BDC I kinda liked it; just specify the service methods in the ADF and you were good to go. Is that still an option, or do you always have to write a cs class which consumes the webservice and re-exposes the CRUD methods?
2. When an external list is made, is it possible to attach things like event receivers and workflows to it? If so; when are those triggered? When a user adds / modifies an item through SharePoint / Client OM? Or maybe even when an item changes in the LOB system (which I would find great but I don't really expect that). Thanks, hope you're enjoying that well deserved beer this evening ;)