0

I am using a custom timer job to delete the Sharepoint List Items if the item count exceeds certain count, say for example 2000. The problem is the items are getting deleted, but it deletes the item in order 1,3,5,7,9.. it doesnt delete the items in 1,2,3,4 order... I have pasted the different approach which i have tried. Let me know if am missing something or i have to modify the code.

Approach 1:

SPList spList = spweb.Lists["Project Server Workflow History"];
SPQuery qry = new SPQuery();
string.Format("<OrderBy><FieldRef Name='Created' Ascending='FALSE'/></OrderBy>");
SPListItemCollection listItems = spList.GetItems(qry);
int i = 0;
while (listItems.Count > limit)
{
    listItems[i].Delete();
    i++;
}

Approach 2:

SPList spList = spweb.Lists["Project Server Workflow History"];
SPQuery qry = new SPQuery();
string.Format("<OrderBy><FieldRef Name='Created' Ascending='FALSE'/></OrderBy>");
SPListItemCollection listItems = spList.GetItems(qry);
foreach (SPListItem item in listItems)
{
    if (listItems.Count > limit)
    {
        item.Delete();
        listItems = spList.GetItems(qry);
    }
}

Approach 3:

SPList spList = spweb.Lists["Project Server Workflow History"];
SPQuery qry = new SPQuery();
string.Format("<OrderBy><FieldRef Name='Created' Ascending='FALSE'/></OrderBy>");
SPListItemCollection listItems = spList.GetItems(qry);
List<SPListItem> valuesToDelete = new List<SPListItem>();

foreach (SPListItem item in listItems)
{
    if (listItems.Count > limit)
    {
        valuesToDelete.Add(item);
    }
}

foreach (SPListItem delitem in valuesToDelete)
{
    delitem.Delete();
}
3

The first Approach deletes every second, because, every time you delete an item from the collection, the item is also removed from the collection. so if the collection before the first delete contained 11 elements, after the deletion there are just 10 elements, and of course, the element on Position i is now the element which was on Position i+1 before deletion of element on Position i.

so if you want to delete all items from the collection, dont use i, simply use listItems[0].Delete() (of course "i"-times).

Second Approach will result in an error, because Collection has been modified during foreach.

Third Approach should work.

However, instead of Iterating all elements until a specific number is reached, i would recomment just fetching that amount of items by the query using "RowLimit", it would be quite better for Performance (or at least put a "break" in the foreach after Limit is reached)

  • 1
    Third one won't work because listItems.Count > limit is constant. Neither Count nor limit is chaged until the second loop. – dstarkowski Oct 1 '15 at 11:09
  • Also, putting break in foreach loop will have not meaningfull effect on performance compared to using RowLimit. – dstarkowski Oct 1 '15 at 11:13
  • Can you help me with the code for deletion. I am totally confused with different kind of approaches. – Sasidharan Chandran Oct 1 '15 at 11:14
  • @dstarkowski: yes didn´t see this. And yes, no comparision to RowLimit, but better than useless loops – Leopold Lerch Oct 1 '15 at 11:14
  • 1
    already suggested in my answer: just juste "listItems[0].Delete()" instead of "listItems[i].Delete()" in your first approach – Leopold Lerch Oct 1 '15 at 11:16
1

Deleting items one by one can take a hit on performance especially when the number of items increases. SPWeb provide a ProcessBatchData method which can be used for deleting items in bulk. Example code:

using (SPSite spSite = new SPSite(site))
{
    using (SPWeb spWeb = spSite.OpenWeb())
    {
        StringBuilder deletebuilder = BatchCommand(spWeb.Lists[list]);

        spSite.RootWeb.ProcessBatchData(deletebuilder.ToString());
    }
}
private static StringBuilder BatchCommand(SPList spList)
{
    StringBuilder deletebuilder = new StringBuilder();
    deletebuilder.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");
    string command = "<Method><SetList Scope=\"Request\">" + spList.ID +
        "</SetList><SetVar Name=\"ID\">{0}</SetVar><SetVar Name=\"Cmd\">Delete</SetVar></Method>";

    foreach (SPListItem item in spList.Items)
    {
        deletebuilder.Append(string.Format(command, item.ID.ToString()));
    }
    deletebuilder.Append("</Batch>");
    return deletebuilder;
}

See Bulk delete items in SharePoint for more information

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.