Friday, May 29, 2009

Deleting the list items at a time from list using batch command in SharePoint 2007

When we are working with SharePoint, especially when programming we can discover plenty of things. Because SharePoint is a very big system and it is pretty much good for the developers and administrators that they can discover more and explore more. Its a lovely product and I really love it from Microsoft. From my experience in SharePoint i thought of placing all the SharePoint stuff in my blog. So now we are going to discuss about the performance and operations we need to perform in coding when dealing with SharePoint objects.
When we do SharePoint coding we need to think a minute about the efficiency and performance of the code . For example, if you take a person object, it has some properties around 10-15 and if you don’t dispose it from memory then we don’t get serious problems. But where as we dealing with SharePoint objects like SPSite and SPWeb etc, these are very big and occupies lot of memory than objects what we use every day in C#. So, don’t forget to use Using keyword for them so that they will be disposed and removed from memory when it’s scope is over automatically.
Another performance related is performing common operation to all list items in a list in SharePoint. Usually we will write a foreach statement to loop through all the list items in a list and perform operation. i.e. add, edit or delete. When we need to update all the list items and columns, data varies dynamically then  we need to write a update command inside the foreach statement. But when we know that we are updating the same columns with same data for all items then we shouldn’t follow the code by writing update statement in foreach statement. Because it will be very costly and time taking. It will use lot of resources multiple times. So for this, Microsoft SharePoint team already integrated beautiful feature in SharePoint system. i.e. called Batch programming for doing operations on list items. This is the most efficient way of deleting or updating in a single list.
Below is the code i use to delete all the list items in a SharePoint list.
private void DeleteAllItemsUsingBatch()
    {
        using (SPSite site = new SPSite("http://mySharePointServer"))
        {
            SPWeb web = site.OpenWeb("/");
            SPList list = web.Lists["Links"];
            StringBuilder sb = new StringBuilder();
            sb.Append("");
            string batchCommand = "<method><setlist scope=\"Request\">" + list.ID + "</setlist><setvar name=\"ID\">{0}</setvar><setvar name=\"Cmd\">DELETE</setvar></method>";
            foreach (SPListItem item in list.Items)
            {
                sb.Append(string.Format(batchCommand, item.ID.ToString()));
            }
            sb.Append("");
            web.AllowUnsafeUpdates = true;
            site.RootWeb.ProcessBatchData(sb.ToString());
            web.AllowUnsafeUpdates = false;
            web.Close();
        }
    }
Below is the code for deleting all library items in a document library. Remember, the only difference is for document libraries we have files as the primary fields. So, we should specify the file name along with the other parameters. So, find below code.
private static void DeleteAllItemsUsingBatch()
        {
            using (SPSite site = new SPSite("http://mysharepointserver"))
            {
                SPWeb web = site.OpenWeb("/");
                SPList list = web.Lists["Documents"];
                StringBuilder sb = new StringBuilder();
                sb.Append("");
                string batchCommand = "<method><setlist scope=\"Request\">" + list.ID + "</setlist><setvar name=\"ID\">{0}</setvar><setvar name=\"Cmd\">DELETE</setvar><setvar name=\"owsfileref\">{1}</setvar></method>";
                foreach (SPListItem item in list.Items)
                {
                    sb.AppendFormat(batchCommand, item.ID.ToString(), item.File.ServerRelativeUrl);
                }
                sb.Append("");

                web.AllowUnsafeUpdates = true;
                site.RootWeb.ProcessBatchData(sb.ToString());
                web.AllowUnsafeUpdates = false;
                web.Close();
            }
        }
Explore more and know more. Happy Coding!!!

8 comments:

  1. This was exactly what I was looking for. I implemented it in minutes and it worked the first time. Excellent job!

    ReplyDelete
  2. Sorry in advance for novice question.

    Is this/can this be used as jquery/javascript? I have a remote client program and need to delete all items in various sharepoint lists. Is there a way to utilize your code and perhaps activate via workflow?

    Thanks in advance.

    ReplyDelete
  3. If you want to implement this and execute via javascript or Jquery then we can implement this. Jquery has client proxy or web method support. You can write or build CAML query or call web service to execute on server side. [You can call web service from Jquery.] SharePoint has web services implemented for everything. So, you can use that option.

    And in a workflow, I am not sure how it will work. Because where the workflow triggers? On a list item?

    Can you tell me when your workflow will raise?

    -Praveen.

    ReplyDelete
  4. FYI,
    I am giving you some example of how you can call SharePoint webservice in Jquery.

    $(document).ready(function() {
    var soapEnv =
    "<soapenv:Envelope xmlns:soapenv='http://schemas.xmlsoap.org/soap/envelope/'> \
    <soapenv:Body> \
    <GetListCollection xmlns='http://schemas.microsoft.com/sharepoint/soap/'> \
    </GetListCollection> \
    </soapenv:Body> \
    </soapenv:Envelope>";

    $.ajax({
    url: "http://yoursite/_vti_bin/lists.asmx",
    type: "POST",
    dataType: "xml",
    data: soapEnv,
    complete: processResult,
    contentType: "text/xml; charset=\"utf-8\""
    });
    });

    It will pull you all list items in the given site.
    Or
    you can try with SOAPClient plug in available in Jquery plugin's
    http://plugins.jquery.com/node/2604
    Which will help you to make SOAP request and execute on server whatever you want.
    I will post more whenever I get time. Feel free to ask if you have any more questions.

    -Praveen.

    ReplyDelete
  5. I step through the code and noticed it picks up all the file (in foreach loop) and ends without an error. Files are still present when I go to back to the site. hmm...

    ReplyDelete
  6. I have a JQuery script below (thanks Marc Anderson). I am needing a way to batch or pause this to allow for deleteiing a large amount of items.




    $(document).ready(function() {
    $().SPServices({
    operation:'GetListItems',
    listName: "{5C6B8A1D-FAB1-40A3-96F2-A54286C49952}",
    CAMLViewFields:'',
    completefunc: function (xData, Status) {
    $(xData.responseXML).find("[nodeName=z:row]").each(function() {
    var ID = $(this).attr("ows_ID");
    $().SPServices({
    operation: "UpdateListItems",
    listName: "{5C6B8A1D-FAB1-40A3-96F2-A54286C49952}",
    updates: "" +
    "" +
    "" + ID + "" +
    "" +
    "",
    completefunc: function (xData, Status) {
    }
    });
    });
    }
    });
    });


    Do you have any insight on how to get this to batch load or process this for large lists?

    ReplyDelete
  7. I tried this but it turned out to be very slow for a "Pages" library I had in my dev environment that had grown to 18,000 pages. Turns out that edit in datasheet mode in very fast somehow. Took me about 10 minutes to get it down to 300 pages.

    ReplyDelete
  8. How is supposed to be deployed using visual studio 2013?

    ReplyDelete