Pagination using SPQuery

Let's see how we can achieve pagination in SPQuery. We need to use ListItemCollectionPosition property to achieve pagination along with some logic.

The below code shows how many pages has to be shown for pagination. i.e. If you have 100 items in a list, and rowlimt=50 then the total no: of pages should be 2.

        static void Main()

        {

            SPListItemCollection items = GetListItems(3, 2);//rowlimit,Pagenumber

            foreach (SPListItem item in items)

            {

                Console.WriteLine(item["Title"]);

            }

            Console.ReadLine();

        }

        static SPListItemCollection GetListItems(int rowlimit, int pageNo)

        {

            string SiteCollectionUrl = "http://SiteURL/";

            using (SPSite site = new SPSite(SiteCollectionUrl))

            {

                using (SPWeb oWebsiteRoot = site.OpenWeb())

                {

                    SPList oList = oWebsiteRoot.Lists["list_name"];

                    int TotalListItems = oList.ItemCount;

                    int iPageCount = (int)Math.Ceiling(TotalListItems / (decimal)rowlimit);

                    Console.WriteLine("Total No of Pages: " + iPageCount); //No of pages to be shown

                    Console.WriteLine("Printing items from pageNo:" + pageNo.ToString());

                    SPQuery query = new SPQuery();

                    query.RowLimit = (uint)rowlimit;

                    query.Query = "<OrderBy Override=\"TRUE\"><FieldRef Name=\"FileLeafRef\" /></OrderBy>";

                    int index = 1;

                    SPListItemCollection items;

                    do

                    {

                        items = oList.GetItems(query);

                        if (index == pageNo)

                            break;

                        query.ListItemCollectionPosition = items.ListItemCollectionPosition;

                        index++;

                    }

                    while (query.ListItemCollectionPosition != null);

                    return items;

                }

            }

        }