SharePoint List Pagination using SPListItemCollectionPosition

Description

This article will show you how to apply paging with sorting on search grid which gets data from sharepoint list by using SPQuery and SPListItemCollectionPosition.

Requirement:-

I have search page which is divided in 2 part; Search filter criteria and other is display result in grid. This page query to sharepoint list using object model and display result.
By thinking of performance; you will never try to get all results from sharepoint list. You have to query page by page. I mean query sharepoint list only for 10 page (page size=10) and display data.

Here I am giving detail about how to query for 10 page and get data. Then display pagination.

Precisely my page will do below things:-

1. Put caml query (only for page size which is set to 10 in my example) on sharepoint list
2. Filter on folder basis
3. Apply sorting
4. Display paging on search result page.

My hunt On Google:-

I have search on google lots but no where I find proper solution which handle sorting, paging with filter using caml query and folder query.
But still I like msdn and below 2 blogs from where I got basic understanding
http://www.directsharepoint.com/2011/03/step-by-step-guide-to-implement-paging.html
http://blogs.msdn.com/b/colbyafrica/archive/2009/02/19/learning-sharepoint-part-vi-list-pagination.aspx

The Code:  

Below are 2 classes SearchDocuments and SearchPaging.
SearchDocuments: This class is code behind file my user control which manage search.
SearchPaging: This class set property for search results and search paging.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
public partial class SearchDocuments : System.Web.UI.UserControl
{
	public string Next
	{
		get { return ViewState["Next"] == null ? string.Empty : (string)ViewState["Next"]; }
		set { ViewState["Next"] = value; }
	}
	public string Previous
	{
		get { return ViewState["Previous"] == null ? string.Empty : (string)ViewState["Previous"]; }
		set { ViewState["Previous"] = value; }
	}
	// store column name for order by query
	private string DataSortExpression
	{
		get { return ViewState["DataSortExpression"] == null ? 
			Constant.SearchResultColumnDocumentName : (string)ViewState["DataSortExpression"]; }
		set { ViewState["DataSortExpression"] = value; }
	}
	// store direction; Acending or decending for order by query
	private SortDirection DataSortDirection
	{
		get { return ViewState["DataSortDirection"] == null ? 
				SortDirection.Ascending : (SortDirection)ViewState["DataSortDirection"]; }
		set { ViewState["DataSortDirection"] = value; }
	}
 
	/// <summary>
	/// This function get call on search button and prev or next link
	/// this manage paging and grid results.
	/// </summary>
	private void BindListData(Dictionary<string, string> searchFilters, 
							string pagingInfo, int currentPageNumber)
	{
		try
		{
			ViewState["CurrentPage"] = currentPageNumber.ToString();
			uint rowCount = 10; //Page Size
 
			// below 2 string format are good if you have orderby in your caml query.
			string nextPageString = "Paged=TRUE&p_FSObjType=0&p_{0}={1}&p_ID={2}";
			string PreviousPageString = "Paged=TRUE&p_FSObjType=0&PagedPrev=TRUE&p_{0}={1}&p_ID={2}";
			/*
			  below 2 string format are good if you dont have orderby in your caml query.
			  string nextPageString = "Paged=TRUE&p_{0}={1}&p_ID={2}";
			  string PreviousPageString = "Paged=TRUE&PagedPrev=TRUE&p_{0}={1}&p_ID={2}";
	*/
			int pageOrderNumber = ((currentPageNumber - 1) * Convert.ToInt32(rowCount)) + 1;
			string orderBy = string.Empty;
			// set order by query.
			// By default DataSortExpression value is ID. but on click of sorting it change column value
			if (DataSortDirection == SortDirection.Ascending)
				orderBy = @"<OrderBy><FieldRef Name='" + 
							DataSortExpression + "' Ascending='True' /></OrderBy>";
			else
				orderBy = @"<OrderBy><FieldRef Name='" + 
							DataSortExpression + "' Ascending='False' /></OrderBy>";
 
			// it generate caml query based on my search filters
			// you can write you own logic to build caml query
			string query = GetQuery(searchFilters);
			query = string.Concat(query, orderBy); // add order by in query
			// get folder name from you search filter criteria
			string folderName = searchFilters.TryGetValue("FolderName", out folderName);
 
			// pass paging info, query row limit in below function
			// this function will return object of my custom class which hold datatable object and paging info 
			// Once current function ends; You will find detail about GetAllSearch function in this blog
			SearchPaging searchData = GetAllSearch(query, pagingInfo, rowCount, pageOrderNumber, folderName);
 
			if (searchData != null && searchData.SearchItems != null)
			{
				SPListItemCollectionPosition itemPosition = searchData.ItemCollPosition;
				// ctlGrid is a object of my SPGridView
				ctlGrid.DataSource = searchData.SearchItems;
				ctlGrid.DataBind();
				this.ResultPanel_Search.Visible = true;
 
				//now we need to identify if this is a call from next or first
				if (null != itemPosition)
				{
				   nextPageString =
				   string.Format(nextPageString, DataSortExpression, 
								archData.LastItem[DataSortExpression], searchData.LastItem.ID);
				}
				else
				{
					nextPageString = string.Empty;
				}
 
				if (currentPageNumber > 1)
				{
				  PreviousPageString =
				  string.Format(PreviousPageString, DataSortExpression, 
								searchData.FirstItem[DataSortExpression], searchData.FirstItem.ID);
				}
				else
				{
					PreviousPageString = string.Empty;
				}
 
				if (string.IsNullOrEmpty(nextPageString))
				{
					LinkButtonNext.Visible = false;
				}
				else
				{
					LinkButtonNext.Visible = true;
				}
 
				if (string.IsNullOrEmpty(PreviousPageString))
				{
					LinkButtonPrevious.Visible = false;
				}
				else
				{
					LinkButtonPrevious.Visible = true;
				}
 
				ViewState["Previous"] = PreviousPageString;
				ViewState["Next"] = nextPageString;
 
				string pageNumber = string.Empty;
 
				if (currentPageNumber == 1)
					pageNumber = Convert.ToString(((currentPageNumber - 1) * Convert.ToInt32(rowCount)) + 1);
				else
					pageNumber = Convert.ToString((currentPageNumber));
 
				lblPaging.Text = "Page - " + pageNumber;
 
				ctlContainerUpdatePanel.Update();
 
			}
			else
			{
				ctlContainerUpdatePanel.Update();
				this.ResultPanel_Search.Visible = false;
				this.ErrorMessageLabel.Text = "No records found";
			}
		}
		catch (Exception ex)
		{
			// manage exception
		}
	}
 
	/// <summary>
	/// This function query to sharepoint list and return SearchPaging class object
	/// which holde results and paging info
	/// </summary>
	private SearchPaging GetAllSearch(string queryXML, string pagingInfo, uint rowCount, 
									int pageOrderNumber, string folderName)
	{
		SPQuery query = new SPQuery();
		query.Query = queryXML;
		query.RowLimit = rowCount;
		if (!string.IsNullOrEmpty(pagingInfo))
		{
			SPListItemCollectionPosition position = new SPListItemCollectionPosition(pagingInfo);
			query.ListItemCollectionPosition = position;
		}
 
		query.ViewFields = string.Concat(
			"<FieldRef Name='ID' />",
				   "<FieldRef Name='Title' />");
 
		// assign folder name
		if (!String.IsNullOrEmpty(folderName))
		{
			SPFolder searchFolder = list.RootFolder.SubFolders[folderName];
			query.Folder = searchFolder;
			query.ViewAttributes = "Scope="Recursive"";
		}
 
		SPListItemCollection itemColl = list.GetItems(query);
 
		// SearchPaging is my custom class which have some get set. 
		// i have copied code of this class in bottom for your reference
 
		SearchPaging searchPaging = new SearchPaging(itemColl.GetDataTable()); // set result datatable
		searchPaging.ItemCollPosition = itemColl.ListItemCollectionPosition; // set position object
		searchPaging.FirstItem = itemColl[0]; // set first item of page
		searchPaging.LastItem = itemColl[itemColl.Count - 1]; // set last item of page
 
		return searchPaging;
	}
 
	protected void linkBtn_Search_Click(object sender, EventArgs e)
	{
		try
		{
			ViewState["Next"] = string.Empty;
			ViewState["Previous"] = string.Empty;
			ViewState["DataSortExpression"] = Constant.SearchResultColumnDocumentName;
			// get filters and store into Dictionary object
			// i store actual internal column value as key, so that i can use in my caml query
			Dictionary<string, string> searchFilters = GetSearchData();
			if (searchFilters.Count > 0)
			{
				BindListData(searchFilters, ViewState["Next"] as string, 1);
			}
		}
		catch(Exception ex)
		{
			// manage exception
		}
	}
 
	protected void LinkButtonPrevious_Click(object sender, EventArgs e)
	{
		Dictionary<string, string> searchFilters = GetSearchData();
		if (searchFilters.Count > 0)
			BindListData(searchFilters, ViewState["Previous"] as string, Convert.ToInt32(ViewState["CurrentPage"]) - 1);
	}
 
	protected void LinkButtonNext_Click(object sender, EventArgs e)
	{
		Dictionary<string, string> searchFilters = GetSearchData();
		if (searchFilters.Count > 0)
			BindListData(searchFilters, ViewState["Next"] as string, Convert.ToInt32(ViewState["CurrentPage"]) + 1);
	}       
 
}
 
public partial class SearchPaging
{
	public SPListItemCollectionPosition ItemCollPosition { get; set; }
	public SPListItem FirstItem { get; set; }
	public SPListItem LastItem { get; set; }
	private DataTable searchItems;
	public int SearchResultCount { get; set; }
	public DataTable SearchItems
	{
		get{return searchItems;}
	}
 
	public SearchPaging() { }
 
	public SearchPaging(DataTable SearchItems)
	{
		searchItems = SearchItems;
	}
}

Summary

In this i felt all things are simple. Only Paging info, list item position, first item and last item need to set properly.

Hope this helps.
Thanks!
Avinash

calendarMarch 6, 2012 · cardInfoyen · comments15 Comments
tagTags: , , , , , , , , ,  · Posted in: List, MOSS, SharePoint

15 Responses

  1. adicodes - March 14, 2012

    Article looks great, covering one of the best way to get share point list data which increase the performance
    -Adi

  2. Avinash Dad - March 14, 2012

    Glad to see that it helps you.
    Enjoy the time.
    -Avinash

  3. napster - June 7, 2012

    Hi, I’m having problem with pagination in Sharepoint. I looked into your code and copied some of your implementation. My problem is: I have 41 sample records, I need to display it per page having 20 records per page. On the last page, which I expect that I can get only 1 record. The bug is: I got 20 records including the last record which should not be the expected result. Can you help me?…Thanks.

  4. Infoyen - June 7, 2012

    Hello Napster,

    Please look at your “nextPageString” and “PreviousPageString” code?
    I suspect on this only.

    As I explain in my article:-
    // below 2 string format are good if you have orderby in your caml query.
    [csharp]
    string nextPageString = "Paged=TRUE&p_FSObjType=0&p_{0}={1}&p_ID={2}";
    string PreviousPageString = "Paged=TRUE&p_FSObjType=0&PagedPrev=TRUE&p_{0}={1}&p_ID={2}";
    [/csharp]

    //below 2 string format are good if you dont have orderby in your caml query.
    [csharp]
    string nextPageString = "Paged=TRUE&p_{0}={1}&p_ID={2}";
    string PreviousPageString = "Paged=TRUE&PagedPrev=TRUE&p_{0}={1}&p_ID={2}";
    [/csharp]

    Let me know your query type. I mean what are your filter criteria:- caml, folder filter, sorting etc..
    Based on that i may give you idea to use nextPageString and PreviousPageString..

    Also 1 quick guide for you:
    Go to sharepoint list (for which you are trying to get pagination). Then there you modify view and put your filter like order by, item limit etc…
    Then you will get 20 items per page in list. Now you click on page next and see browser query. Same click page previous and see browser query.. You will easily understand that what should be value of nextPageString and PreviousPageString..

    Thanks!
    Avinash

  5. napster - June 8, 2012

    Hi Avinash,

    Thanks for the reply. Right now, I’m having problem with what you suggested to try in the sharepoint list because from what I have observed, I can’t do a successful custom view because my doc library has folders.

    See details below:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    
    string nextPageString = "Paged=TRUE&amp;p_Created={0}&amp;p_ID={1}";
    string previousPageString = "Paged=TRUE&amp;PagedPrev=TRUE&amp;p_Created={0}&amp;p_ID={1}";
     
    private const string _reportsQuery = "{0}";
     
    query.Query = string.Format(_reportsQuery, SPContext.Current.Web.CurrentUser.Name);
    query.RowLimit = 20;
    query.ViewAttributes = "Scope='RecursiveAll'";
    if (!string.IsNullOrEmpty(pagingInfo))
    {
    SPListItemCollectionPosition position = new SPListItemCollectionPosition(pagingInfo);
    query.ListItemCollectionPosition = position;
    }

    Regards,
    Napster

  6. Infoyen - June 8, 2012

    I am not sure why its not working but 100% problem is in your nextPageString & previousPageString.

    My another suggestion:-
    Remove p_created from your string. It is to sort by created…
    Make string like this:-
    string nextPageString = “Paged=TRUE&p_FSObjType=0&p_{0}={1}&p_ID={2}”;
    string PreviousPageString = “Paged=TRUE&p_FSObjType=0&PagedPrev=TRUE&p_{0}={1}&p_ID={2}”;

    And for order; you keep in orderby caml query;
    ======================
    For example: you want to sort title:- then paging string would be format like that

    string.format(nextPageString, “Title”, listitem["Title"].ToString(), listitem["ID"].ToString());
    string.format(PreviousPageString , “Title”, listitem["Title"].ToString(), listitem["ID"].ToString());

    and caml query would be
    string orderBy = @”“;
    ======================
    Try this and i hope you will make it work.

  7. napster - June 13, 2012

    Hi Avinash,

    Thanks, your suggested solution worked. However, I still have problem with the pagination, because I also included in my implementation that the user can also select a specific page. Do you also have an implementation for this scenario? Thanks again.

    Regards,
    Napster

  8. napster - June 13, 2012

    Hi Avinash,

    I have been able to create an implementation in selecting specific page. My problem now is I noticed that when I changed the Ascending attribute of my OrderBy string to False, I encountered again the same problem with the last page having 20 reports instead of only one remaining report.

    Regards,
    Napster

  9. Infoyen - June 15, 2012

    Good that my solution works for you…

    But its wiered that ascending= false does not work.
    This one you need to dig into it and find out.

    From my side just recheck your order by query. May be test on caml query builder.
    It will be some think like below:-

    1
    2
    3
    4
    
    if (DataSortDirection == SortDirection.Ascending)
     orderBy = @"<OrderBy><FieldRef Name='Title' Ascending='True' /></OrderBy>";
      else
     orderBy = @"<OrderBy><FieldRef Name='Title' Ascending='False' /></OrderBy>";

    Thanks!
    Avinash

  10. sai - July 11, 2012

    where can I write this code ?

  11. napster - July 11, 2012

    Hi Avinash,

    I have encountered an issue regarding the pagination. In my project I have single and multiple reports. In multiple reports, all the related reports have same Created field value. I have noticed that if I go to the next page, if the nextPageString contains the Id of the middle report in the multiple reports, it skipped the other related report in the next page. I think it’s because they have same Created field value. Do you have an idea on how to resolve this? Thanks.

    Regards,
    Napster

  12. Infoyen - July 16, 2012

    Hi Napster,
    I am not sure why you are facing this issue. I do not have idea about it.
    This too depth level investigation where we can get solution if we troubleshoot, debug etc..

    Once you get answer then it would be great if you can comment here with your solution.

    Regards,
    Avinash

  13. Infoyen - July 16, 2012

    Hi Sai,

    I have already mentioned the steps in article. However see below a quick glance:-

    1. Create a user control, place your all filters and place grid there to see results.
    2. In code behind file; write your logic to get items from sharepoint list.
    3. Then bind into your grid.
    4. Now the issue is paging. Now you can use my article :)

    A) Use my logic to create prev and next link button link.
    B) Use my logic to bind your filters
    C) Use my logic to create paging info string which is constructed using PreviousPageString or nextPageString.
    D) Use my method “GetAllSearch” to get document from sharepoint list.

    Note: My blogs are not complete copy paste to get solution. You need to take logic and may be few functions which you can merge into your code to mitigate your business needs.

    Hope it helps
    Thanks & Regards,
    Avinash

  14. suresh kumar gundala - December 24, 2013

    Hi Avinash,

    Very nice article!!
    This article or code helps the user to navigate in a serial fashion. i.e., user can either move forward or backward using the buttons.
    I just want to know whether it is possible for the end user to navigate to the page of their choice.

  15. Infoyen - December 29, 2013

    As per my knowledge its possible.
    Approach-
    1. Go to sharepoint list which has content more than 100
    2. click on next page
    3. Copy browser url
    4. Understand this url. Here you will find that if you need to go to page 3 of page search then what should be url.
    5. Once you understand logic; you can build c# code to generate such url which is generated based on user page choice.

    Hope i clarify you.

    Regards,
    Avinash

Leave a Reply

Spam Protection: , required

myworldmaps infoyen