Wednesday, 26 January 2011

Sharepoint 2007 Batch Update example using web services and CAML

Here is an example of running a batch update process on Sharepoint 2007 using web services. It was written in C# in Visual Studio 2008.

In this example, I take the value of a text field "Date_x0020_of_x0020_Birth", turn this into a DateTime value, and then use this to update the the value of a DateTime field "Date_x0020_of_x0020_Birth0". Obviously this section of the code will not be particularly generalisable, however I have left in all the code in order to show you the principles involved, and hopefully you will find it useful in building your own batch routines.

In order to get it running, you must create a reference to the Sharepoint Lists web service and call it "ListsService". The SDK shows you how to do this, and is available here:

The GetListItems() method of the Lists web service is used to query the data, and the UpdateListItems() method is used to update items.

I used an XPathNavigator object to filter only rows that contain data. You could just loop through the nodes if you prefer.

CAML is used to query the Sharepoint list and look for items where the "Date_x0020_of_x0020_Birth" is null. Tailor this to your own needs.
For CAML reference, look at the MSDN article here:

The CAML update command uses owshiddenversion, and so is useful in situations where versioning is enabled. Lists without versioning do not need this field.

If the UpdateListItems command returns an error, in my experience this is usually caused by using an incorrect internal name for a field.  You may need to add the URI prefix "urn:schemas-microsoft-com:office:office#" to the field name (e.g. change line 42 to something like:  "<Field Name=\"urn:schemas-microsoft-com:office:office#{4}\">{5}</Field>").

Full code:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using System.IO;
using System.Xml;
using System.Xml.XPath;
using System.Net;

namespace BatchUpdateDOB
    class Program
        static string LogFile = "D:\\BatchUpdateLog.txt";  // Log file location

        static void Main(string[] args)
                StatusMessage("Writing log file to: " + LogFile);
                // Set up the variables to be used.
                StringBuilder methodBuilder = new StringBuilder();
                string batch = string.Empty;
                DateTime currentDate = DateTime.Now;
                string formattedDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentDate);
                string fieldToReference = "Date_x0020_of_x0020_Birth";
                string fieldToUpdate = "Date_x0020_of_x0020_Birth0";

                string batchFormat = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                  "<ows:Batch OnError=\"Return\">{0}</ows:Batch>";

                // CAML update command for web service
                string methodFormat = "<Method ID=\"1\" Cmd=\"Update\">" +
                 "<Field Name=\"ID\">{1}</Field>" +
                 "<Field Name=\"FileRef\">{2}</Field>" +
                 "<Field Name=\"owshiddenversion\">{3}</Field>" +
                 "<Field Name=\"{4}\">{5}</Field>" +

                string siteName = "http://<your site root uri here>";
                string listName = "<your list name here>";

                //string viewName = "";  // No need for viewname
                int rowLimit = 10;       //The number of items returned per batch (not used)

                StatusMessage("Processing " + siteName + ", List: " + listName + "...");

                //Web service setup
                //ListService should be the name of your web service reference
                ListService.Lists listService = new ListService.Lists();
                listService.Credentials = System.Net.CredentialCache.DefaultCredentials;
                listService.Url = siteName + "/_vti_bin/Lists.asmx";

                //Build CAML query to search for records
                XmlDocument camlDocument = new XmlDocument();
                XmlNode queryNode = camlDocument.CreateElement("Query");
                //queryNode.InnerXml =  "<Where><IsNull><FieldRef Name='Date_x0020_of_x0020_Birth'/></IsNull></Where>";
                //queryNode.InnerXml = "<OrderBy><FieldRef Name='Country' /></OrderBy>";

                XmlNode viewFieldsNode = camlDocument.CreateElement("ViewFields");
                viewFieldsNode.InnerXml = "<FieldRef Name='Title' />" +
                  "<FieldRef Name='owshiddenversion' />" +
                  "<FieldRef Name='" + fieldToReference + "' />" +
                  "<FieldRef Name='" + fieldToUpdate + "' />" +
                  "<FieldRef Name='FileRef' />";

                XmlNode queryOptionsNode = camlDocument.CreateElement("QueryOptions");
                queryOptionsNode.InnerXml = "<ViewAttributes Scope=\"Recursive\" /><IncludeMandatoryColumns>True</IncludeMandatoryColumns>";

                // Get the GUID of the list
                XmlNode ndListView = listService.GetListAndView(listName, "");
                string listGuid = ndListView.ChildNodes[0].Attributes["Name"].Value;
                string listVersion = ndListView.ChildNodes[0].Attributes["Version"].Value;

                // Run the CAML query
                XmlNode resultNode = listService.GetListItems(listGuid, null, queryNode, viewFieldsNode, rowLimit.ToString(), queryOptionsNode, null);
                // If you want to put a rowlimit on, then use this command:
                //XmlNode resultNode = listService.GetListItems(listGUID, null, queryNode, viewFieldsNode, rowLimit.ToString(), queryOptionsNode, null);

                long totalRecords = 0;
                //string itemDOB;
                string dob;
                DateTime dtDOB;
                string owsHiddenVersion;  
                string fullFileRef;
                string owsFileRef;

                // Create xpath navigator   
                XPathNavigator navigator = resultNode.CreateNavigator();
                // Set up namespace manager for XPath   
                XmlNamespaceManager ns = new XmlNamespaceManager(navigator.NameTable);
                ns.AddNamespace("rs", "urn:schemas-microsoft-com:rowset");
                ns.AddNamespace("z", "#RowSetSchema");

                // Select only the z:row nodes
                XmlNodeList nodes = resultNode.SelectNodes("//*[name()='z:row'] ", ns);

                // Iterate through the returned nodes
                foreach (XmlNode node in nodes)
                    string itemID = node.Attributes["ows_ID"].InnerText;
                        dob = node.Attributes["ows_" + fieldToReference].InnerText;
                        owsHiddenVersion = node.Attributes["ows_" + "owshiddenversion"].InnerText;
                        owsFileRef = node.Attributes["ows_" + "FileRef"].InnerText;
                        // For some reason my data is returned in the format: 2#/listname/path, 
                        // where 2 seems to be an id or version number, and listname/path points to the file in relation to the root site.
                        // The following strips that out and gets the full path (required for the CAML update)
                        fullFileRef = siteName + "/" + owsFileRef.Remove(0, owsFileRef.IndexOf("#") + 1);
                    catch(Exception ex)
                        // Ignore this row
                        //StatusMessage("ERROR: Cannot process: " + node.InnerXml.ToString() + ": " + ex.Message); // If you want to see why, uncomment

                    if (String.IsNullOrEmpty(dob)) continue;  // Ignore list items with no value

                    dtDOB = Convert.ToDateTime("01/01/1900"); // Set default value for dates before 1900

                    // Convert date text to DateTime
                        dtDOB = Convert.ToDateTime(dob);
                    catch (Exception ex)
                        StatusMessage("Cannot process: " + siteName + "/" + fullFileRef + " DOB: " + dob + ".  Setting to 01/01/1900. " + ex.Message);

                    MessageToFile("Processing: " + fullFileRef + ", DOB = " + dob);

                    methodBuilder.AppendFormat(methodFormat, listGuid, itemID, fullFileRef, owsHiddenVersion, fieldToUpdate, SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtDOB));

                    // Put the pieces together.
                    batch = string.Format(batchFormat, methodBuilder.ToString());

                    //Build CAML command to update using Web service
                    XmlDocument xmlDoc = new XmlDocument();
                    XmlElement elBatch = xmlDoc.CreateElement("Batch");
                    elBatch.SetAttribute("OnError", "Continue");
                    elBatch.SetAttribute("ListVersion", listVersion.ToString());
                    elBatch.SetAttribute("ViewName", "");
                    elBatch.InnerXml = methodBuilder.ToString();

                    // This line runs the CAML update command
                    XmlNode updatesResponse = listService.UpdateListItems(listGuid, elBatch);
                    if (updatesResponse.FirstChild.FirstChild.InnerText != "0x00000000") throw new Exception("Could not update properties.\n\n" + updatesResponse.InnerText + "\n" + updatesResponse.FirstChild.FirstChild.InnerText);
                    StatusMessage(fullFileRef + " processed...");

                StatusMessage("Completed - " + totalRecords + " processed.");
            catch (Exception exp)
                StatusMessage("ERROR: " + exp.Message);

        /// <summary>
        /// Display a  message
        /// </summary>
        /// <param name="myMessage"></param>
        static void StatusMessage(string myMessage)

        /// <summary>
        /// Write message to the log file
        /// </summary>
        /// <param name="myMessage"></param>
        static void MessageToFile(string myMessage)
            using (StreamWriter sw = new StreamWriter(LogFile, true))
                DateTime now = DateTime.Now;
                sw.WriteLine(now.ToString() + ": " + myMessage.ToString());

Tuesday, 11 January 2011

Binding a CheckBoxList object to an ordered Sharepoint list of reference values in a web part (c#)

It's common when creating database applications to use reference tables to provide lookup values.  These tables ofter contain a flag that is used to mark a record as no longer available, and an order column that provides a method of putting the values in an order of importantance, perhaps when using them in a drop down list or list of checkboxes.  Sharepoint is NOT a database, however there is no reason why good practice such as this cannot be applied to Sharepoint's list model.

In the following example I will show you how to set up a reference list and then use the Sharepoint object model to programmatically access this list and bind it to an control (in this case a Sharepoint CheckBoxList control).  Once you understand this, you can use the same method to connect to any reference list and bind it to other objects and controls, such as drop down lists. This example has been developed for Sharepoint 2007, but should also work for Sharepoint 2010.

Step 1:  Set up a reference list
Create a new list on your Sharepoint site with teh following structure: 
  • ID - integer (this is a default field) - this will be used as the unique key (lookup value) used to link this reference filed to other lists
  • Title - string (this is a default field) - this is used to hold the description of the reference field, and will be the value shown in lookups
  • SortOrder - integer, default 1000 - this can be used to sort the list values in lookups and programmatically using CAML
  • Enabled - Yes/No field, default Yes - setting to NO will enable you to filter out these values in views and in programmatic lookups in CAML
You can now enter you reference values into the list, for example here is a list of types of medical record:

Step 2:  Add method to webpart that use CAML to read and order the list
Now for the code that reads the Sharepoint list in a structured manner so we can use it in web parts.  First, add the following method to your web part:
/// <summary>
/// Returns a list collection, ordered using a field in the list called "SortOrder"
/// </summary>
/// <param name="listName">The literal string name of the list to query</param>
/// <returns>SPListItemCollection</returns>
private SPListItemCollection ReadOrderedList(string listName)
        using (SPWeb configWeb = SPContext.Current.Site.OpenWeb())
            //Get the list
            SPList lookupList = configWeb.Lists[listName];
            // Use CAML to order the list
            SPQuery query = new SPQuery();
            // This CAML where clause searches for only items where the Enabled field is set to "Yes"
            query.Query = "<Where><Eq><FieldRef Name=\"Enabled\" /><Value Type=\"Integer\">1</Value></Eq></Where> ";
            // This CAML order by claus uses an integer field of "SortOrder"
            query.Query += "<OrderBy><FieldRef Name=\"SortOrder\" /></OrderBy>";
            SPListItemCollection lookupItems = lookupList.GetItems(query);
            return lookupItems;
    catch (Exception ex)
        ErrorTrap("ReadOrderedList: " + ex.Message + Environment.NewLine + ex.StackTrace.ToString());

This method will return the content of the list in a SPListItemCollection object. CAML is use to order the list by the "SortOrder" column, and to ignore values that do not have the boolean field "Enabled" set to true.

Step 3:  Bind the list to a control
At the class level of your webpart, create the control that you wish to bind to (in this case a CheckBoxList):

CheckBoxList myCheckList = new CheckBoxList();

To bind this control to your Sharpoint list, put this code in the CreateChildControls() method. Simply replace "My List" with the real name of your list:
// Set up the Record Section checkboxes list
    SPListItemCollection dt = ReadOrderedList("My List");
    myCheckList.DataSource = dt;
    myCheckList.DataTextField = "Title";
    myCheckList.DataValueField = "ID";

That's it! You now have a Sharepoint object bound to your Sharepoint list of reference values.  When rendered, the output will look something like this:

Yyou can use the following method to render the CheckBoxList to the web part.  This example puts the CheckBoxList in a DIV that fixes the maximum size of the list with a scrollbar.
/// <summary>
/// render a list of checkboxes  to the page
/// </summary>
void RenderCheckBoxes()
        // Set up list of checkboxes
        myCheckList.EnableViewState = true;
        myCheckList.CellSpacing = 0;
        myCheckList.CellPadding = 0;

        for (int i = 0; i < myCheckList.Items.Count; i++)
            ListItem li = new ListItem(myCheckList.Items[i].Text, myCheckList.Items[i].Value);
            // Set all boxes to selected if not a postback
            if (!Page.IsPostBack)
                myCheckList.Items[i].Selected = true;

        // Give the checklist an ID (can use this in javascript etc)
        myCheckList.ID = "RecordSections";

        //Add controls to web part
        Controls.Add(new LiteralControl(@"<TABLE border='0' bordercolor='red' cellpadding='0' cellspacing='0' valign='top'>"));
        Controls.Add(new LiteralControl("<TR>"));
        Controls.Add(new LiteralControl("<TD>"));
        Controls.Add(new LiteralControl(@"<div class=""ms-frombody""><b>Record Sections</b></div>"));
        Controls.Add(new LiteralControl("</TD>"));
        Controls.Add(new LiteralControl("</TR>"));
        Controls.Add(new LiteralControl("<TR>"));

        Controls.Add(new LiteralControl("<TD>\r\n"));
        Controls.Add(new LiteralControl(@"<input type=""hidden"" id=""ScrollPos"" runat=""server""/>"));

        Controls.Add(new LiteralControl(@"<div id='RecordSectionScroll' style=""OVERFLOW: auto; WIDTH: 247px;  TOP: 0px; HEIGHT: 300px"" onscroll=""javascript:document.getElementById('ScrollPos').value = this.scrollTop""> "));

        Controls.Add(new LiteralControl("</div></TD>"));
        Controls.Add(new LiteralControl("</TR>"));     
        Controls.Add(new LiteralControl("</TABLE>"));
    catch (Exception ex)
        ErrorTrap("RenderCheckBoxes" + ex.Message + Environment.NewLine + ex.StackTrace.ToString());

Note that I am using literal controls to render the check boxes in a HTML table. This is pretty horrible and I don't recommend it, but it does work.

Here is the ErrorTrap method. Note that this is just a simple error handling function and is not required to render a checklist bound to a Sharepoint list, but since it appears in the examples I though I would show you what I do.
/// <summary>
/// Give an error message
/// </summary>
/// <param name="myMessage"></param>
void ErrorTrap(string myMessage)
    Controls.Add(new LiteralControl("<strong>ERROR: " + myMessage.ToString() + "<BR/></strong>"));

/// <summary>
/// Write errors to the log file
/// </summary>
/// <param name="myMessage"></param>
void ErrorToFile(string myMessage)
    using (StreamWriter sw = new StreamWriter("D:\\SearchErrorLog.txt"))
        DateTime now = new DateTime();
        sw.Write(now.ToString() + ": " + myMessage.ToString());