How To Get Data From List Beyond Threshold Limit Through Calmquery

Most of the time, we face a situation when threshold limit for list exceeds and we are not able to pull records beyond threshold. In this blog we will see how to get List items beyond threshold limit.

Most of us face this situation where List Threshold exceeds and the following error is shown.

 

There are many ways to overcome List Threshold, like increasing the threshold value, but it is not recommmended by Microsoft as it creates performance problems.

I have created a scenario where List Threshold has increased and we need to overcame the situation by using List item collection position.

Go to Central Administration > Manage Web Applications > General Settings > Resource Throttling > List View Threshold.

Below is the screenshot of resource throttling which has been set to 2000.

I have created a list by the name Test and created 2432 items in it.

Below is the code that I have used to fetch all 2432 items in single query.

DataTable dt = new DataTable();
dt.Columns.Add("Name1", typeof(string));
dt.Columns.Add("ID", typeof(int));
using(SPSite site = new SPSite(SPContext.Current.Site.ID)) {
    using(SPWeb web = site.OpenWeb()) {
        SPList list = web.Lists.TryGetList("Test");
        SPListItemCollectionPosition itemPosition = null;
        while (true) {
            SPQuery camlQuery = new SPQuery();
            camlQuery.ListItemCollectionPosition = itemPosition;
            camlQuery.RowLimit = 2000;
            camlQuery.ViewFields = "<FieldRef Name='Name1'/><FieldRef Name='ID'/>";
            camlQuery.ViewFieldsOnly = true;
            camlQuery.Query = "<View></View>";
            SPListItemCollection listItems = list.GetItems(camlQuery);
            itemPosition = listItems.ListItemCollectionPosition;
            DataTable dtitems = listItems.GetDataTable();
            dt.Merge(dtitems);
            dt.AcceptChanges();
            if (itemPosition == null) {
                break; // TODO: might not be correct. Was : Exit While
            }
            //Page.Response.Write(itemPosition.PagingInfo);
        }
    }
}
if (dt != null && dt.Rows.Count > 0) {
    GridView1.DataSource = dt;
    GridView1.DataBind();
}

Below is the screenshot for data in the webpart.

Note

Ensure that the below three properties are set before setting Query property for SPQuery.

camlQuery.ListItemCollectionPosition = itemPosition;
camlQuery.ViewFieldsOnly = true;
camlQuery.RowLimit = 2000;