Saturday, January 24, 2009

Batch Update In SharePoint

SharePoint support batch update in List by webservice and SharePoint object model.

For Example i used sample Custom List (Orders).

image

1. By Use of Web Service.

SharePoint webservice allows update list items as a batch.At a time only one list can be updated by single webservice call.

CAML That use for Batch Update. Here i update item with ID 2 and create new item in Orders List in one webservice call.

<Batch OnError=’Continue’>
         <Method ID=’1’ Cmd=’Update’>
                <Field Name=’ID’ >2</Field>
                <Field Name=’OrderDate’ >2007-1-21</Field>
                <Field Name=’CustomerID’ >1;#Cust_1</Field>
         </Method>
         <Method ID=’2’ Cmd=’New’>
                <Field Name=’OrderDate’ >2007-1-21</Field>
                <Field Name=’CustomerID’ >2;#Cust_2</Field>
         </Method>
</Batch>

C# Code (Either Add web reference of SharePoint webservice or Create Proxy Class using wsdl.exe and use that class)

Lists lst = new Lists();
lst.Url = “http://<your site>/_vti_bin/lists.asmx”;
lst.Credentials = new System.Net.NetworkCredential ("test", "test");
XmlDocument doc = new XmlDocument();
XmlElement batchElement =  doc.CreateElement("Batch");
batchElement.SetAttribute("OnError", "Continue");
batchElement.InnerXml = "<Method ID='1' Cmd='Update'>" +
               "<Field Name='ID'>2</Field>" +
               "<Field Name='OrderDate'>2009-1-31</Field><Field Name='OrderDateTime'>2009-1-31</Field></Method><Method ID='2' Cmd='New'><Field Name='CustomerID'>1;#Cust_1</Field><Field Name='OrderDate'>2009-1-31</Field><Field Name='OrderDateTime'>2009-1-31</Field></Method>";
XmlNode result = lst.UpdateListItems("Orders", batchElement); // This line of code make webservice call to update lists.

One more thing In CAML displayed above has two bold line for CustomerID field. CustomerID is a LookUp Field. So i mention that date id;#Value (Commom lookup Structure).If you want to choose lookup value only by id then use following CAML for Field.

<Batch OnError=’Continue’>
         <Method ID=’1’ Cmd=’Update’>
                <Field Name=’ID’ >2</Field>
                <Field Name=’OrderDate’ >2007-1-21</Field>
                <Field Name=’CustomerID’  Type=’LookUp’ LookUpID=’True’>1</Field>
         </Method>
         <Method ID=’2’ Cmd=’New’>
                <Field Name=’OrderDate’ >2007-1-21</Field>
                <Field Name=’CustomerID’ Type=’LookUp’  LookUpID=’True’ >2</Field>
         </Method>
</Batch>

2. By Use Of SharePoint Object Model. (SPWeb.ProcessBatchData)

SharePoint Object Model has SPWeb class. SPWeb class has ProcessBatchData Method , this method is used to update Batch via object model. Even this method allows you update two list items in Single method call.

Sample CAML .
<?xml version="1.0" encoding="UTF-8"?>
<ows:Batch OnError="Continue">
  <Method ID="1">
    <SetList>71a9fac3-2e94-4246-8fec-41e30ea65b06</SetList>
    <SetVar Name="Cmd">Save</SetVar>
    <SetVar Name="ID">New</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#OrderDate">2009-2-21</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#OrderDateTime">2009-2-21</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#CustomerID">1;#Cust_1</SetVar>
  </Method>
  <Method ID="2">
    <SetList>71a9fac3-2e94-4246-8fec-41e30ea65b06</SetList>
    <SetVar Name="Cmd">Save</SetVar>
    <SetVar Name="ID">3</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#OrderDate">2009-2-21</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#OrderDateTime">2009-2-21</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#CustomerID">1;#Cust_1</SetVar>
  </Method>
 
<Method ID="3">
    <SetList>71a9fac3-2e94-4246-8fec-41e30ea65b06</SetList>
    <SetVar Name="Cmd">Delete</SetVar>
    <SetVar Name="ID">4</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#OrderDate">2009-2-21</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#OrderDateTime">2009-2-21</SetVar>
    <SetVar Name="urn:schemas-microsoft-com:office:office#CustomerID">2;#Cust_2</SetVar>
  </Method>
</ows:Batch>

In above SetList element set GUID of SPList. Here i only update Orders List but you can set another list id combine it with above by just adding another Method Element.

SetVar element with attribute Name ‘cmd’ used to identify type of functionality. It has mainly two values : Save and Delete . Save is used for either for New Item or in case of update existing item.

SetVar element with attribute Name ‘ID’ used to find out on which item it operate. ID value ‘New’ is for new item and in case of Update and Delete it should have valid id value. (Integer).

SetVar element with Name attribute value urn:schemas-microsoft-com:office:office#<Field_Name> indicate perticular field of ListItem,<Field_Name> should replace by respetive field internal name.

C# Code.

SPSite site = new SPSite("http://avani:45830");
SPWeb web = site.OpenWeb();
SPList lst = web.Lists["Orders"];
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\">{2}</SetVar>" +
             "<SetVar Name=\"ID\">{3}</SetVar>" +
             "<SetVar Name=\"urn:schemas-microsoft-com:office:office#OrderDate\">{4}</SetVar>" +
             "<SetVar Name=\"urn:schemas-microsoft-com:office:office#OrderDateTime\">{5}</SetVar>" +
             "<SetVar Name=\"urn:schemas-microsoft-com:office:office#CustomerID\">{6}</SetVar>" +            
             "</Method>";

StringBuilder strmethodFormat = new StringBuilder();
strmethodFormat.AppendFormat(methodFormat, 1, lst.ID.ToString(), "Save", "New", "2009-2-21", "2009-2-21","1;#Cust_1");
strmethodFormat.AppendFormat(methodFormat, 2, lst.ID.ToString(), "Save", "3", "2009-2-21", "2009-2-21","1;#Cust_1");
strmethodFormat.AppendFormat(methodFormat, 3, lst.ID.ToString(), "Save", "4", "2009-2-21", "2009-2-21","2;#Cust_2");
string processtext = String.Format(batchFormat, strmethodFormat.ToString());

web.ProcessBatchData(processtext);

Other usefull post about Batch element creation through XLinq or Linq are
http://dotnetstep.blogspot.com/2009/01/xlinq-to-generate-batch-element-for.html
http://dotnetstep.blogspot.com/2009/01/sharepoint-to-linq.html

4 comments:

Bill said...

Thanks you so much. I've been trying to use Cmd="New" to insert an item. MS needs to standardize their XML. Some places you use a <Field> element (list service), and in others a <SetVar> (ProcessBatchData) for the exact same command.

Jinal Patel said...

Thanks for your comment.

Anonymous said...

I wished MSDN had an example like yours! Just what I needed to clear my doubts !

All the best!

Alexey Olhovskiy said...

Consider reusing http://alexeydev.blogspot.com/2011/11/sharepoint-batch-add-update-and-delete.html