Applies To: SharePoint

One of the biggest irritations to me coming from a SQL background is trying to do SQL like things in SharePoint 2007. One of those irritations is performing a simple update through the web services.  In SQL, I could write something like:

UPDATE ListTable
    SET FieldName = FieldValue
    WHERE QueryFieldName = QueryFieldValue

In the generic T-SQL above, any row where the QueryFieldName column had a value equal to the QueryFieldValue would have it’s FieldName Column set to FieldValue. Pretty straightforward for anyone with a database background.

With the SharePoint Web Services, however, there isn’t a simple UPDATE command like this. Instead of the one simple command above, we have to make 2 calls to the Web Service and provide some complicated XML parameters. This can be a big hassle the first time you do this, so to hopefully save you some headaches, I’ll provide most of the code required below.

I won’t be showing you how to setup whatever project you are using or how to connect to the web service. The code is in C# and I will assume your service reference (Lists) is called myService.  So let’s get started!

Helper Functions:

I will be using a few helper functions to make generating the XML parameters easier, I’ll go ahead and list these here:

private void AddAttribute(XmlDocument x, ref XmlNode node,
          string attributename, string attributevalue,
          string AttributeNameSpace = "")
{
    XmlNode attnode =
              x.CreateNode(XmlNodeType.Attribute, attributename,
              AttributeNameSpace);
    attnode.Value = attributevalue;
    node.Attributes.Append(attnode);
}

private XmlNode CreateUpdateNode(XmlDocument x, int ID, string RowID)
{
    XmlNode node = x.CreateNode(XmlNodeType.Element, "Method", null);
    AddAttribute(x, node, "ID", ID);
    AddAttribute(x, node, "Cmd", "Update");
    node.AppendChild(CreateFieldNode(x, "ID", RowID));
    return node;
}

private XmlNode CreateFieldNode(XmlDocument x, string Name, string Value)
{
    XmlNode node = x.CreateNode(XmlNodeType.Element, "Field", null);
    AddAttribute(x, node, "Name", Name);
    node.InnerText = Value;
    return node;
}

Retrieve the Items to be Updated:

This is equivalent to the WHERE clause of the example SQL query. You will need to use the GetListItems method of the Lists service.

This method uses the List GUID to reference the target list, I’ll leave it to you to retrieve it and will assume it in the variable ListGUID. You will also need to replace the QueryFieldName, QueryFieldType, and QueryFieldValue variables with appropriate values.

XmlDocument doc = new XmlDocument();
XmlNode queryNode = doc.CreateNode(XmlNodeType.Element, "Query", null);
queryNode.InnerXml =
          string.Format("<Where><Eq><FieldRef Name='{0}' />
                         <Value Type='{1}'>{2}</Value></Eq></Where>",
          QueryFieldName, QueryFieldType, QueryFieldValue);

XmlNode viewNode = doc.CreateNode(XmlNodeType.Element, "ViewFields", null);
viewNode.InnerXml = "<FieldRef Name='ID'/>";

XmlNode resultsNode = myService.GetListItems(ListGUID, null, queryNode,
          viewNode, null,
          doc.CreateNode(XmlNodeType.Element, "QueryOptions", null), null);

 Parse the IDs from the Results:

XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
nsmgr.AddNamespace("z", "#RowsetSchema");
XmlNodeList rowNodes = resultsNode.SelectNodes(".//z:row", nsmgr);
List<int> IDs = new List<int>();
foreach (XmlNode rowNode in rowNodes) {
    XmlAttribute result = rowNode.Attributes("ows_ID");
    IDs.Add(int.Parse(result.Value));
}

Perform the Update:

This is equivalent to the SET portion of the example SQL query. You will need to use the UpdateListItems method of the Lists service.

Again, this method uses the List GUID to reference the target list, I’ll leave it to you to retrieve it and will assume it in the variable ListGUID. You will also need to replace the FieldName and FieldValue variables with appropriate values.

<pre>XmlDocument doc2 = new XmlDocument();
XmlNode node = doc2.CreateNode(XmlNodeType.Element, "Batch", null);
AddAttribute(doc2, node, "OnError", "Continue");

int itemCount = 1;
foreach (int i in IDs) {
    XmlNode updateNode = CreateUpdateNode(doc2, itemCount, i);
    updateNode.AppendChild(CreateFieldNode(doc2, FieldName, FieldValue));
    //Append More children here to update multiple fields
    node.AppendChild(updateNode);
    itemCount += 1;
}

myService.UpdateListItems(ListGUID, node);

That’s it. To review, we created a CAML query and retrieved the IDs of the matching rows using the GetListItems method. We parsed the XML result and put all of those IDs in a List<int>. We then added each row ID to a batch update XML parameter which we used in the UpdateListItems method.

As you can see, this is way more complicated and not nearly as obvious as a SQL UPDATE command, but you can take the above code and create a pretty generic wrapper to make your web service updates nearly as easy. Let me know how it works out for you or if you have any questions!

Originally Published on WireBear.com/blog on February 11, 2011