SharePoint Integration With Automation: Part 1

I’ve been using this pattern for > 6 months now. It’s been working great for using SharePoint as a UI host and data host – which is itself a very productive pattern; you’ve got mediocre templates that DO work right out of the box (as opposed to NOT having them, this is a plus), the SharePoint designer is free to download and use, its fairly self explanatory, and you don’t have to stand up things like database servers and application tiers just for a small little project.

This is Part 1, where I’ll share what code I have that talks to SharePoint and discuss how it works. In Part 2, we’ll look at specific jobs in UC4 that consume the output of that code. What we won’t be covering is how to create basic workflows and lists, etc. in SharePoint – that’s a topic that’s already well covered out there on teh intratubez.

The basic idea is you use SharePoint UI – whether its the form of InfoPath, Workflow, Web Parts, or Custom Web Parts – to host all the interaction with the user. Any data that UI needs gets pre-loaded to SharePoint Lists, and any data that the UI gathers from users gets saved to other Lists. This technique can be used both to get that user-generated data out of SharePoint, and to pre-populate any Lists or lookup tables the UI needs.

For the following sample you’ll need a service account created that has access to your site. The below code assumes you stored this information in variables named ServiceUser, ServicePassword, and ServiceDomain – you’ll need to work with corporate security and/or your sharepoint admins to get that account created and add the appropriate information to your program. You’ll also need to, at a minimum, create one list to use. You can put whatever you want in it, but be sure to add a boolean / checkbox column named “Handled” that defaults to false.

First, you need to create a new project in Visual Studio. I’m using Visual Studio 2010 – this will probably work in 2012 as well, but I wanted a version matched to the SharePoint we use. We’re going to be creating a C# Console solution. Go to File -> New -> Project and select Visual C# -> Windows on the left pane and Console Application on the right.


Next you need to add a reference to SharePoint Lists in your project. Right-click the Service References folder and select “Add Service Reference” Enter the path to your SharePoint Site or SubSite Lists service, which is hosted in /_vti_bin/listdata.svc – so if your site is “http://sharepoint/teams/automation” your list service will be “http://sharepoint/teams/automation/_vti_bin/listdata.svc” Click Go and under Services you should have one service that is selected (name will depend on the site you’re on but should end in “DataContext” – mine in the MobileDeviceManagementRollout site was named “MobileDeviceManagementRolloutDataContext”, for instance. Change the namespace to SharepointData and click OK. This will add a new Service Reference to your project named “SharepointData”


In your solution, in the static void Main(string[] args) method – you need to create a new data context to represent the connection to SharePoint in code. Whatever data context was displayed for the service reference in the previous step is the object type you need to create.

MobileDeviceManagementRolloutDataContext dc = new MobileDeviceManagementRolloutDataContext(new Uri("YourSharepointPath/_vti_bin/listdata.svc"));dc.Credentials = new System.Net.NetworkCredential(ServiceUser, ServicePassword, ServiceDomain);

Now, since we’re creating a console app, we need ways for the automation component to pass data in. We’ll be using the command line for this. My general pattern is to pass in nothing when I want a straight dump of the list and to pass in parameters such as “-mark id” when I want to modify the list, so this is the if statement I generally use. Put this in your code after the above:

if (args.GetUpperBound(0) >= 1 && args[0].ToLower().Equals("mark"))

We’ll put the marking code in the true condition and the default code in the else condition here. Let’s start with the else condition:

var result2 = dc.ListName
    .Where((i.Handled == false) || (i.Handled == null));
foreach (var e in result2)
    Console.WriteLine(String.Format("MyList;{0};{1}", e.Id, e.Username.Split('|')[1]));

The above code uses Linq to perform the query on the sharepoint list. You’ll want to modify the above query to point to the proper list, and to have any additional parameters you may need; for instance, in the code I ripped this sample out of the actual query was .Where(i => i.ManagerAgreement.Equals(“I agree”) && ((i.Handled == false) || (i.Handled == null))); – in other words, only export the row if ManagerAgreement = “I agree” in addition to the rest. Make sure to leave the i.Handled logic in there – that’s what will ensure we only process each row once. On the Console.WriteLine above you’ll want to modify the String.Format for the data you need to export. As you can see, its very easy – just use dot notation on the iterator variable (the one specified in foreach) and it should present a menu of available data in the list.

When you run the above code (and it should be ready to run now – go ahead and try it if you’re following along) you should get the contents of the list, one line at a time, on the output:


and so on… this is a great start, but we still need a way for the automation component(s) to mark a row as having been handled. For this we’ll go back to the if statement and fill in the true path with the following code:

var result2 = dc.ListName
    .Where(i => i.Id == int.Parse(args[1]));
foreach (var e in result2)
    e.Handled = true;

This is very similar to the other code except we’re parsing the ID and using that in the where clause – recall that args[0] is “-mark” in this case, so we expect args[1] to be the ID of the item to be marked. We again use dot notation to access the data in the iterator – but in this case, you need to call dc.UpdateObject(), passing in the iterator, to update the local data context with the changed data. Once you’ve updated all the local data, you call dc.SaveChanges() to persist the changes to SharePoint.

That’s it for part 1, you now have a program that can export list data and modify the list data. You can use .NET programming to make any changes you like. In part 2, we’ll go over the UC4 objects to call, parse, and act on this integration.


I've been automating everything I can get my hands on since I was a wee lad, these days its mostly Office, UC4, or VMWare - but I have a strong interest in AI, microfluidics, and 3D printing when I'm not slaving for "da man"

Tagged with: , , , , , , , , , , ,
Posted in Automation, SharePoint

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Head Shot of Jim

Jim McCracken

Enter your email address to follow this blog and receive notifications of new posts by email.

Follow me on Twitter
Past Posts
Automate The Cloud pages
%d bloggers like this: