Bulk Add new items to a SharePoint List using ProcessBatchData

private readonly string BatchXMLBanner =
"<?xml version=\"1.0\" encoding=\"UTF-8\"?><ows:Batch OnError=\"Continue\">{0}</ows:Batch>";

private readonly string BatchXMLMethod =
"<Method ID=\"{0}\"><SetList>{1}</SetList><SetVar Name=\"ID\">New</SetVar><SetVar Name=\"Cmd\">Save</SetVar>{2}</Method>";
 
private readonly string BatchXMLSetVar =
"<SetVar Name=\"urn:schemas-microsoft-com:office:office#{0}\">{1}</SetVar>";

Batch = buildBatch(dataTable, TargetList.ID);
BatchReturn = oWeb.ProcessBatchData(Batch);

private string buildBatch(DataTable dataTable, Guid guid)
{
    StringBuilder XMLSetVarLines = new StringBuilder();
    StringBuilder XMLSetMethods = new StringBuilder();

    int methodID = 0;

    foreach(DataRow row in dataTable.Rows)
    {
        foreach(DataColumn column in dataTable.Columns)
        {
            var fieldname = column.ColumnName;
            var fieldvalue = row[fieldname].ToString().Replace("&", "&amp");
            XMLSetVarLines.Append(String.Format(BatchXMLSetVar, fieldname, fieldvalue));
        }

        XMLSetMethods.Append(String.Format(BatchXMLMethod, methodID, guid, XMLSetVarLines));

        methodID++;

        XMLSetVarLines.Length = 0;
    }
    return (String.Format(BatchXMLBanner, XMLSetMethods));
}

8 Comments


  1. pungi
    Dec 30, 2011

    I don’t get this…MS provides a ProcessBatchData() and to use it you need to build this XML crap? Come-on, show a little creativeness!


  2. aaron
    Jan 08, 2012

    Not sure if your comment is directed at me or Microsoft but yes you do need to build the XML to use it (also shown at http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spweb.processbatchdata.aspx).

    Whilst it may not be the most intuitive process its purpose is to allow the creation of the XML from various sources / streams and bulk process the tasks versus a foreach X in XXX Item.Add.

    The performance difference is significant, and can be further used to you advantage to bulk delete items – for instance you can use ProcessBatchData to delete a million items in a fraction of the time it would take by any of the other object model methods.


  3. moss
    Mar 31, 2012

    Thanks man , it worked very well for me. God bless you…


  4. Matt
    Jun 22, 2012

    Thanks for the code example! We’re using this to insert ~ 100 items at once. The problem is that we need to set unique permissions on each item based on a field in that item. Is there a way to set the permissions of the item automatically (either via the ProcessBatchData XML, workflow, or event receiver)? We’ve tried the workflow and event receiver, and it doesn’t seem like ProcessBatchData kicks off either. The other limitation we’re facing is the 30 second timeout in SP Online. Starting workflows on each item in code right after the batch process makes the code timeout.


  5. aaron
    Jun 25, 2012

    Hello Matt. Certainly I am not suprised that ProcessBatchData does not kick off a workflow or event reciever as it is built to add/change 1000′s of records in one go, if a process started per entry it would swallow the system.

    It sounds to me that in you are going to have to do a couple of passes on your code, first the ProcessBatchData as per above, then for each variant of the field set the permission, something like;

    Code

    The 30 second limitation you speak of is due to the worker process settings and probably is out of you reach (being hosted), so I would suggest you are going to have to batch your ProcessBatchData – inject 100 records at a time, set permissions, inject another 100 records.

    If there are no dependancies you could look at injecting in parrallel threads using BackgroundWorker.


  6. Jackie
    Mar 05, 2013

    Hello. I am using Access 2007 and WSS 3.0 / MOSS 2007. I have two queries within Access. One is a MakeTable query to make local tables and the other is an AppendTable query to append Access 2007 linked SharePoint lists. Unfortunately, I have multiple lists with around 60 thousand items max (so far…). It is taking some time to update SharePoint using SharePoint linked lists within Access 2007 method.

    I would like to be able to use the ProcessBatchData method to add items to existing SharePoint lists. I am reading various blogs and forums and there are references to using XML but how can I get from data in my Access 2007 local tables to XML?

    I started a Visual Studio 2008 project using C Sharp to connect to Access 2007 and list the local tables. Now I need to access the individual records within those tables.

    Is it possible to use the data from an Access table with the ProcessBatchData method? Or am I going in the wrong direction?

    Thank you very much in advance. Have a great day.

    ~Jackie


  7. Joe
    May 07, 2013

    Hi,

    these two lines are showing errors in my console app: (missing namespace)

    Batch = buildBatch(dataTable, TargetList.ID);
    BatchReturn = oWeb.ProcessBatchData(Batch);

    can you post the complete script with the “usingé statement, please.
    I need to add several items with 15 columns each.


  8. Joe
    May 07, 2013

    OK, got it. they are strings.

Leave a Reply