Batch Updating List Items in Windows SharePoint Services 3.0

  • Article

Summary:  Learn how to update multiple items in a list in Windows SharePoint Services 3.0 by constructing and executing a batch of commands.

Applies to:  2007 Microsoft Office system, Windows SharePoint Services 3.0

Microsoft Corporation

April 2008

In Windows SharePoint Services 3.0, sometimes you must update multiple items in a list. Creating a foreach loop to iterate through a list item collection and performing an Update on each list item can be very intensive. An alternative approach is to construct a Collaborative Application Markup Language (CAML) string that contains a batch of commands to perform the updates and execute the commands by using the ProcessBatchData command of an SPWeb object. This Microsoft Office Visual How To demonstrates how to construct and execute a batch of commands to update text and date fields for all items in a list.

This example uses a document library named Processed Documents that contains a Processed yes/no field and Processed Date date field. You use a query to obtain a collection of items that have not been processed, as identified by their Processed value. You create a CAML statement to update the Processed and Processed Date fields for all documents in this library.

After setting up the variables, you instantiate an SPList object for the Processed Documents document library by first instantiating an SPSite object and then an SPWeb object that contains the library. After getting the SPList object, you construct and execute a query for all items in the library that do not have a Processed value of 1, that is, all items that have not been processed. The query returns an SPListItemCollection object of items that meet this criterion.

The next step is to build the CAML batch command that is used to perform the update. Each command includes a Method element with SetVar subelements. The SetVar elements define the type of command, the GUID of the list, the ID of the item, and the fields to update. The fields are defined in the format <schema>#<internal_field_name>.

As the update command for each item is created, it is appended to a StringBuilder object. After all the commands are created, they are placed in an ows:Batch element. This element contains an OnError attribute that defines what happens if an exception occurs while the commands are executed. In this example, execution stops on the first exception. The full XML string that defines the batch of commands is executed using the ProcessBatchCommand command of the SPWeb object. It returns a string that contains the results of each command.

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

string methodFormat = "<Method ID=\"{0}\">" +
 "<SetList>{1}</SetList>" +
 "<SetVar Name=\"Cmd\">Save</SetVar>" +
 "<SetVar Name=\"ID\">{2}</SetVar>" +
 "<SetVar Name=\"urn:schemas-microsoft-com:office:office#Processed\">{3}</SetVar>" +
 "<SetVar Name=\"urn:schemas-microsoft-com:office:office#Processed_x0020_Date\">{4}</SetVar>" +
 "</Method>";

using (SPSite site = new SPSite("http://localhost"))
{
   using (SPWeb web = site.OpenWeb())
   {

      // Get the list containing the items to update.
      SPList list = web.Lists["Processed Documents"];
      string listGuid = list.ID.ToString();

      // Query to get the unprocessed items.
      SPQuery query = new SPQuery();
      query.Query = "<Where><Neq><FieldRef Name='Processed'/>
        <Value Type='Choice'>1</Value></Neq></Where>";
      query.ViewAttributes = "Scope='Recursive'";
      SPListItemCollection unprocessedItems = list.GetItems(query);

      // Build the CAML update commands.
      for (int i = 0; i < unprocessedItems.Count; i++)
      {
         int itemID = unprocessedItems[i].ID;
         methodBuilder.AppendFormat(methodFormat, itemID, listGuid, itemID, 1, formattedDate);
      }

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

      // Process the batch of commands.
      string batchReturn = web.ProcessBatchData(batch);
   }
 }
}
' Set up the variables to be used.
Dim methodBuilder As StringBuilder = New StringBuilder()
Dim batch As String = String.Empty
Dim currentDate As DateTime = DateTime.Now
Dim formattedDate As String = SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentDate)

Dim batchFormat As String = "<?xml version=""1.0"" encoding=""UTF-8""?>" + _
  "<ows:Batch OnError=""Return"">{0}</ows:Batch>"

Dim methodFormat As String = "<Method ID=""{0}""><SetList>{1}</SetList>" + _
 "<SetVar Name=""Cmd"">Save</SetVar>" + _
 "<SetVar Name=""ID"">{2}</SetVar>" + _
 "<SetVar Name=""urn:schemas-microsoft-com:office:office#Processed"">{3}</SetVar>" + _
 "<SetVar Name=""urn:schemas-microsoft-com:office:office#Processed_x0020_Date"">{4}</SetVar>" + _
 "</Method>"

Using site As SPSite = New SPSite("http://localhost")
  Using web As SPWeb = site.OpenWeb()

    ' Get the list containing the items to update.
    Dim list As SPList = web.Lists("Processed Documents")
    Dim listGuid As String = list.ID.ToString()

    ' Query to get the unprocessed items.
    Dim query As SPQuery = New SPQuery()
    query.Query &_
     = "<Where><Neq><FieldRef Name='Processed'/><Value Type='Choice'>1</Value></Neq></Where>"
    query.ViewAttributes = "Scope='Recursive'"
    Dim unprocessedItems As SPListItemCollection = list.GetItems(query)

    ' Build the CAML update commands.
    Dim i As Integer
    For i = 0 To unprocessedItems.Count - 1 Step i + 1
      Dim itemID As Integer = unprocessedItems(i).ID
      methodBuilder.AppendFormat(methodFormat, itemID, listGuid, itemID, 1, formattedDate)
      Next

    ' Put the pieces together.
    batch = String.Format(batchFormat, methodBuilder.ToString())

    ' Process the batch of commands.
    Dim batchReturn As String = web.ProcessBatchData(batch)

  End Using
End Using

The query used in this example is intended to update a relatively small number of list items. As the number of items in the list, and therefore, the query, grow, you must take specific precautions to ensure optimal performance. Primarily, you should set the RowLimit property of the SPQuery object. This property defines the number of items returned per page. Because the returned page of data is in-memory, the value should be tuned based on memory limits, but should be less than 2000.

The RowLimit property is used together with the ListItemCollectionProperty and a loop to page through the results. In the following code example, which is based on the example in this Visual How To, the RowLimit of the query is set to return 100 items per page of data, and a do/while loop is used to page through all of the items.

To further optimize this example, you can place an index on the Processed column in the Processed Documents library.

Processing commands in a batch can be very helpful for developers. It is one of the faster methods for updating, and you can use batch commands for inserting and deleting items. Although this example shows how to do a batch update through the object model, you can also do batch processing through several Web services, such as the UpdateListItems method on the Lists Web service, and the UpdateDwsData method on the Dws Web service.

Note
To correctly format the date, use the SPUtility.CreateISO8601DateTimeFromSystemDateTime method.

Watch the Video

Length: 07:02 | Size: 5.24 MB | Type: WMV