Wictor Wilén

When I first heard about SharePoint Online at the PDC 2008 I was a bit disappointed that you could not use custom code but had to rely on the built-in functionality and the things you could do with SharePoint Designer (which is quite powerful anyway, especially with jQuery).

To read more about SharePoint online, head over to Tobias Zimmergrens blog.

But with some clever techniques you can take advantage of the Windows Azure Hosted Services and create your custom code. I will show you how to create some custom code, which normally is done by SharePoint event receivers or timer jobs, using a Worker Role in Windows Azure.

The post is quite lengthy but I have skipped some of the code snippets to make it easier to read. You will find downloads of the C# code at the end of this post.

Scenario

This sample assumes that we have a Document Library in SharePoint Online containing Word 2007 documents for Proposals. The library is called Proposals. We want to make sure that our Proposals has all comments removed before we send it away to our clients. This comment removal procedure will be initiated by a workflow.

The Document Library

The Proposals Document Library is a standard SharePoint Document Library with versioning enabled. First we add a new column to the library called Action. This column will contain the status of the comment removal procedure. It has the type Choice and three possible values; RemoveComments, Processing and Done.

The workflow

Using SharePoint Designer we create a new workflow, called Prepare Proposal. The workflow has three stages. The workflow is initiated manually

1. Set the Action column to the RemoveComments value
2. Wait for Action column to get the value Done
3. Send e-mail the the author

Pretty simple workflow but all using the out-of-the-box SharePoint Designer activities.

So far everything is basic SharePoint Online “development”, but now we head on over to the custom coding parts.

Create the Hosted Service Worker Role

Using Visual Studio 2008 and the Windows Azure SDK we can create a new project of the type Worker Cloud Service. This cloud service will contain all our code for removing the comments from our Word documents.

When the project is created, actually a solution with two projects, we add two references to our Worker Role project; WindowsBase.dll and DocumentFormat.OpenXml.dll (from Open XML SDK 1.0). The Open XML reference must also have the Copy Local property set to true, so that the assembly is deployed with the hosted service App Package.

Note: Version 2 of Open XML SDK can not be used since it does not have the AllowPartiallyTrustedCallers attribute, which is required for all referenced assemblies in Windows Azure.

Add a service reference

The worker role will use the SharePoint Online web services to read and update data so we need to add a Service Reference to our SharePoint online site and the Lists.asmx web service. When this is done an App.config file is created, just delete it - we have to set our bindings and endpoints in the code so it is fully trusted.

In your worker role Start method you create code as follows:

  1: BasicHttpBinding binding = new BasicHttpBinding();
  2: binding.Security.Mode = BasicHttpSecurityMode.Transport;
  3: binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Basic;
  4: binding.Security.Transport.ProxyCredentialType = HttpProxyCredentialType.None;
  5: binding.Security.Transport.Realm = WorkerRole.Realm;
  6: binding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.UserName;

WorkerRole.Realm contains a string with your SharePoint Online Realm with a value something like this: “XXXXXmicrosoftonlinecom-1.sharepoint.microsoftonline.com”.

Let’s code!

The worker role will poll the Proposals library for new documents which has the RemoveComments value in the Action column. This is done with the GetListItems method and a query selecting only those documents.

  1: XElement query = new XElement("Query",
  2:     new XElement("Where",
  3:         new XElement("Eq",
  4:             new XElement("FieldRef",
  5:                 new XAttribute("Name", "Action")
  6:                 ),
  7:             new XElement("Value",
  8:                 new XAttribute("Type", "Choice"),
  9:                 "RemoveComments"))));
 10: 
 11: XElement queryOptions = new XElement("queryOptions");
 12: 
 13: var itemsXml = client.GetListItems("Proposals", string.Empty, query.GetXmlElement(), null, string.Empty, queryOptions.GetXmlElement(), string.Empty);
 14: 
 15: XNamespace s = "http://schemas.microsoft.com/sharepoint/soap/";
 16: XNamespace rs = "urn:schemas-microsoft-com:rowset";
 17: XNamespace z = "#RowsetSchema";
 18: 
 19: var docs = itemsXml.
 20:     GetXElement().
 21:     Descendants(z + "row").
 22:     Select(x => new {
 23:         Title = (string)x.Attribute("ows_LinkFilename"),
 24:         Id = (string)x.Attribute("ows_ID"),
 25:         Url = WorkerRole.Url + "/Proposals/" + (string)x.Attribute("ows_LinkFilename")
 26:     });
 27: 

Now we have all our proposals in the docs variable and we can iterate over it.

  1: foreach (var doc in docs) {
  2:     RoleManager.WriteToLog("Information", "Processing: " + doc.Title);
  3:     setStatusOnDocument(client, doc.Id, "Processing");
  4: 
  5:     WebRequest request = HttpWebRequest.Create(doc.Url);
  6:     request.Credentials = new NetworkCredential(WorkerRole.Username, WorkerRole.Password);
  7: 
  8:     MemoryStream mems = new MemoryStream();
  9: 
 10:     using (WebResponse response = request.GetResponse()) {
 11:         using (Stream responseStream = response.GetResponseStream()) {
 12:             byte[] buffer = new byte[8192];
 13:             int offset = 0;
 14:             int count;
 15:             do {
 16:                 count = responseStream.Read(buffer, 0, 8192);
 17:                 offset += count;
 18:                 mems.Write(buffer, 0, count);
 19:             } while (count != 0);
 20:             using (WordprocessingDocument wpDoc = WordprocessingDocument.Open(mems, true)) {
 21:                 RemoveComments(wpDoc);
 22:             }
 23:             using (WebClient wc = new WebClient()) {
 24:                 wc.Credentials = new NetworkCredential(WorkerRole.Username, WorkerRole.Password);
 25:                 wc.UploadData(doc.Url, "PUT", mems.ToArray());
 26:             }
 27:         }
 28:     }
 29:     setStatusOnDocument(client, doc.Id, "Done");
 30:     RoleManager.WriteToLog("Information", "Comments removed from: " + doc.Title);
 31: }
 32: 

First of all we have a method which updates the Action column of the item to Processing to indicate to the users that we are currently processing this document. Then we read the document into a stream and pass it into a WordprocessingDocument object. On that object we do the RemoveComments method which removes all comments in the Word 2007 document.

Note: Have a look at Eric Whites blog for some nifty operations on your Open Xml documents, such as the remove comments function.

When all comments are removed we upload the file back to SharePoint and set the status to Done, and we are all set.

As you can see I use three different techniques to read from the SharePoint Online site; the SharePoint web services, request-response and a simple WebClient. Isn’t coding fun!

Test

One of Windows Azures beauties is that you can test it on your local machine using the Azure Development Fabric. Just hit F5 in Visual Studio.

This allows you to debug and fine tune your code and see how it scales out.

Deploy to Windows Azure Staging environment

When you are sure that there are no bugs just choose Publish in Visual Studio and it will open up a web browser which directs you to the Azure Services Developer Portal, there you can create a new Hosted Service Project or choose an existing one to use for this Proposals service.

To deploy this solution we have to upload two files; the  App Package containing all your code and references and the Configuration Settings file, both automatically created by Visual Studio.

When this is done you have to wait a few minutes so Windows Azure can allocate resources for you and prepare the environment. Once this is done you are ready to test your service in the cloud.

Just hit the Run button and after a minute or so the Proposals Service is running - in the cloud!

Deploy to Production

For us used to the SharePoint environment and the mess it is to get from development to staging to production environment it’s so much fun to just hit Deploy in Windows Azure to move the Staging service to Production.

As you can see from the image above this solution is now in production and started with one worker role.

Run the workflow

Now we can test our complete solution by uploading a document containing some comments. Then initiate the Prepare Proposals Workflow.

The document will be updated with a new value in the Action column.

After a few seconds the document is updated by the cloud worker role we created, the workflow completes, there are no more comments in the document and the author should have received an e-mail with information that the workflow is done.

Summary

This was a short demonstration on what you can do with the standard functionalities of Microsoft's cloud offerings such as SharePoint Online and Windows Azure. The scenario was quite simple but using the same techniques you can elevate your SharePoint Online experience and functionality a lot.

Using Windows Azure is one possibility to host your online custom code, hosting it on on-premise servers is another option and it works just as fine.

Note: I have not made this sample so it can scale out nor does it handle any exceptions. Take it what its for.

If you would like to look at the code you can find it here.

Happy coding!