Monthly Archives: November 2011

Paginating SharePoint List Items

Paginating 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 my trial to 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 with 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 begining 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 selelct 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;
    }
}

Grouping XML elements using XSLT version 1.0

Suppose you have an XML like:

<Rows>
  <Row Category="Sweet" Value="Chocolate"/>
  <Row Category="Salty" Value="Potato Chips"/>
  <Row Category="Salty" Value="Pop Corn"/>
  <Row Category="Sour" Value="Lemon"/>
  <Row Category="Sweet" Value="Biscuits"/>
  <Row Category="Salty" Value="Fries"/>
</Rows>

And you want to have these rows grouped by the ‘Category’ attribute like:

Salty
Fries
Pop Corn
Potato Chips

Sour
Lemon

Sweet
Biscuits
Chocolate

You can use Muenchian grouping just like the following:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
  <!-- First, define a key that uses our element or attribute we need to group by. In our case it's the Category attribute. -->
  <xsl:key name="food-by-Category" match="Row" use="@Category" />
  <xsl:template match="Rows">
  <!-- Now, we need to iterate on the Categories, This can be done by iterating on the first XML Row item for each Category. We will do that by create a node set for the current Row item (.) and the first item in the Key of the Current item  Category. Then we check the count to see is it 1 or 2.. If 1, So we've the same item in the node set; We have a new Category.-->
    <xsl:for-each select="Row[count(. | key('food-by-Category', @Category)[1]) = 1]">
      <!-- Sort by the Category -->
      <xsl:sort select="@Category" />
      <xsl:value-of select="@Category" />
      <hr />
      <!-- Now loop on the items of this Category, We get them from the Key we defined -->
      <xsl:for-each select="key('food-by-Category', @Category)">
        <!-- Sort by the item Value -->
        <xsl:sort select="@Value" />
        <xsl:value-of select="@Value" />
        <br/>      </xsl:for-each>
      <br/>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>