Paging SharePoint List Items

Paging SharePoint list items is not a straight forward task. You have to do some plumbing to make it work, especially if you have sorting and filtering applied to the query you want to get your items with.

This is a trial that, I think, will make the task easier:

SPPagedListItemsRetriever is a class that we need to instantiate by specifying the SPList we need to work with, and SPQuery we will execute.

SPPagedListItemsRetriever pagedItemsRetriever = new SPPagedListItemsRetriever(list, query);

Then, we have 2 public methods:

public SPListItem[] GetItems(int? startIndex, int? maxRowsCount);
public int GetTotalItemsCount();

The drawback of the used technique in this implementation, is that it always get the items from the beginning!

So if you are asking to get the items starting from item 200 and page size 10, it will get the first 199 items and throw them away, then begin getting the required items from 200 to 210.

Here is the complete post of the code:

/// <summary> 
/// Retrieves paginated items of SPList with the specified SPQuery. 
/// </summary> 
public class SPPagedListItemsRetriever
{ 
  private SPQuery _query;
  private SPList _list;
  private const int MaxRowLimit = 2000;
  private static SPListItemCollectionPosition _emptySPListItemCollectionPosition = 
  new SPListItemCollectionPosition(string.Empty); 

  /// <summary>
  /// Constructs a new instance of SPPagedListItemsRetriever 
  /// </summary> 
  /// <param name="list" />The list to get the items from
  /// <param name="query" />The query by which the items should be retrieved
  public SPPagedListItemsRetriever(SPList list, SPQuery query) 
  { 
    _list = list; 
    _query = query;
  }
  
  /// <summary> 
  /// Get the items of the list with the specified query 
  /// beginning from a specified startIndex and with maxRowsCount (PageSize) 
  /// </summary> 
  /// <param name="startIndex" /> 
  /// <param name="maxRowsCount" /> 
  /// <returns></returns>
  public SPListItem[] GetItems(int? startIndex, int? maxRowsCount)
  {
    SPListItemCollectionPosition listItemCollectionPosition = null; 
    uint actualStartIndex = startIndex.HasValue ? (uint)startIndex.Value : 0;
    //If we need items beginning from a specific index (greater that 0, the first one) 
    //Create a dummy query to begin getting the items 
    //from the first one (0) till we reach the specified startIndex 
    if (actualStartIndex > 0)
    {
      SPQuery dummyQuery = new SPQuery();
      //Change the ViewFields returned from this dummy query to minimal, 
      //actually we dont need these items so selelct the ID only 
      //to minimize the view fields
      dummyQuery.ViewFields = "<fieldref name='ID'>"; 
      dummyQuery.Query = _query.Query;
      
      if (null != _query.Folder)
        dummyQuery.Folder = _query.Folder; 
      
      int gotDummyItems = 0;
      do
      { 
        //Minimize the number of items not to exceed 
        //the recommended 2000 MaxRowLimit for SPQuery
        dummyQuery.RowLimit = 
        Math.Min((uint)(actualStartIndex - gotDummyItems), MaxRowLimit); 
        if (null == listItemCollectionPosition)
          listItemCollectionPosition = _emptySPListItemCollectionPosition;
        
        dummyQuery.ListItemCollectionPosition = listItemCollectionPosition;
        SPListItemCollection items = 
        _list.GetItems(dummyQuery); gotDummyItems += items.Count; 
        listItemCollectionPosition = items.ListItemCollectionPosition;
      }
      while (gotDummyItems < actualStartIndex && 
                  listItemCollectionPosition != null);
    } 
    
    //Now we will get the actual items we need 
    SPQuery query = new SPQuery();
    query.Query = _query.Query;
    if (null != _query.Folder)
      query.Folder = _query.Folder;
    query.ViewFields = _query.ViewFields;
    List<splistitem> returnedItemsList = new List<splistitem>();
    uint actualMaxRowCount = maxRowsCount.HasValue ? 
    (uint)maxRowsCount.Value : (uint)_list.ItemCount; 

    do
    { 
      //Minimize the number of items not to exceed 
      //the recommended 2000 MaxRowLimit for SPQuery
      query.RowLimit = Math.Min(actualMaxRowCount, MaxRowLimit); 
      if (null == listItemCollectionPosition)
        listItemCollectionPosition = _emptySPListItemCollectionPosition;
      query.ListItemCollectionPosition = listItemCollectionPosition;
      SPListItemCollection listItems = _list.GetItems(query);
      returnedItemsList.AddRange(listItems.Cast<splistitem>().Select(i=>i));
      listItemCollectionPosition = listItems.ListItemCollectionPosition;
    }
    while(returnedItemsList.Count < actualMaxRowCount && 
    listItemCollectionPosition != null); 
     
    return returnedItemsList.ToArray();
  } 

  /// <summary>
  /// Gets the total items count using the specified query
  /// </summary>
  /// <returns></returns>
  public int GetTotalItemsCount()
  {
    SPQuery query = new SPQuery();
    //Change the ViewFields returned from this dummy query to minimal, 
    //actually we dont need these items so select the ID only 
    //to minimize the view fields
    query.ViewFields = "<fieldref name='ID'>";
    query.Query = _query.Query;
    SPFolder folder = _query.Folder;
    if (null != folder)
      query.Folder = folder;
    return _list.GetItems(query).Count; 
  } 
}