Tuesday, 29 August 2017

Get all Items from a SharePoint List using CSOM

In this article, I am going to write C# code to find all items in a SharePoint list with Client-side Object Model (CSOM). This code also solves the List View Threshold problem. The List View Threshold defines the maximum limit to retrieve a number of items in a single query call. By default this limit is set to 5000, and in OneDrive for Business this limit is 20000. So any list with more than 5000 items will return an error (Ex: The number of items in this list exceeds the list view threshold, which is 5000 items).

By using CSOM, you can retrieve list items page by page by setting row limit, this will avoid the list view threshold error as we are querying only certain amount of rows in a single query.

Retrieve all items from a SharePoint library which has more than 5000 items

The following C# based CSOM code fetch all items from a SharePoint online library. It will get 50 rows in every page. You can change the row limit as per your wish.
public static List<ListItem> GetAllListItemsInaList1()
{
    List<ListItem> items = new List<ListItem>();
    string sitrUrl = "https://spotenant.sharepoint.com/sites/yoursite";
    using (var ctx = new ClientContext(sitrUrl))
    {
        //ctx.Credentials = Your Credentials
        ctx.Load(ctx.Web, a => a.Lists);
        ctx.ExecuteQuery();

        List list = ctx.Web.Lists.GetByTitle("Documents");
        ListItemCollectionPosition position = null;
        // Page Size: 50
        int rowLimit = 50;
        var camlQuery = new CamlQuery();
        camlQuery.ViewXml = @"<View Scope='RecursiveAll'>
            <Query>
                <OrderBy Override='TRUE'><FieldRef Name='ID'/></OrderBy>
            </Query>
            <ViewFields>
                <FieldRef Name='Title'/><FieldRef Name='Modified' /><FieldRef Name='Editor' />
            </ViewFields>
            <RowLimit Paged='TRUE'>" + rowLimit + "</RowLimit></View>";
        do
        {
            ListItemCollection listItems = null;
            camlQuery.ListItemCollectionPosition = position;
            listItems = list.GetItems(camlQuery);
            ctx.Load(listItems);
            ctx.ExecuteQuery();
            position = listItems.ListItemCollectionPosition;
            items.AddRange(listItems.ToList());
        }
        while (position != null);
    }           
    return items;
}

Advertisements
Advertisements

No comments:

Post a Comment