SharePoint List Batch Update

I was required to add and delete nearly 100 items (it may grow) in a list. If we do it in normal procedure we will definitely face performance issue and also there is a chance of Request timed out error. Calling SPListItem.Update() or SPListItem.Delete() method for every record is not recommended, because every time it hits the database.

1. SPList lstData = web.GetList(web.Url + "/Lists/Ratio");
2. SPListItemCollection itmColl = lstData.GetItems(lstData.DefaultView);
3. //assume the datatable dtData have 100 records
4. foreach(DataRow dr in dtData.Rows)
5. {
6.        SPListItem itemData = itmColl.Add();
7.        itemData["Title"] = dr["title"].ToString();
8.        itemData["PaperCount"] = dr["PaperCount"].ToString();
9.        itemData.Update();
10. }

The above code may take more time to complete the operation. Then one thing came up into my mind don't we have a kind of batch insert/update commands like SQL server. Then in the MSDN article I found information about SPWeb.ProcessBatchData() mehod. It is simply beautiful.

It just takes one string parameter and it is a CAML string contains batch string of commands, which consists of a batch element. With following example you will get to know how to use this.

1. StringBuilder sbAOERatio = new StringBuilder();
2. sbAOERatio.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");
3. string command = "<Method><SetList Scope=\"Request\">{0}</SetList>" +
4. "<SetVar Name=\"ID\">New</SetVar>" +
5. "<SetVar Name=\"Cmd\">Save</SetVar>" +
6. "<SetVar Name=\"urn:schemas-microsoft-com:office:office#Title\">{1}</SetVar>" +
7. "<SetVar Name=\"urn:schemas-microsoft-com:office:office#PaperCount\">{2}</SetVar>" +
8. "<SetVar Name=\"urn:schemas-microsoft-com:office:office#ReviewerCount\">{3}</SetVar>" +
9. "<SetVar Name=\"urn:schemas-microsoft-com:office:office#Ratio\">{4}</SetVar>" +
10. "</Method>";
11. foreach (DataRow dr in dtData.Rows)
12. {
13.      sbAOERatio.AppendFormat(command, lstAOERatio.ID, Convert.ToString(dr["title"]), P, R, ratio);
14. }
15. sbAOERatio.Append("</Batch>");
16. web.ProcessBatchData(sbAOERatio.ToString());

The above code will get executed in fraction of seconds. This code snippet is for adding items. If you want to perform update operation, small change required in step-4. i.e) <SetVar Name=\"ID\">1</SetVar>. 

Give the exact list item ID to update instead of New. 

If you want to perform delete operation, change the step-5. Give Delete instead of Save command.

The above batch command will add the items in Root folder. In case, if you are required to add items in specific folder check the below code.

<SetVar Name=\"RootFolder\">/Lists/TestList/2012</SetVar>

You can also create folders in following ways.

string methodFormat = "<Method ID=\"{0}\">" +
"<SetList>{1}</SetList>" +
"<SetVar Name=\"ID\">New</SetVar>" +
"<SetVar Name=\"Cmd\">Save</SetVar>" +
"<SetVar Name=\"urn:schemas-microsoft-com:office:office#Title\">{2}</SetVar>" +                
"<SetVar Name=\"urn:schemas-microsoft-com:office:office#ContentType\">Folder</SetVar>" +
"</Method>";

another way:

<?xml version="1.0" encoding="utf-8"?> 
<ows:Batch OnError="Continue"> 
  <Method ID="Test"> 
    <SetList Scope="Request">82d62a9a-55ba-49c8-a9b8-68ec965a5931</SetList> 
    <SetVar Name="Cmd">Save</SetVar> 
    <SetVar Name="ID">New</SetVar> 
    <SetVar Name="Type">1</SetVar> 
    <SetVar Name="owsfileref">/sites/1/docs/folder1</SetVar> 
  </Method> 
</ows:Batch>

This way you can create subfolders too, e.g. using:

    <SetVar Name="owsfileref">/sites/1/docs/folder1/sub1</SetVar>