To do custom paging in SharePoint list, just we should calculate the position of last item (ListItemCollectionPosition) and pass it to the query. The steps to do paging are as following:
1. Get the previous page last SPList Item. And then generate the paging filter string as following:
SPList assetList = web.Lists[listName];
SPQuery query = new SPQuery();
query.Query = camel;
query.RowLimit = (uint)(pageItemCount * (pageIndex - 1));
assetDetailsItems = assetList.GetItems(query);
int previousPageLastItemPosition = assetDetailsItems.Count - 1;
StringBuilder columnBuilder = new StringBuilder();
if (columnNames != null)
{
foreach (string column in columnNames)
{
string columnValue = (assetDetailsItems[previousPageLastItemPosition][column] == null) ? string.Empty : assetDetailsItems[previousPageLastItemPosition][column].ToString();
columnBuilder.Append("&p_" + column + "=" + columnValue);
}
}
2. Create SPListItemCollectionPosition object based on columns details which retieved in step 1.
SPListItemCollectionPosition objSPListColPos = new SPListItemCollectionPosition("Paged=TRUE"
+ columnBuilder.ToString());
query.ListItemCollectionPosition = objSPListColPos;
3. Pass the SPQuery to the GetItems function, which will fetch items starting from the position that we created in step 2.
assetList.GetItems(query);
The following function does the paging for you, and in each calling, it will calculate first the total items count and then generate the last position for the page index parameter and finally get the items by using GetItems Function.
public static DataTable GetItems(string webSite, string listName, string camel,string viewFields, string[] columnNames, int pageIndex, int pageItemCount,out int ItemsCount)
{
SPListItemCollection assetDetailsItems = null;
using (SPSite site = new SPSite(webSite))
{
using (SPWeb web = site.OpenWeb())
{
// Get the list
SPList assetList = web.Lists[listName];
SPQuery query = new SPQuery();
query.Query = camel;
if (!string.IsNullOrEmpty(viewFields))
{
query.ViewAttributes = "Scope='RecursiveAll'";
query.ViewFields = viewFields;
}
// first calculate the totla items count
assetDetailsItems = assetList.GetItems(query);
ItemsCount = assetDetailsItems.Count;
// Retrieve the items for the last page. E.g.: If request is for 5th page and item count/page=10 then row limit will retrieve 40 items and 40th item will be used to get the column details which will be used for pagination.
query = new SPQuery();
query.Query = camel;
if (!string.IsNullOrEmpty(viewFields))
{
query.ViewAttributes = "Scope='RecursiveAll'";
query.ViewFields = viewFields;
}
query.RowLimit = (uint)(pageItemCount * (pageIndex - 1));
assetDetailsItems = assetList.GetItems(query);
// Get the previous page last item position. Use this item to retrieve the column details which will be used for pagination.
int previousPageLastItemPosition = assetDetailsItems.Count - 1;
// generate the last position
StringBuilder columnBuilder = new StringBuilder();
if (columnNames != null)
{
foreach (string column in columnNames)
{
// Make sure that if the field value is mandatory and if you are passing it as NULL then SPList.GetItems will throw exception.
string columnValue = (assetDetailsItems[previousPageLastItemPosition][column] == null) ? string.Empty : assetDetailsItems[previousPageLastItemPosition][column].ToString();
// Check if the value is null or empty
columnBuilder.Append("&p_" + column + "=" + columnValue);
}
}
query = new SPQuery();
query.Query = camel;
if (!string.IsNullOrEmpty(viewFields))
{
query.ViewAttributes = "Scope='RecursiveAll'";
query.ViewFields = viewFields;
}
// Create Paging Information which will be used for retrieving paging based items
SPListItemCollectionPosition objSPListColPos = new SPListItemCollectionPosition("Paged=TRUE"
+ columnBuilder.ToString());
query.RowLimit = uint.Parse(pageItemCount.ToString());
// check if the page index = 1,that mean no need for list position
if (pageIndex != 1)
query.ListItemCollectionPosition = objSPListColPos;
// Execute the CAML query.
assetDetailsItems = assetList.GetItems(query);
}
}
return assetDetailsItems.GetDataTable();
}
[Note: we can increase the performance of the code by storing the last calculated position in view state and don't generate it each time, but in this case we can do paging not by numbers but with next and previous functionality]
