CAML Query Execution using Pagination (for custom columns)
Below approach is used when user wants to perform the pagination based on the custom SharePoint columns(columns other than the SharePoint inbuilt "ID" column ). In order to make the pagination work, we need to first fetch the last page item and get the columns details which takes part in pagination. SPList items can be retrieved in pages and user can specify the custom column which will be used for sorting the data. Algorithm to retrieve the list items in pages is explained below:
1. Retrieve the previous page last SPList item. Use this splist item to get the column information from the splist item. These column details will be used for pagination.
2. Create the SPListItemCollectionPosition object using the column details fetched in step 1.
3. Set the SPQuery.ListItemCollectionPosition property with the value fetched in step 2.
4. Pass SPQuery object in SPList.GetListItems function i.e. assetList.GetItems(query). GetItems method returns the paged list items.
Note: Most important part of pagination is forming the string to retrieve the data in pages.
ListItemCollectionPositionNext=" Paged=TRUE&p_ID=#id#&CustomColumnInternalName=#colValue#&PageFirstRow=#row#" where
p_ID is SharePoint List "ID" Column Name, #row# is the row number e.g. Let's consider page size=5 and total item count=10 and user wants to retrieve page "5" then #row# value will be "41",
#id# is SharePoint list Item "ID" column Value,
CustomColumnInternalName is any custom/inbuilt column name on which you are doing item sorting and #colValue# is the column value. Note: User has to specify the column name in "ListItemCollectionPositionNext" string only when CustomColumnInternalName is being used for sorting the data. In short, if CAML query contains any custom column/inbuild column for sorting, then that column should be specified in the ListItemCollectionPositionNext string.
See the below sample code for details:
/// <summary>
/// Function to retrieve the splist items in pages.
/// PageIndex and PageCount contains values. E.g.: If PageIndex = 5 and PageItemCount = 10 then it is assumed that CAML will contain atleast 50 items exist.
/// Paging is based on the ID field and the fields which are passing through the columnNames string collection
/// </summary>
/// <param name="listName">SharePoint List Name - Used for querying</param>
/// <param name="viewName">SharePoint List View Name</param>
/// <param name="caml">CAML - Caml query</param>
/// <param name="columnNames">List Column Name-it is used for sorting the data based on the solumn names. Make sure that column names used are system column names
/// Do not use display column names</param>
/// <param name="pageIndex">Page Size - List Item count to be fetched from the page</param>
/// <param name="pageItemCount">Page Count - Page Number</param>
/// <param name="inputParams">Input Parameters - To be replaced in CAML query which contains the CAML query where clause condition values.</param>
/// <returns>Returns collection of SharePoint List Item Collection</returns>
public static SPListItemCollection ExecuteCAMLToRetrieveListItemsInPages(string listName, string viewName, string caml, string[] columnNames, int pageIndex, int pageItemCount, params object[] inputParams)
{
SPListItemCollection assetDetailsItems = null;
string siteURL = "https://localhost";
using (SPSite kSite = new SPSite(siteURL))
{
using (SPWeb sharePointWeb = kSite.OpenWeb())
{
// Check if the SharePoint web object is not null or not.
if (sharePointWeb == null)
{
throw new SharePointAgentException("Site could not be created.");
}
// Get the Asset List
SPList assetList = sharePointWeb.Lists[listName];
// Get the SPLIst View
SPView listView = assetList.Views[viewName];
SPQuery query = new SPQuery(listView);
query.Query = caml;
// 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.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;
StringBuilder columnBuilder = new StringBuilder();
// Form the paging filter query string
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(listView);
query.Query = caml;
// 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());
query.ListItemCollectionPosition = objSPListColPos;
// Execute the CAML query.
assetDetailsItems = assetList.GetItems(query);
}
}
return assetDetailsItems;
}