How does SharePoint List pagination work?

Question

 

I am trying to page the requests of Data from SPLists. I am using the SPQuery's ViewFields property to only get the fields required. Also I am trying to use the SPQuery's ListItemCollectionPosition property (Paged=TRUE&p_ID={0}) and RowLimit to do the same. I am not able to get it working for all scenarios. They are listed below:

1. If an item in the list is deleted then i will not be able to set the p_ID correctly.

2. If the SPQuery has an orderby clause then setting the p_ID is tough.

3. Moreover, if the caml query is trying to filter listitems based on conditions, then i dont know how to set the ListItemCollectionPosition property.

 

I noticed that, the default list view is able to page according to the number of items set as the RowLimit and irrespective of items deleted in between, it is able to paginate properly. Please help.

 

Code Block

private const string listItemCollectionPostion = "Paged=TRUE&p_ID={0}";

public class PagingInfo
{

      // corresponding properties exist
        private int elementsPerPage;
        private int pageRequested;
        private string orderByField;

        private bool isAscending;

}

 

 

 --  Method code below --

Code Block

// Set the listitemcollection position to the page requested * elements per page
int itemCollectionPosition = 0;
itemCollectionPosition = (pagingInfo.PageRequested * pagingInfo.ElementsPerPage);
query.ListItemCollectionPosition = new SPListItemCollectionPosition(string.Format(listItemCollectionPostion, itemCollectionPosition));
           
// Set the rowlimit to the number of items requested
query.RowLimit = Convert.ToUInt32(pagingInfo.ElementsPerPage);

totalPages = Convert.ToInt32(Math.Ceiling( web.Lists[listName].ItemCount / (decimal)pagingInfo.ElementsPerPage));

 

 

If anybody could post a code sample which does the pagination for SharePoint lists. That would be great.

 

Thanks,

Puneet.H

 

Answer 1

1. If an item in the list is deleted then i will not be able to set the p_ID correctly.

2. If the SPQuery has an orderby clause then setting the p_ID is tough.

3. Moreover, if the caml query is trying to filter listitems based on conditions, then i dont know how to set the ListItemCollectionPosition property

I have written a page,perhaps it's helpful to you!

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;


public partial class QueryPager : System.Web.UI.Page
{
    /// <summary>
    /// 
    /// </summary>
    int iPageSize = 2;
    /// <summary>
    /// the pages you wan to  display
    /// </summary>
    int iPages = 5;
    int iRowsCount = 0;
    int iPageCount = 0;
    SPSite site;
    SPWeb web;
    SPList list;
    SPQuery query;
    protected void Page_Load(object sender, EventArgs e)
    {
        site = new SPSite("http://MossSvr");
        web = site.AllWebs["news"];
        list = web.Lists["MyList"];
        query = new SPQuery();
        query.ViewAttributes = "Scope='RecursiveAll'";
        query.ViewFields = "<FieldRef Name='ID'/><FieldRef Name='FSObjType'/><FieldRef Name='Title'/>";
        query.Query = "<Where><Eq><FieldRef Name='ContentType'/><Value Type='Text'>Item</Value></Eq></Where><OrderBy><FieldRef Name=\"Title\" Ascending=\"false\"/></OrderBy>";

        if (Page.IsPostBack)
        {
            //
            if (ddlPageNumber.SelectedItem.Text == "more")
            {
                //
                GetResults(iPageSize * iPages, true, ddlPageNumber.SelectedItem.Value);
            }
            else
            {
                GetResults(iPageSize, false, ddlPageNumber.SelectedItem.Value);
            }
        }
        else
        {
            //
            GetResults(iPageSize * iPages, true, string.Empty);
        }
        //Finalize
        web.Dispose();
        site.Dispose();
    }
    private void GetResults(int iRowLimit,Boolean bMore,string sPageInfo)
    {
        query.RowLimit = (uint)iRowLimit;
        //
        if (sPageInfo.Length > 0)
        {
            query.ListItemCollectionPosition = new SPListItemCollectionPosition(sPageInfo);
        }
        SPListItemCollection listitems = list.GetItems(query);
        iRowsCount = listitems.Count;
        DataTable dtResults = listitems.GetDataTable();
        if (bMore)
        {
            iPageCount = (int)Math.Ceiling(iRowsCount / (decimal)iPageSize);
            int ddlListItemCount = ddlPageNumber.Items.Count;
            if (ddlListItemCount > 0)
            {
                //del More
                ddlListItemCount--;
                ddlPageNumber.Items.Remove(ddlPageNumber.Items[ddlListItemCount]);
            }
            if (iRowsCount > 0)
            {
                int iRowNum = 0;
                SPListItem listitem = null;
               
                //new  page
                ddlPageNumber.Items.Add(new ListItem(Convert.ToString(ddlListItemCount + 1), sPageInfo));
                for (int i = 2; i <= iPageCount; i++)
                {
                    //""
                    iRowNum = (i-1) * iPageSize - 1;
                    listitem = listitems[iRowNum];
                    sPageInfo = "Paged=TRUE&p_FSObjType=" + listitem["FSObjType"].ToString() + "&p_Title=" + listitem["Title"].ToString() + "&p_ID=" + listitem["ID"].ToString();
                    ddlPageNumber.Items.Add(new ListItem(Convert.ToString(ddlListItemCount + i), sPageInfo));
                }
                //Display More...
                if (iPageCount == iPages && listitems.ListItemCollectionPosition != null)
                {
                    ddlPageNumber.Items.Add(new ListItem("more", listitems.ListItemCollectionPosition.PagingInfo));
                }
           
                //Remove additional rows...
                if (iRowsCount > iPageSize)
                {
                    for (int i = iRowsCount - 1; i >= iPageSize; i--)
                    {
                        dtResults.Rows.RemoveAt(i);
                    }
                }
            }
        }
        GridView1.DataSource = dtResults;
        GridView1.DataBind();
    }
}
.

Answer 2

Please see these two articles.

Sharepoint List Pagination using Web Service API

Sharepoint List Pagination using Object Model

XmlDocument doc = new XmlDocument();

XmlElement query = doc.CreateElement("Query");

query.InnerXml = "<OrderBy><FieldRef Name='Created' Ascending='FALSE' /></OrderBy>";

XmlNode positionNext = null;

XmlNode queryOptions = doc.CreateElement("QueryOptions");

int count = 0;

while(true)

{

count++;

this.tbResult.Text += "------------ Page " + count + " ----------------" + Environment.NewLine;

XmlNode results = listService.GetListItems("ITNews", null, query, null, null, queryOptions, null);

this.tbResult.Text += "Query :" + query.OuterXml + Environment.NewLine;

this.tbResult.Text += "QueryOptions :" + queryOptions.OuterXml + Environment.NewLine;

this.tbResult.Text += results.OuterXml.Replace("<", "\r\n<").Replace("ows", "\r\n\tows");

positionNext = results.SelectSingleNode("//@ListItemCollectionPositionNext");

if (positionNext == null)

break; // break if it is the last page

else

// set next page info if there is more page

queryOptions.InnerXml = "<Paging ListItemCollectionPositionNext='" + positionNext.InnerXml + "' />";

}

Answer 3

Here is a more detailed analysis of how to get this to work:

http://blogs.msdn.com/colbyafrica/archive/2009/02/19/learning-sharepoint-part-vi-list-pagination.aspx

HTH,

Colby

Answer 4

What about pages with folders? Unfortunately Colby Africa didn't cover that in his (her?) article.
The problem is that, when there is at least one folder on the page, the PagingInfo must contain p_FSObjType=1. And when there are no folders - p_FSObjType=0.

When you paginating to the NEXT page with ListItemCollectionPosition from the previous clause - there is no problem with that at all. But how to get a link to the PREVIOUS page in a similiar manner?

When you paginating with manually making PagingInfo - you can not know about p_FSObjType value, what makes hard to list pages with folders correctly.

I'm stuck with that stupid pagination.. It's totally a nonsense what Microsoft had done with it..

Answer 5

Hi Aleksanderis,

I have implemented the same scenario with folders contains list items. See my post: http://blogs.msdn.com/dbadki/archive/2008/10/08/caml-query-execution-using-pagination-for-custom-columns.aspx

Regarding browsing to the previous page, you sort the data in opposite direction and pass the item ID in page info parameters. E.g.: Let's say page has got 50 items with 10 items/page with Ascending sorting based on Created Date  and currently, user is on 2nd page. When user clicks on previous page i.e. page 1, you need to pass the item id of 11th item and sort it in opposite direction i.e. descending to get the items.

Let me know still you are facing the issue.

Regards
Deepak

---

db

Answer 6

I looked at your example, Deepak. However I hadn't found anything about folders and p_FSObjType pararameter. If you form PagingInfo string manually - it's important to provide the mentioned parameter with 0 or 1 value. Otherwise pagination will be buggy.

Or did I miss something?


Anyway, I solved my paging problems in another way. I hadn't any problem navigating to the NEXT pages. All that I need - was navigation to the previous pages. I solved it simply by storing PagingInfo strings of the previous pages. Now it works like a charm, at least I hadn't found any big problem for now.

Answer 7

Aleksanderis,

Your solution only works if you store your entire navigation history, which seems a pain.

In digging into the issue a bit deeper, I think I've figured out the general algorithm:

The algorithm works like so:

1. When paging FORWARD, capture the ID <AND SORT FIELD VALUE> of the first item on the newly retrieved result collection.
2. When paging BACKWARDS, use the previously captured ID <AND SORT FIELD VALUE> of the first item on the current result collection to generate the paging string.
3. Once paged BACKWARDS, capture the ID <AND SORT FIELD VALUE> of the first item on the loaded page for the next BACKWARDS operation (just leave this off of the input query if going forwards again).

Took a while to hack this out, but it works for paging forwards and backwards seamlessly.

More details here: http://www.charliedigital.com/PermaLink,guid,99499571-6b81-49b5-ac03-b5c5eb5054a7.aspx

---

http://www.charliedigital.com

Answer 8

Huh, that's an old post. I even didn't remember how I solved it, but it works somewhere almost in the production, I believe. :)

Anyway, you didn't get a problem again, Charlie. The algorithm was clear to me, I did know about all these steps and they worked perfectly. Unfortunately, until folders came into the play. All the mess is because of folders and you don't mention about them again, as others. Can you confirm your solution works with folders in the list, document library? :)

The problem with folders is - when there is one or more folders on the page, the p_FSObjType parameter should be added. There is no conventional way to determinate when this parameter is needed, because you must create PageingInfo string for the NEXT page before you actually see that page. In this state you don't know is there folder on the page, or no.

Without p_FSObjType parameter folders will not be count in the pagination algorithm, or something like that. In result the pagination doesn't work correctly with folders (without extra tricks).

Answer 9

Ah I see.  It's true, I did not take folders into consideration (but then again, I am lucky enough that my use case doesn't have to deal with it :D)

I think this is still useful, though, since there aren't many resources that I came across that break down how to page to a previous page (and it's actually not that hard once you sit down and figure it out).

If I end up having to deal with folders, I'll update with my findings.

---

http://www.charliedigital.com

Answer 10

Please visit here for more info

http://www.anmolrehan-sharepointconsultant.com/2011/10/client-object-model-access-large-lists.html

Answer 11

I did some helper classes to work with pagination in Sharepoint. http://hveiras.wordpress.com/2011/11/07/listpagert-using-splistitemcollectionposition/

Answer 12

You can also visit my blog which explain how to apply paging with sorting,caml query, folder query on sharepoint list by using SPQuery and SPListItemCollectionPosition.

SharePoint List Pagination using SPListItemCollectionPosition

Thanks & Regards,
Avinash
My Blog: SharePoint Solutions

Answer 13

Aleksanderis,

Your solution only works if you store your entire navigation history, which seems a pain.

In digging into the issue a bit deeper, I think I've figured out the general algorithm:

The algorithm works like so:

• When paging FORWARD, capture the ID <AND SORT FIELD VALUE> of the first item on the newly retrieved result collection.
• When paging BACKWARDS, use the previously captured ID <AND SORT FIELD VALUE> of the first item on the current result collection to generate the paging string.
• Once paged BACKWARDS, capture the ID <AND SORT FIELD VALUE> of the first item on the loaded page for the next BACKWARDS operation (just leave this off of the input query if going forwards again).

Took a while to hack this out, but it works for paging forwards and backwards seamlessly.

More details here: http://www.charliedigital.com/PermaLink,guid,99499571-6b81-49b5-ac03-b5c5eb5054a7.aspx

---

http://www.charliedigital.com

I went through your post. You have assumed ascending sort order, could you please tell what changes need to be made if my sorting is descending.