Using LINQ to Create a Pager for SQL Data in C#

Using LINQ to SQL, we can make use of the built-in methods to page the database data a lot easier than with using SQL alone. LINQ to SQL can make it extremely easy for us to create pages from our data source using just two methods - Skip and Take.Skip allows us to skip a certain number of records, and Take allows us to select a certain number of records.
In this tutorial, we will be creating a SQL database and adding a LINQ to SQL Class that Visual Studio creates to represent our database. We will then extend the class to support paging of the data, using the methods mentioned above.Let's start by creating our database. In this example, we will use one table named tblEmployees with three columns - id, name, position.Once the database is set up, we will add some sample data - we will need at least 5 records to make use of the paging feature.
Once we have our database set up and have added data to it, we then need to create a representation of our database using a LINQ to SQL Class. Right-click your project in the Solution Explorer, and goto Add ASP.NET Folder > App_Code. Now right-click the App_Code folder and choose Add New Item.. LINQ to SQL Classes. This will bring up the Object Relationship Designer. All we need to do here is drag the tables we will be working with into the Designer, from the Server Explorer, and then Save. This will allow Visual Studio to create a representation of our database. For this example, we will name it Employees.dbml
Now we will create an extension of this class by again right-clicking the App_Code folder and choose Add New Item.. Class. We will also name this Employees and change the public class to public partial class. We may need to also add extra assembly references; we will be using the System.Collections.Generic, System.Data.Linq and System.Linq in particular.We are going to extend this class by providing methods to select the data in pages. Our first method will select all the data:
public static IEnumerable Select()
{
EmployeesDataContext db = new EmployeesDataContext();
return db.tblEmployees;
}
Notice the EmployeesDataContext refers to our LINQ to SQL class.Next, we add a method to move between the pages of the data:
public static IEnumerable SelectPage(int startRowIndex, int maximumRows)
{
return Select().Skip(startRowIndex).Take(maximumRows);
}
This method will be called when a new page is requested through PostBack. The GridView's paging links will provide the variables required for this method.Finally, we create a method that will get the number of records in the database:
public static int SelectCount()
{
return Select().Count();
}
The entire class extension will look something like this:
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Data.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections.Generic;
///

/// Extension class for Employees.dbml///

public partial class Employees{
public static IEnumerable Select()
{
EmployeesDataContext db = new EmployeesDataContext();return db.tblEmployees;
}
public static IEnumerable SelectPage(int startRowIndex, int maximumRows)
{
return Select().Skip(startRowIndex).Take(maximumRows);
}
public static int SelectCount()
{
return Select().Count();
}
}
Now we are done with the class, and can implement the functionality into our ASPX page. To make this work, we will need to use a GridView control and an ObjectDataSource:

Because we are using VS.NET 2008, we can simply add AJAX Functionality to our web application using a ScriptManager and UpdatePanel.In order to implement paging, we need to set the EnablePaging attributes on both of our controls. We also set the Method attributes of the ObjectDataSource to reflect those we created in our partial class - note that the TypeName refers to our class name.
NOTE: We can change the PageSize to set the number of items on each page.