More SharePoint list view threshold fun

Here’s a quick follow-up to my previous post on dealing with SharePoint list view thresholds. I just bumped up against another case where I had to change some code to deal with it.

To recap the project a bit, I am writing a console app that will import a bunch of data into a SharePoint site. Since I’m human, and I make mistakes, the first step of this importer is to delete any existing data in those lists (which would be leftover from the previous test run).

I had a simple solution for doing that, deleting records in batches of 100, based somewhat on this example. I assumed that would work OK, even with larger lists, but I didn’t take into account that the first step in my process was to get all items in the list. That, of course, fails for a very large list. So I had to change my code to initially get only the first 4000 records in the list. (That can be done with the RowLimit clause in CAML.) Then, I delete from that subset in batches of 100. Then, I just repeat that until there are no more records left.

As far as I can tell, there’s no SharePoint CSOM equivalent to SQL’s “truncate table”, which would have made this much easier. And I feel like I’m probably still not doing this in the most efficient way. If I was creating a process that needed to do this repeatedly, instead of just a few times, I’d dig into it some more. And if I was retrieving items instead of deleting them, I’d probably do something with ListItemCollectionPosition.

private void deleteAllFromList(ClientContext cc, List myList)
{
int queryLimit = 4000;
int batchLimit = 100;
bool moreItems = true;
string viewXml = string.Format(@"
<View>
<Query><Where></Where></Query>
<ViewFields>
<FieldRef Name='ID' />
</ViewFields>
<RowLimit>{0}</RowLimit>
</View>", queryLimit);
var camlQuery = new CamlQuery();
camlQuery.ViewXml = viewXml;
while (moreItems)
{
ListItemCollection listItems = myList.GetItems(camlQuery); // CamlQuery.CreateAllItemsQuery());
cc.Load(listItems,
eachItem => eachItem.Include(
item => item,
item => item["ID"]));
cc.ExecuteQuery();
var totalListItems = listItems.Count;
if (totalListItems > 0)
{
Console.WriteLine("Deleting {0} items from {1}…", totalListItems, myList.Title);
for (var i = totalListItems 1; i > 1; i)
{
listItems[i].DeleteObject();
if (i % batchLimit == 0)
cc.ExecuteQuery();
}
cc.ExecuteQuery();
}
else
{
moreItems = false;
}
}
Console.WriteLine("Deletion complete.");
}

view raw
deleteAllFromList.cs
hosted with ❤ by GitHub

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.