Create data paging using linq

As everyone probably know and have tried, is the ASP.NET integrated paging system in the data sources, but it's not always it fits your solution. That's why i want to show you how i make simple data paging using query.

Getting data access

To start off we need to get access to the database, to do this i use LINQ by creating a .dbml file, within this example contains products from the sample database Northwind.

I'm creating a simple method to get the data out:

public static List<Product> GetProducts()
{
   PagingDataContext db = new PagingDataContext();
   
   return (from p in db.Products
           select p).ToList();
}

I will use the skip and take in linq to make the paging:

.Skip(skip).Take(take); 

Create paging class

Now we need the class that can convert pageNumber and pageSize to skip and take, in my case I've made a Paging object that can contain the take, skip and totalPages integers:

public static Paging GetTakeAndSkip(int pageNumber, int pageSize)
{
   int take = pageSize;
   int skip = (pageNumber - 1) * pageSize;

   //(totalProducts / pageSize)
   int totalPages = Int32.Parse(Math.Ceiling(decimal.Parse(ProductService.GetProducts().Count().ToString()) / decimal.Parse(pageSize.ToString())).ToString());

return new Paging(take, skip, totalPages);
}

Putting it all together

This is just databound to a gridView to give you an example, and the page size is defined in the query. You can make a drobdownlist or so to handle how many to be displayed, but that's not made in this example:

int pageNumber;
int pageSize;
Paging pagingItem;

protected void Page_Load(object sender, EventArgs e)
{
   //Request the queries, to make it more secure
   //put IsNullOrEmpty and tryParse arround.
   pageNumber = Int32.Parse(Request.QueryString["page"]);
   pageSize = Int32.Parse(Request.QueryString["size"]);

   //Requesting the Method in the paging class
   pagingItem = Paging.GetTakeAndSkip(pageNumber, pageSize);

   //This keeps track of the number and size
   LitPagePlace.Text = string.Format("{0}/{1}", pageNumber, pagingItem.TotalPages);

   //Using the skip and take on our method to get products
   GridProducts.DataSource = ProductService.GetProducts().Skip(pagingItem.Skip).Take(pagingItem.Take);
   GridProducts.DataBind();
}

protected void LinkNext_Click(object sender, EventArgs e)
{
   //This will make it stop paging,
   //when the last page is reached
   if (pagingItem.TotalPages > pageNumber)
   {
      //replacing and redirecting to the next page
   Response.Redirect(Request.RawUrl.Replace(string.Format("page={0}", pageNumber),

   string.Format("page={0}", pageNumber + 1)));
   }
}

protected void LinkPrev_Click(object sender, EventArgs e)
{
   //This will make it stop paging,
   //when the first page is reached
   if (1 < pageNumber)
   {
      //replacing and redirecting to the previous page
      Response.Redirect(Request.RawUrl.Replace(string.Format("page={0}", pageNumber),

      string.Format("page={0}", pageNumber - 1)));
   }
}