Batch Updating List Items in Windows SharePoint Services 3.0

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 theirProcessed 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 Methodelement 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);
   }
 }
}

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.


query.RowLimit = 100;
do
{
   SPListItemCollection unprocessedItems = list.GetItems(query);

   //....process all the returned items in this page
   //....
   //....

   query.ListItemCollectionPosition = unprocessedItems.ListItemCollectionPosition;
} while (query.ListItemCollectionPosition != null);




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.