Search This Blog

Monday, November 14, 2011

Delete Large data from Sharepoint Lists

This post contains code to delete the litems from a sharepoint list having very large data.
In one of my projects, I was storing very large amount of data in a list that I require to delete after specific number of days.  I tried deleting the data using SPListItemcollection but that seems to be too heavy for getting all the items in memory and delete those one by one. So I used inbuilt method of SPWeb object which processes the information for bulk data. You can make use of SPWeb's
ProcessBatchData method which takes in an XML string and processes the query as per the command specified in XML. I have done it for "Delete" however there are other methods too which are supported.

Below is the code snippet for deleting bulk data from a large list.

private void DeleteListItems(String listName)
        {
            SPWeb objWeb = SPContext.Current.Web;
            StringBuilder sbDelete = new StringBuilder();
            SPList list = objWeb.Lists[listName];
            SPQuery objSPQuery = newSPQuery();
            SPQuery query;
            SPSiteDataQuery objSPSiteDataQuery;
            String listGuid = list.ID.ToString();
            Int32 itemcount = list.ItemCount;

            //Create an XML to be send to ProcessBatchData method of SPWeb
            sbDelete.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");

            //String with specified format
            String command = String.Format("<Method><SetList Scope=\"Request\">{0}</SetList><SetVar Name=\"ID\">{{0}}</SetVar><SetVar Name=\"Cmd\">Delete</SetVar></Method>", listGuid);

            if (itemcount < 1000)
            {
                //Process simple delete if items < 1000
                DataTable objDataTable = null;
                query = new SPQuery();
                objSPSiteDataQuery = new SPSiteDataQuery();
                objSPSiteDataQuery.ViewFields = "<FieldRef Name=\"ID\" />";
                objSPSiteDataQuery.Lists = "<Lists><List ID=\"" + listGuid + "\" /></Lists>";
                //building a void query for getting results
                objSPSiteDataQuery.Query = "<Where><IsNotNull><FieldRef Name='ID' /></IsNotNull></Where>";
               
                objDataTable = objWeb.GetSiteData(objSPSiteDataQuery);
                if (objDataTable != null && objDataTable.Rows.Count > 0)
                {
                    foreach (DataRow row in objDataTable.Rows)
                    {
                        sbDelete.Append(string.Format(command, Convert.ToString(row["ID"])));
                    }
                }
                sbDelete.Append("</Batch>");
                objWeb.ProcessBatchData(sbDelete.ToString());
                            list.Update();
            }
            else
            {
              for (int batchCount = 0; batchCount < itemcount / 1000; batchCount++)
              {
                    DataTable objDataTable = null;
                    query = new SPQuery();
                    objSPSiteDataQuery = new SPSiteDataQuery();
                    objSPSiteDataQuery.ViewFields = "<FieldRef Name=\"ID\" />";
                    objSPSiteDataQuery.RowLimit = 1000;
                    objSPSiteDataQuery.Lists = "<Lists><List ID=\"" + listGuid + "\" /></Lists>";
                   objSPSiteDataQuery.Query = "<Where><IsNotNull><FieldRef Name='ID' /></IsNotNull></Where>";
                    objDataTable = objWeb.GetSiteData(objSPSiteDataQuery);
                    //Using here Site data query since it retrieves dataset instead of SPListItemCollection which is much Lighter

                    if (objDataTable != null && objDataTable.Rows.Count > 0)
                    {
                        foreach (DataRow row in objDataTable.Rows)
                         {
                          sbDelete.Append(string.Format(command, Convert.ToString(row["ID"])));
                        }
                        sbDelete.Append("</Batch>");
                        //Execute the batch job for deleting items
                        objWeb.ProcessBatchData(sbDelete.ToString());
                        list.Update();

                        //re initialise the string builder to add new XML
                        sbDelete = new StringBuilder();
                        sbDelete.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");
                    }
               }
            }
}

2 comments:

  1. This looks to be just the thing that I'm looking for, however I would need to delete everything except for files that were created in the last 30 days.
    How would you amend the where clause in the query to say "where created is less than (today - 30)"?
    Also, how do you actually implement this code? I've not delved into the coding site of SP much. Is this something you'd need to run using jQuery? Or something else?
    Many thanks!

    ReplyDelete
  2. Hi Jamie,

    You can replace the Query with :
    "<Where><Lt><FieldRef Name='Created' /><Value IncludeTimeValue='TRUE' Type='DateTime'>YOUR DATE</Value></Lt></Query>"

    This way the method will be modified like:

    DateTime currentDate = System.DateTime.Today.AddDays(-30);
    String convertedTime = Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentDate);
    objSPSiteDataQuery.Query = string.Format("<Where><Lt><FieldRef Name='Created' /><Value IncludeTimeValue='TRUE' Type='DateTime'>{0}</Value></Lt></Query>", convertedTime);

    Regarding your second query, you can add the code to anywhere depending upon requirements. You can create a console application and add the code there if you can access the server. Iw you want the code to be executed from intranet, you can also create a Web Part in the site.
    Remember to do all the stuff under Previledge Web.

    ReplyDelete