Step by Step Guide to implement Paging and sorting with SPQuery and SPListItemCollectionPosition

This article describes the step by step implementation of paging and sorting with the help of SPQuery and SPListItemCollectionPosition  in Sharepoint

Step One :

Create a user control or a webpart that will have datagrid for displaying the paged and sorted results , next and previous link buttons , label to show the current page values and drop down list for sort column / sort order.

I have used visual webpart and added these lines for the controls shown in this image

Sort By  :

<asp:DropDownList ID="DropDownListSortColumns" runat="server"

    onselectedindexchanged="DropDownListSortColumns_SelectedIndexChanged" AutoPostBack=true>

    <asp:ListItem>ID</asp:ListItem>

    <asp:ListItem>Title</asp:ListItem>

    <asp:ListItem>Created</asp:ListItem>

    <asp:ListItem>Modified</asp:ListItem>

</asp:DropDownList>

<asp:DropDownList ID="DropDownListSortOrder" runat="server"

    onselectedindexchanged="DropDownListSortOrder_SelectedIndexChanged" AutoPostBack=true>

    <asp:ListItem Value="True">Ascending</asp:ListItem>

    <asp:ListItem Value="False">Descending</asp:ListItem>

</asp:DropDownList>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns=true class="style1">

</asp:GridView>

<table style="float:right; width:100px">

    <tr>

        <td>

            <asp:LinkButton ID="LinkButtonPrevious" runat="server"

                onclick="LinkButtonPrevious_Click"><<</asp:LinkButton>

        </td>

        <td>

           <asp:Label ID="LabelPaging" runat="server" Text="Label"></asp:Label></td>

        <td>

            <asp:LinkButton ID="LinkButtonNext" runat="server"

                onclick="LinkButtonNext_Click">>></asp:LinkButton>

        </td>

    </tr>

</table>

Step Two:

Now we need to handle the data load events , sort column change events and the paging buttons events . For that we need to write event handlers

 protected void Page_Load(object sender, EventArgs e)

        {

            if (!Page.IsPostBack)

            {

                LoadData(1);

            }

        }

        private void LoadData(int currentPage)

        {

            ViewState["CurrentPage"] = currentPage;

            FillData(ViewState["Next"] as string, DropDownListSortColumns.SelectedValue,Convert.ToBoolean( DropDownListSortOrder.SelectedItem.Value));

        }

        private void FillData(string pagingInfo, string sortColumn, bool sortAscending)

        {

            int currentPage = Convert.ToInt32(ViewState["CurrentPage"]);

            uint rowCount = 5;

            string columnValue;

            string nextPageString = "Paged=TRUE&p_ID={0}&p_" + sortColumn + "={1}";

            string PreviousPageString = "Paged=TRUE&PagedPrev=TRUE&p_ID={0}&p_" + sortColumn + "={1}";

            SPListItemCollection collection;

            //first make a call to fetch the desired result set

            //here is the actual call to the dal function

            collection = DAL.GetTestItems(sortColumn, sortAscending, pagingInfo, rowCount);

            DataTable objDataTable = collection.GetDataTable();

            GridView1.DataSource = objDataTable;

            GridView1.DataBind();

            //now we need to identify if this is a call from next or first

            if (null != collection.ListItemCollectionPosition)

            {

                if (collection.Fields[sortColumn].Type == SPFieldType.DateTime)

                {

                    columnValue = SPEncode.UrlEncode( Convert.ToDateTime(collection[collection.Count - 1][sortColumn]).ToUniversalTime().ToString("yyyyMMdd HH:mm:ss"));

                }

                else

                {

                    columnValue = SPEncode.UrlEncode( Convert.ToString(collection[collection.Count - 1][sortColumn]));

                }

                nextPageString = string.Format(nextPageString, collection[collection.Count - 1].ID, columnValue);

            }

            else

            {

                nextPageString = string.Empty;

            }

            if (currentPage > 1)

            {

                if (collection.Fields[sortColumn].Type == SPFieldType.DateTime)

                {

                    columnValue = SPEncode.UrlEncode(Convert.ToDateTime(collection[0][sortColumn]).ToUniversalTime().ToString("yyyyMMdd HH:mm:ss"));

                }

                else

                {

                    columnValue =SPEncode.UrlEncode(  Convert.ToString(collection[0][sortColumn]));

                }

                PreviousPageString = string.Format(PreviousPageString, collection[0].ID, columnValue);

            }

            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;

            LabelPaging.Text = ((currentPage - 1) * rowCount) + 1 + " - " + currentPage * rowCount;

        }

        protected void LinkButtonPrevious_Click(object sender, EventArgs e)

        {

            LoadData(Convert.ToInt32(ViewState["CurrentPage"]) - 1);

        }

        protected void LinkButtonNext_Click(object sender, EventArgs e)

        {

            LoadData(Convert.ToInt32(ViewState["CurrentPage"]) + 1);

        }

        protected void DropDownListSortColumns_SelectedIndexChanged(object sender, EventArgs e)

        {

            ViewState.Remove("Previous");

            ViewState.Remove("Next");

            LoadData(1);

        }

        protected void DropDownListSortOrder_SelectedIndexChanged(object sender, EventArgs e)

        {

            ViewState.Remove("Previous");

            ViewState.Remove("Next");

            LoadData(1);

        }

Step 3

Now the last step is to add the DAL class for this solution to complete

public class DAL

    {

        public static SPListItemCollection GetTestItems(string sortBy, bool sortAssending, string pagingInfo, uint rowLimit)

        {

          

            SPWeb objWeb = SPContext.Current.Web;

            SPListItemCollection collection;

            SPQuery objQuery = new SPQuery();

            objQuery.RowLimit = rowLimit;

            objQuery.Query = "<OrderBy><FieldRef Name='" + sortBy + "' Ascending='" + sortAssending + "' /></OrderBy>";

            objQuery.ViewFields = "<FieldRef Name='Title' />";

            if (!string.IsNullOrEmpty(pagingInfo))

            {

                SPListItemCollectionPosition position = new SPListItemCollectionPosition(pagingInfo);

                objQuery.ListItemCollectionPosition = position;

            }

            collection = objWeb.Lists["Test"].GetItems(objQuery);

            return collection;

        }

    }

Now in the above code I have hardcoded the library name but we can get this name from the calling function.

This is a fully functional example and the source code can be downloaded for 

http://min.us/mvj5TGr

All you need to do is to just create a custom list name "Test" and load it with lot of data.