Sunday, March 17, 2013

SharePoint List Pagination using Web Service API

Microsoft MSDN article “Lists.GetListItems Method (Lists)” documented how pagination works when using Sharepoint List web services. However I found this document is confusing and there is no code sample on how to do that.
Here is how it is documented at in the MSDN article.
Paging
A string that contains data needed to support paging, including a ListItemCollectionPositionNext attribute. If left empty, the list items returned start from the beginning of the list. Client applications should supply the value of a server-supplied bookmark. The server returns the next page of data based on the bookmark supplied. This element is optional, and its default value is an empty string.
…….
The GetListItems method supports server-side paging. The XML data returned by this method includes a ListItemCollectionPositionNext attribute inside the rs:Data element that contains the information to support paging. This string contains data for the fields in the sort and for other items needed for paging. You should consider this string internal and not to be modified; modifying it can produce unexpected results. The following example shows the form of this return value when paging is supported.
Xml
<rs:Data ListItemCollectionPositionNext="Paged=TRUE&p_ID=100&View=%7bC68F4A6A%2d9AFD%2d406C%2dB624%2d2CF8D729901E%7d&PageFirstRow=
      101" Count=1000 >
   <z:row ows_FirstName="Nancy" ows_LastName="Name" ….. />   ...
</rs:Data>
To get the next page of data, the queryOption parameter is used, as shown in the following example. 
Xml
<QueryOptions>
  <Paging ListItemCollectionPositionNext="Paged=TRUE&p_ID=100&View=%7bC68F4A6A%2d9AFD%2d406C%2dB624%2d2CF8D729901E%7d&PageFirstRow=
   101" />
</QueryOptions>

I will explain what this really means using an example. To get a list of items without considering pagination, you will usually do
string url = "http://machineName/sites/AllSharepointNews";
/*Declare and initialize a variable for the Lists Web service.*/
listService = new MachineName.Lists();
/*Authenticate the current user by passing their default
credentials to the Web service from the system credential cache.*/
listService.Credentials = System.Net.CredentialCache.DefaultCredentials;
/*Set the Url property of the service for the path to a subsite.*/
listService.Url = url+"/_vti_bin/Lists.asmx";
XmlDocument doc = new XmlDocument();
XmlElement query = doc.CreateElement("Query");
query.InnerXml = "<OrderBy><FieldRef Name='Created' Ascending='FALSE' /></OrderBy>";
XmlNode results = listService.GetListItems("ITNews", null, query, null, null, null, null);
this.tbResult.Text += "Query :" + query.OuterXml + Environment.NewLine;
this.tbResult.Text += results.OuterXml.Replace("<", "\r\n<").Replace("ows", "\r\n\tows");

A sample output from this code is
Query :<Query><OrderBy><FieldRef Name="Created" Ascending="FALSE" /></OrderBy></Query>
<listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:r
owset" xmlns:z="#R
owsetSchema" xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<rs:data ItemCount="20">
<z:row
ows_Attachments="0"
ows_LinkTitle="Pirates of the Amazon hits the rocks - CNET News"
ows_ASN_Link="http://news.google.com/news/url?sa=T&amp;ct=us/17-0&amp;fd=R&amp;url=http://news.cnet.com/8301-17939_109-10116503-2.html&amp;cid=1277424647&amp;ei=ob08SdasL4b2gAOGiIzICQ&amp;usg=AFQjCNF9OKsyjlkSLG_7h9eDNuKHneX68w"
ows_ASN_Guid="tag:news.google.com,2005:cluster=http://news.cnet.com/8301-17939_109-10116503-2.html"
ows_ASN_PubDate="Sat, 06 Dec 2008 18:42:13 GMT"
ows__ModerationStatus="0"
ows__Level="1"
ows_Title="Pirates of the Amazon hits the rocks - CNET News"
As you can see all 20 items in the list are returned and ordered by creation date. Without considering pagination, this code actually contains a bug. Once your item grows to number larger than 100, no item starting 101 will returned. 100 is the default pagination number defined in Sharepoint List default view.
On Sharepoint web UI, navigate to the list “Settings” -> “List Settings” -> “Views” -> “All Items” -> “Item Limit”. Change the “Number of items to display” to “3” and select “Display items in batches of the specified size ”. Run the above code again, you will get
Query :<Query><OrderBy><FieldRef Name="Created" Ascending="FALSE" /></OrderBy></Query>
<listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:r
owset" xmlns:z="#R
owsetSchema" xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<rs:data ItemCount="3" ListItemCollectionPositionNext="Paged=TRUE&amp;p_Created=20081208%2006%3a24%3a38&amp;p_ID=20">
<z:row
ows_Attachments="0"
ows_LinkTitle="Pirates of the Amazon hits the rocks - CNET News"
ows_ASN_Link="http://news.google.com/news/url?sa=T&amp;ct=us/17-0&amp;fd=R&amp;url=http://news.cnet.com/8301-17939_109-10116503-2.html&amp;cid=1277424647&amp;ei=ob08SdasL4b2gAOGiIzICQ&amp;usg=AFQjCNF9OKsyjlkSLG_7h9eDNuKHneX68w"
ows_ASN_Guid="tag:news.google.com,2005:cluster=http://news.cnet.com/8301-17939_109-10116503-2.html"
ows_ASN_PubDate="Sat, 06 Dec 2008 18:42:13 GMT"
ows__ModerationStatus="0"
ows__Level="1"
ows_Title="Pirates of the Amazon hits the rocks - CNET News"
Notice the query is not changed. The server side changes affect the number of items returned. Once the server side pagination is enabled, it will affect how the client can read the list. To get next page you need to leverage the ListItemCollectionPositionNext returned in the response. (Server side pagination is not the only way you can control the pagination. You can control the pagination from client only. I will show how to do that later.)
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 + "' />";
}
The code creates a queryOptions xml node and insert a “<Paging>” node with its property ListItemCollectionPositionNext set to the value returned in XmlNode from GetListItems. As suggested by the MSDN article, you don’t need to do anything to this returned position string. Just make sure you retrieved it as “InnerXml” and use it as “InnerXml”. This string is XML encoded. If you access it as “InnerText” or assign it as “InnerText” queryOption, the XML encoding will break and you will get XML parsing errors.
A sample output is
------------ Page 1 ----------------
Query :<Query><OrderBy><FieldRef Name="Created" Ascending="FALSE" /></OrderBy></Query>
QueryOptions :<QueryOptions />
<listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:r
owset" xmlns:z="#R
owsetSchema" xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<rs:data ItemCount="3" ListItemCollectionPositionNext="Paged=TRUE&amp;p_Created=20081208%2006%3a24%3a38&amp;p_ID=20">
<z:row
ows_Attachments="0"
ows_LinkTitle="Pirates of the Amazon hits the rocks - CNET News"
ows_ASN_Link="http://news.google.com/news/url?sa=T&amp;ct=us/17-0&amp;fd=R&amp;url=http://news.cnet.com/8301-17939_109-10116503-2.html&amp;cid=1277424647&amp;ei=ob08SdasL4b2gAOGiIzICQ&amp;usg=AFQjCNF9OKsyjlkSLG_7h9eDNuKHneX68w"
ows_ASN_Guid="tag:news.google.com,2005:cluster=http://news.cnet.com/8301-17939_109-10116503-2.html"
ows_ASN_PubDate="Sat, 06 Dec 2008 18:42:13 GMT"
ows__ModerationStatus="0"
ows__Level="1"
ows_Title="Pirates of the Amazon hits the rocks - CNET News"
ows_ID="18"
ows_
owshiddenversion="5"
ows_UniqueId="18;#{FBA470A3-AA57-4535-A4E8-15938CE07F1C}"
……
</listitems>------------ Page 7 ----------------
Query :<Query><OrderBy><FieldRef Name="Created" Ascending="FALSE" /></OrderBy></Query>
QueryOptions :<QueryOptions><Paging ListItemCollectionPositionNext="Paged=TRUE&amp;p_Created=20081208%2006%3a24%3a34&amp;p_ID=5" /></QueryOptions>
<listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:r
owset" xmlns:z="#R
owsetSchema" xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<rs:data ItemCount="2">
<z:row
ows_Attachments="0"
ows_LinkTitle="NASA delays Mars rover mission two years - CNET News"
ows_ASN_Link="http://news.google.com/news/url?sa=T&amp;ct=us/0-0&amp;fd=R&amp;url=http://news.cnet.com/8301-11386_3-10116542-76.html&amp;cid=1277532394&amp;ei=ob08SdasL4b2gAOGiIzICQ&amp;usg=AFQjCNHincV-C1Mt5nAkJcKb5zp97W3Diw"
ows_ASN_Guid="tag:news.google.com,2005:cluster=http://news.cnet.com/8301-11386_3-10116542-76.html"
ows_ASN_PubDate="Sun, 07 Dec 2008 15:38:43 GMT"
ows__ModerationStatus="0"
ows__Level="1"
ows_Title="NASA delays Mars rover mission two years - CNET News"
ows_ID="1"
ows_
owshiddenversion="5"
ows_UniqueId="1;#{9CF48EE7-4F5A-4703-93E2-64D564999309}"
ows_FSObjType="1;#0"
ows_Created_x0020_Date="1;#2008-12-07 22:24:33"
ows_Created="2008-12-07 22:24:33"
As you can see from the output, when the last page is hit, the “rs:data” will not return ListItemCollectionPositionNext property. You can use it as a sign to properly terminate the reading.
As I said you must add this pagination handling to all your list item retrieve logic if your list view’s “Item Limit” is set to the default 100 with “Display items in batches of the specified size ” to prevent losing data after 100 items.
Another way to do pagination is to initiate it from the client side. According to the GetListItems API
public XmlNode GetListItems (string listName, string viewName, XmlNode query, XmlNode viewFields, string rowLimit, XmlNode queryOptions, string webID)
You can set the rowLimit to initiate pagination. It is a little strange that this rowLimit is defined as string instead of integer.
The only line of code that needs to be changed is
XmlNode results = listService.GetListItems("ITNews", null, query, null, "5", queryOptions, null);

The actually relationship between the "viewName", "query", "viewField" and "rowLimit" is that the "query", "viewField" and "rowLimit" will override the properties defined in "viewName" or default view. This means the View either the default view of the list or any customized view defined for the list will have these three properties to generate views.
When using GetListItems without specifiy these thress parameters, the default view on the server or view defined in "viewName" paramter will be used to get the list items. For example, the default view will return only first 100 items from Sharepoint. Calling GetListItems will only return first 100 items. Any more item will not be returned. To override this, you need to give a number as
XmlNode results = listService.GetListItems("ITNews", null, query, null, "5000", queryOptions, null);
and consider to handle pagination when the number of items returned could be more than 5000.

1 comment:

  1. Nice post. I tried this code and got an error. First, it did not jump to 5001 (I used max 5000). all page contents are the same and kept running.

    ReplyDelete