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."); | |
} |