Programming with External Lists In SharePoint Online (Office 365)

In my previous post I announced that SharePoint Online (SPO) in Office 365 now supports Business Connectivity Services (BCS) and shown an example how to connect SPO via BCS to SQL Azure. In this post we’re going to have a look at what you can do programmatically with external lists in SPO. In general we’ve three options: Sanboxed Solutions, Client Object Model and SharePoint Web Services.

Sandboxed Solutions

Especially using the sanboxed object model to program against  external lists in SPO  sounds promising. Just imagine, event receivers and workflow actions that can access external systems through an external list. I was really excited! But the disillusionment followed soon. It seems you can’t connect external systems in SPO via BCS from within sandboxed solutions!
If I try to programmatically access an external list within a sandboxed web part, I get an “The shim execution failed unexpectedly - Access is denied….” exception.

SPList externalList = SPContext.Current.Web.Lists["Customers"];

foreach (SPListItem externalItem in externalList.Items)
{
  writer.Write(externalList.Item["CustomerID"] + ", ");
  writer.Write(externalItem["CompanyName"] + "<br/>");
}

The sandbox stripes out the user’s security token and this means that the credential mapping (e.g. All Users) defined in the Secure Store Service doesn’t work within in the sandbox. The behavior is described in following MSDN article. The suggested work around is that the managed account that runs the user code proxy service (SPUCWorkerProcessProxy.exe) is mapped to the external credentials. But this is not possible in SPO

Client Object Model

What indeed does work is using external lists with Client Object Model. There is nice post about this from Steve Fox.
You can use the SharePoint ECMA Client Object Model to access the external customer list from my previous post.

<script type='text/javascript'>
ExecuteOrDelayUntilScriptLoaded(function () {

  var ctx = new SP.ClientContext.get_current();
  var web = ctx.get_web();
  var list = web.get_lists().getByTitle('Customers');
  var query = new SP.CamlQuery();

  query.set_viewXml('<View><ViewFields><FieldRef´Name="CustomerID" /><FieldRef Name="CompanyName" /></ViewFields></View>');
var items = list.getItems(query);

//If you try to use a "default" ClientContext.Load of ListItem data from an external list, 

//you will get the following error: "The given key was not present in the dictionary." Instead, you need to explicitly specify the fields you want in the CamlQuery, 

//and also in the ClientContext.Load method.
  ctx.load(items, 'Include(CustomerID, CompanyName)');

  ctx.executeQueryAsync(
    function (sender, args) {
      var strHtml = "";
      var listItemEnumerator = items.getEnumerator();
      while (listItemEnumerator.moveNext()) {
         var item= listItemEnumerator.get_current();
         strHtml += "<p>" +  item.get_item('CustomerID');
         strHtml += "," + item.get_item('CompanyName') + "</p>";
      }
      $get('output').innerHTML = strHtml;

    }, function (sender, args) { alert(args.get_message()); });

 }, "sp.js");

</script>
<div id='output'></div>

Web Services

Another way to program against the external list in SPO is using the SharePoint built-in Lists.asmx web service.

Summary

Although it's nice that SharePoint Online in Office 365 now supports BCS, but as developer I really miss the support for the sandboxed object model. I hope Microsoft will add it with the next service update.