Practical .NET

Shorten Your Backlog: Integrating ASP.NET and SharePoint

Integrating your .NET line-of-business applications with SharePoint is easy to do and allows you to transfer a ton of work from your desk to your users' desks.

• By Peter Vogel
• 03/18/2013

You've just been told that your organization needs an application to manage some relatively fluid process: expediting orders to customers (getting an order to a customer as fast as possible), managing customer complaints (anything from offering a refund to restructuring the service process) or some equally unstructured process. These kinds of business processes often involve coordinating members of multiple departments and managing unstructured information while still setting and managing deadlines. They're not like building the line-of-business (LOB) applications you're used to -- applications like a sales order system.

You could write this new application yourself. Or you could let your users build the application they want in SharePoint (using the SharePoint UI) and just tie SharePoint into your LOB application. For instance, your users might decide to start with a SharePoint task list (treating each order as a task to be performed), or a SharePoint calendar (treating each complaint as a set of scheduled deadlines or events), or a SharePoint document library (gathering together all the related information for solving the problem). Whichever format your users choose, they can add or remove the data elements they need (adding, for instance, a new field to hold an OrderId or CustomerID) or even create whole new data elements (for instance, a dedicated Status field that specifies the current state of an order).

But the important thing is that you don't have to build it. And, because your users understand the business process better than you do, they will also probably do a better job than you. All you have to do is integrate your LOB application with the SharePoint application.

I'm going to use the order-expediting example for my case study. Initially, I'll assume that the only integration required between the LOB application and the SharePoint list built by the users is adding orders from the LOB application to the list when an LOB user marks an order as "to be expedited." Adding the orders includes transferring whatever information the users who set up the list require.

SharePoint Weirdness
This isn't as easy as I'd like to make it out to be: SharePoint is different from other development environments. For instance, to access a SharePoint site from another .NET application, you'll use the SharePoint client-side object model (CSOM), which requires you to add some references to DLLs to your LOB project. The libraries that support the CSOM for SharePoint 2010 are available as a NuGet package.

For SharePoint 2013, however, you'll need to download the Microsoft Office Developer Tools for Visual Studio 2012. After downloading the package you'll need to add references to two DLLs: Microsoft.SharePoint.Client.Runtime.dll and Microsoft.SharePoint.Client.dll (browse to your Program Files folder and look in Common Files | Microsoft Shared | Web server extensions | 15 | ISAPI for the DLLs).

To access the SharePoint list from your LOB application, you'll need two pieces of information: the URL for the SharePoint site and the actual name of the list, which may not be the name displayed on the list's page in SharePoint. The easiest way to get the URL for the SharePoint site is to surf to the SharePoint site in the browser of your choice and copy the front part of the URL (my site's URL is http://spdev/sites/phvcol). To get the actual name of the list, surf to the list's page in the SharePoint site and click on the list choice in the page menu bar. Then, in the SharePoint Ribbon, click on the List Settings choice. The name of the list is displayed at the top of the page (my list is called "Orders to Expedite").

The CSOM communicates with SharePoint through a set of Web services installed with SharePoint. Frequent trips to those Web services will slow down your application so, to avoid that, the CSOM gives you a ClientContext object that lets you stack up requests and then execute all the stacked up requests in one trip. It's not difficult code to write, but it is … different.

Adding an Item
To add a sales order to the SharePoint "Orders to Expedite" list, you first need to request the list. You begin by creating the SharePoint ClientContext object, which handles communication with SharePoint Web services, passing the URL for the site that you want to work with. You can then request the list by its name using the GetByTitle method of the site's lists collection (SharePoint refers to "sites" as "Webs" in the CSOM). You'll need to add a using/Imports statement for Microsoft.SharePoint.Client to make the following code work:

ClientContext cc = new ClientContext("http://spdev/sites/phvcol");
List exOrdList = cc.Web.Lists.GetByTitle("Orders To Expedite");

To add an item to the list, you create a ListItemCreationInformation object and pass it to the list's AddItem method (the name of the ListItem class has to be fully qualified with its namespace to prevent it from being confused with ListItems classes in other namespaces):

ListItemCreationInformation lici = new ListItemCreationInformation();
Microsoft.SharePoint.Client.ListItem li = exOrdList.AddItem(lici);

Now you can set the values for whichever fields in the ListItem your users have specified and then stack the update for processing by calling the ListItem's Update method. This code sets three fields before stacking the request:

li["Title"] = "A123";
li["PercentComplete"] = "0";
li["DueDate"] = DateTime.UtcNow;
li.Update();

Up to now you haven't communicated with SharePoint. So, the last step is to call the ClientContext's ExecuteQuery method to send your stacked requests (get the list and add the ListItem) to SharePoint for processing:

cc.ExecuteQuery();

There -- you've added a ListItem to your users' SharePoint list. Ideally, you'd be done at this point, but that's probably not realistic.

Retrieving Items
The integration between your SharePoint site and your LOB application will probably require more than just the ability to add items to the SharePoint list. For instance, your LOB application's users will probably want to look at the current status of the expedited order in the SharePoint list without having to switch to SharePoint.

For this example, I'll assume that my LOB application only needs to retrieve individual ListItems from the SharePoint list. Even with that assumption, checking every order as it's retrieved to see if it also appears in the SharePoint list would be foolish. So, part of your integration effort will probably include flagging those orders added to the SharePoint list by adding a new column to the SalesOrder table to indicate if an order is being expedited (you'd update this field as part of the code that adds the item to the list).

With that in place, you're ready to retrieve the ListItem for an expedited order from SharePoint. As before, the process begins by creating a ClientContext object aimed at the SharePoint site and requesting the list:

if (salesOrder.Expedited == true) 
{
  ClientContext cc = new ClientContext("http://spdev/sites/phvcol");
  List exOrdList = cc.Web.Lists.GetByTitle("Orders To Expedite");

The next step is to extract the ListItem that you want from the ListItems in the "Orders to Expedite" list. The list object doesn't, unfortunately, have an Items method. (Did I mention that SharePoint is weird?) But the list does have a GetItems method that will return the result of a query written in the SharePoint query language (Collaborative Application Markup Language, or CAML). Good news: You don't have to learn CAML. You can, instead, use LINQ and let the CSOM handle the conversion to CAML for you. But you do need some placeholder CAML to make the GetItems method happy.

This code creates the simplest possible CAML query (using the SharePoint CAMLQuery object) and passes that to the list's GetItems query to get a collection that can be used as the target of a LINQ query:

CamlQuery dummyQuery = new CamlQuery();
dummyQuery.ViewXml = "<View/>";
Microsoft.SharePoint.Client.ListItemCollection exOrds = exOrdList.GetItems(dummyQuery);

Do remember, though: Until the ExecuteQuery method is called, nothing is passed to or requested from the SharePoint site. At this point my exOrds variable is just a request for a collection of SharePoint ListItems.

Selecting, Updating, Deleting
I could retrieve all of the ListItems from the SharePoint list and search that collection for the ListItem that I need. That wouldn't be a bad strategy if the list wasn't volatile and wasn't very large (in the range of several dozen ListItems, for instance). I could retrieve the ListItems collection, store it somewhere in my application (in the ASP.NET Cache object, for example) and process all subsequent requests against that collection, eliminating further trips to SharePoint.

However, for this example I'd prefer for the single ListItem that I want to be selected by SharePoint and sent over the wire to my application. With that in mind, the last two steps in retrieving the ListItem are to use the ClientContext Load method to stack the ListItemCollection for retrieval along with a LINQ expression that specifies the ListItem that I want. That LINQ statement appears as part of a lambda expression in the second parameter of the Load method. The lambda expression is passed the collection specified in the Load method's first parameter.

This example uses a LINQ statement in the second parameter to find a ListItem based on the value in the Title field:

cc.Load(exOrds, ords => 
        from ord in ords
        where ord.FieldValuesAsText["Title"] == this.SalesOrderTextBox.Text
        select ord);
cc.ExecuteQuery();

You can now retrieve the first (and, in my case, the only) ListItem in the collection and update your page with the value from one of the fields in the ListItem:

this.StatusTextBox.Text = exOrds.First().FieldValues["Status"].ToString();

Having come this far, it makes sense to show the code for updates and deletes. The code to update a ListItem is, for SharePoint, reasonably obvious: retrieve the item, change the appropriate field and call the Update method. When you use the Update method, you don't need to call the Load method -- just call ExecuteQuery to send your request back to the server. The code in Listing 1 updates the DueDate to move the order out three months.

Listing 1. Updating the DueDate

ClientContext cc = new ClientContext("http://spdev/sites/phvcol");
List exOrdList = cc.Web.Lists.GetByTitle("Orders To Expedite");
CamlQuery dummyQuery = new CamlQuery();
dummyQuery.ViewXml = "<View/>";
Microsoft.SharePoint.Client.ListItemCollection exOrds = exOrdList.GetItems(dummyQuery);
cc.ExecuteQuery();
Microsoft.SharePoint.Client.ListItem exOrd = exOrds.First();
exOrd["DueDate"] = DateTime.UtcNow.AddMonths(2);
exOrd.Update();
cc.ExecuteQuery();

Deleting an item is even easier: Instead of updating any fields on the ListItem, just call its DeleteObject method (which also stacks the request) before calling the ExecuteQuery method:

Microsoft.SharePoint.Client.ListItem exOrd = exOrds.First();
exOrd.DeleteObject();
cc.ExecuteQuery();

ClientContext cc = new ClientContext("http://spdev/sites/phvcol");
List exOrdList = cc.Web.Lists.GetByTitle("Orders To Expedite");
cc.Load(exOrdList.Fields);
cc.ExecuteQuery();

var listFields = exOrdList.Fields.ToList();
var orderedFields = from fld in listFields
                    orderby fld.Title
                    select fld;

foreach (var fld in orderedFields) {
  Debug.Print(fld.Title + " = " + fld.InternalName);
}

You could, of course, create an order-expediting LOB application yourself in the development tool of your choice -- an excellent strategy, provided that you're paid by the hour. Hopefully, you'd give your users the application they need. But (depending on how much integration code you want to put into your LOB application) a days' worth of work on your part might be all that's necessary to integrate with a SharePoint app your users build.

Did I mention, by the way, that your SharePoint users set up a workflow to send a notification e-mail to the relevant people whenever an item is added to the list? Again, something you didn't have to do.