Altijd als eerste onze nieuwste blogs lezen? Laat je email adres achter en je ontvangt een bericht als wij een nieuw blog plaatsen.

When a large amount of items must be retrieved from Sharepoint, someone will encounter problems because of the List View Threshold. This threshold is a limit to the maximum number of items that can be requested at once and is set to 5000 by default.

Sharepoint

Onze technische blogs zijn in het Engels. Dit doen wij omdat wij menen dat technische kennis van Sitecore en Sitefinity grensoverschrijdend moet zijn. Wij leren veel van buitenlandse developers, wij delen onze kennis ook graag met hen. Mochten er in de code fouten zitten of punten ter verbetering, dan gaan we graag het gesprek aan. 

When a large amount of items must be retrieved from Sharepoint, someone will encounter problems because of the List View Threshold. This threshold is a limit to the maximum number of items that can be requested at once and is set to 5000 by default. This comes from the limitation in Microsoft SQL server which will lock the full table if more than 5000 items are involved with the query. Therefore it is not recommended to set the List View Threshold higher than 5000 items.

So when the number of items in a list exceeds this number something must be changed to maintain access to the list because not all items can be shown at once because of the threshold. The first solution which probably will be provided when you search for it is creating an index. This is a perfect way to solve problems with getting data from a large list, but...

…what if you need to get more than 5000 items at once?

This can’t be achieved by using indexes, because the query will still involve more than 5000 items. In our case we needed to get all items from a list which had more items than the List View Threshold. These items where needed in an application where the user could select one of all those items.

This can be achieved by making use of the paging in Sharepoint List Views. By limiting the number of items that is returned by a List View, the items still can be accessed in a large list and with the use of paging all items are still available. This seemed the best solution for our problem where we wanted to get all items, but not to change the List View Threshold.

Now let’s see how this is done

First we change the item limit property of the List View we request in our code:


We need to have 5000 items per page, because then we have to make less calls as possible to get all the items.

After that we need to change the code to get the items from the list, as it will now return only the first 5000 items regarding the item limit. We have to define paging in the query options property in the request. To make this possible, we need two properties: p_ID and PageFirstRow. The p_ID is the ID of the last item on the current page. The PageFirstRow is the number of the first item on the next page. In code this looks like this:


get
{
    IList entities = new List();

    using (var client = new ListsSoapClient())
    {
        bool completed = false;
        int threshold = 5000; //central administration -> manage web applications -> resource throttling
        int counter = 0;

        while (!completed)
        {
            int itemCount = counter * 5000;

            var rq = new ListsService.GetListItemsRequest(new ListsService.GetListItemsRequestBody());
            rq.Body.listName = "ListWithAName";

            //first page will be returned default
            if (counter > 0)
            {
                string p_Id = entities[itemCount - 1].ID;
                string pageFirstRow = (itemCount + 1).ToString();

                //structure of the xml must be: 
                //
                XNamespace xns = "";
                XElement xmlTree1 = new XElement(xns + "QueryOptions",
                    new XElement(
                        xns + "Paging", 
                        new XAttribute(
                            "ListItemCollectionPositionNext", 
                            string.Format("Paged=TRUE&p_ID={0}&View=%7BAF8959C7%2d778B%2d4B09%2dB6B6%2dCE97182607DF%7D&PageFirstRow={1}", p_Id, pageFirstRow)))
                    );

                rq.Body.queryOptions = xmlTree1;
            }

            var rs = client.GetListItems(rq);

            XElement listcollection = rs.Body.GetListItemsResult;

            XNamespace ns_rs = "urn:schemas-microsoft-com:rowset";
            XNamespace ns_z = "#RowsetSchema";

            IList data = listcollection.Element(ns_rs + "data").Elements(ns_z + "row").ToList();

            foreach (var row in data)
            {
                entities.Add(Helpers.XmlConvertor.ConvertXmlElementToEntity(row));
            }

            if (data.Count != threshold)
            { completed = true; }
            else
            { counter++; }
        }
    }

    return entities;
}

Conclusion

Now the complete content of the list will be returned. If the first page contains more items than the threshold is set to, it will look for the next page. If that page also has that amount of items, the third page will be retrieved, and so on. There will be no table lock and the application can still retrieve all the data from the list!



Meer achtergrond nodig? Neem contact met ons op

Deel deze pagina