GridView Paging with Skip() and Take()

Paging is one of the most commonly used features of ASP.NET GridView control. When you bind a GridView with Data Source Controls such as SqlDataSource you can easily enable paging by setting the AllowPaging property to true and then by setting the PageSize property. This default way, however, comes with its own price. The default paging has a drawback that it fetches all the records from the data source and then discards the ones other than the current page. That means if your table contains 100 records and you wish to display 10 on a page, the GridView is going to fetch all 100 and then discard 90 that are not required.

To overcome this problem developers used custom paging by setting the AllowCustomPaging property to true and then by handling GridView events such as PageIndexChanging and PageIndexChanged. Although this worked fine, it was bit tedious to code. ASP.NET 4.5 Model Binding along with Entity Framework and LINQ to Entities can simplify your job of implementing custom paging. Let's see how.

Begin by creating a new project in Visual Studio and add a new web form to it. Add a new ADO.NET Entity Framework Data Model to the project and configure it against Customers table of Northwind database. The following figure shows how the Customer class looks like in the designer.

Then drag and drop a GridView control on the web form and set its AllowPaging property to true. Also, set PageSize property to 10. More importantly set the SelectMethod property of the GridView to SelectCustomers. You will write the SelectCustomers() method shortly. This method returns a collection of Customer objects to the GridView at run time. Go in the code behind of the web form and add the SelectCustomers() method as shown below:

public List<Customer> SelectCustomers(int startRowIndex,
int maximumRows,out int totalRowCount)
{
  var data = (from c in db.Customers
              orderby c.CustomerID
              select c).Skip(startRowIndex).Take(maximumRows);
  totalRowCount = db.Customers.Count();
  return data.ToList();
}

Notice the SelectCustomers() method carefully. It takes three important parameters that are required for paging. These parameter names must be the same as shown in the code above. The startRowIndex parameter tells you what is the start index for the records. The maximumRows indicates the PageSize so that your code knows how many records are to be fetched. Finally the output parameter totalRowCount governs how many paging LinkButtons are rendered in the footer of the grid.

The SelectCustomers() method then constructs a LINQ to Entities query. Notice how Skip() and Take() methods are used while retrieving the records. The Skip() method skips the specified number of rows in the result set whereas Take() method fetches the specified number of records. The startRowIndex is passed as the parameter to Skip() method and maximumRows is passed as the parameter of Take() method. This way you return only the required number of Customer records from the SelectCustomers() method instead of all the customers.

The following figure shows how the resultant GridView looks like:

That's it for now. Keep coding!