Search This Blog

Thursday, March 10, 2011

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 

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

18 comments:

  1. Great Post.
    From what I understand, there are two buttons: Next and Previous.

    How would one do about retrieving the Total Number of Pages?

    How would one go about being able to navigate to a specific page?

    ReplyDelete
  2. Hi RalphZero,

    Sharepoint object model doesn't provide such kind of paging out of the box. If you need to implement total records and page number then you need to write a ugly code to fetch all records and custom paging using pageddataset.

    ReplyDelete
  3. The XmlNode that is returned from GetListItems has a pointer to the next page ONLY.
    There is no pointer to the previous page or page(X).

    At one point we were using GetList and GetListItems to create and populate an ADO.Net DataTable with the entire List. Once we have the DataTable, we bind it to a GridView control on and ASP.Net web page. We gave the client the ability to Filter, Sort and Update the data. This is a much nicer interface than anything SharePoint has.

    However, when the List became too large, GetListItems would throw the Soap "System.OutOfMemoryException".

    We had one of our subs build us a C# Class Library that is basically a wrapper around GetList, GetListItems and UpdateListItems.

    This Wrapper works in the following manner: When the class is instantiated, the ListURL, ListName, RowsPerPage, Query and ViewFields need to be set. GetSPList will return the first page as an Ado.Net DataTable. It also has the PageNext, PagePrevious and GotoPage(X) methods that return an Ado.Net DataTable. Other read only properties include CurrentPage and TotalPages.
    If ViewFields, Query or RowsPerPage are reset, it will recalculate TotalPages. GetListItems is another read only property that holds the GetListItems XmlNode. This has worked perfectly for us and solved our problems.

    We were told the module was developed for SharePoint Lists. They did not develop this for Document Libraries. We were told it is written in C# and was unit tested using WSS 3.0, MOSS 2007, IE 8.0 and Windows Server 2003 32bit.

    We purchased only the Assembly. Not the code. If you want, I can put you in touch with these people.

    ReplyDelete
  4. Hi DiningRoom,

    If you see my example above, we have implemented next and previous paging from native SharePoint object model programming. I completely understand the wrapper class that you purchased which I am sure have used paged dataset that we already explored.

    Thanks for the good comment

    ReplyDelete
  5. Check out my blog.

    http://dr-sharepoint.blogspot.com/

    ReplyDelete
  6. DiningRoom,
    The blog you are referring to is:
    http://jmlistpaginationtool.blogspot.com/

    ReplyDelete
  7. The Blog URL has been changed:
    http://jmlistpaginationtool.blogspot.com/

    ReplyDelete
  8. Excellent work ... BUT...
    Well It works, except if your list exceed the threshold and you want to change the Orde by to some other field, then the sort by does not work. It throws an exception.
    I tried creating new Indexes, but I still get an Threshold exception as soon as I modify the in the SPQuery.

    Thanks anyway
    Patrick Imboden

    ReplyDelete
  9. All,

    Changing the sort will break this solution because of the following reason:
    The NextPageString and PreviousPageString are using only the ID column.
    The Fields in the NextPageString and PreviousPageString must match the Sort. For example, if your list is sorted by LastName, FirstName and MiddleName, then NextPageString and PreviousPageString must include LastName, FirstName, MiddleName and ID.

    Using SharePoint, modify one of your views. Set the sort so the View is being sorted by 2 columns other than ID. Set the ItemLimit to 10.

    Navigate to the next page of your view and look at the URL.
    Navigate to the previous page of your view and look at the URL.

    My solution has everything.
    PageCount, RowCount, PageNext, PagePrevious, PageGoto(X), etc.

    Check out my blog.
    http://jmlistpaginationtool.blogspot.com/

    ReplyDelete
  10. Hi Joseph,
    This code works with column sorting and its not breaking if you provide the different column name also. In object model we need to provide start Id in the spquery for sorting to work with all our requests and if no other column is specified then it works on id sort.

    If you are facing any specific error then please let me know.

    ReplyDelete
  11. Hi Nitin,

    Great Post indeed. Good walk through about implementing custom paging. However, i have one query. What if i do not want to use the sorting functionality? My CAML Query already sorts based on two fields and i would like to show the result of this caml query as such in a datagrid. How do i build the Page Info? Is sorting information mandatory in paging info. Looks like it is as i am not able to get paging working without this sorting information in page info.

    Hope i make my requirement clear.

    ReplyDelete
  12. if you dont want sorting then just remove the &p_" + sortColumn + "={1}" from your query. It should work fine and will be sorted on the default column i.e. ID

    ReplyDelete
  13. Hi, your work was really useful. I've changed some thing to an approach like this to make it more reusable.

    http://hveiras.wordpress.com/2011/11/07/listpagert-using-splistitemcollectionposition/

    ReplyDelete
  14. Gr8 post ..One correction..Need to pass pager information as on of the arg on next previous click.
    protected void LinkButtonPrevious_Click(object sender, EventArgs e)
    {
    LoadData(Convert.ToInt32(ViewState["CurrentPage"]) - 1, ViewState["Previous"] as string);
    }

    protected void LinkButtonNext_Click(object sender, EventArgs e)
    {
    LoadData(Convert.ToInt32(ViewState["CurrentPage"]) + 1, ViewState["Next"] as string);
    }

    ReplyDelete
  15. Nice post Nitin, thank you.
    Thanks for sorting out the 'Previous' click issue , Devarpi.

    THiru

    ReplyDelete
  16. Thanks but there is errors in Previous Button
    i made some changes and post the full smaple that is working very well on
    blog

    http://ajamaeen.blogspot.com/2013/02/paging-with-splistitemcollectionposition.html#!/2013/02/paging-with-splistitemcollectionposition.html

    ReplyDelete
  17. I'm a little bit late, but this method is working if I use GridView SortExpression and not these dropdown?

    Because trying to sort with SortExpression returns a sorting different each time.

    ReplyDelete
  18. it's a great information. I spent a lot of time to find something like this. Thank you very much!!!

    ReplyDelete