ASP.net - Custom paging in gridview by using LINQ to SQL

Paging gridviews are the most sought after functionalities in most of the web applications.When it comes to performance, for datacentric applications its very important to implement the custom paging mechanism instead of the default paging for gridviews. This is because the page becomes heavily loaded when thousands of records are fetched at a strech from the
database.
To overcome this, we must implement a "Custom paging" mechanism which brings page of data requested by the user.

What is LINQ to SQL classes?
LINQ to SQL classes that are mapped to database tables and views are called entity classes. The entity class maps to a record, whereas the individual properties of an entity class map to the individual columns that make up a record. Create entity classes that are based on database tables or views by dragging tables or views from Server Explorer/Database Explorer onto the Object Relational Designer (O/R Designer). The O/R Designer generates the classes and applies the specific LINQ to SQL attributes to enable LINQ to SQL functionality.
In short, LINQ to SQL provides an Object-Relational Mapping (O/R Mapping) which maps objects of different type system.

Let see how to create custom paging in gridview by using LINQ to SQL.

Step 1
Download northwind database from the following link.
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

Step 2
Attach a northwind database into MS-SQL server.

Step 3
Create a Web application and give solution name as SolCustompagingLINQ.

Step 4
In this example we have to bind employee data from northwind database into a gridview. add a gridview on page it is look like this

<asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>  

        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <asp:GridView ID="GvEmployee" runat="server" AllowPaging="True" CellPadding="4" 
                    EnableModelValidation="True" ForeColor="#333333" GridLines="None" PageSize="5">
                    <AlternatingRowStyle BackColor="White" />
                    <EditRowStyle BackColor="#7C6F57" />
                    <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#E3EAEB" />
                    <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
                </asp:GridView> 
 
            </ContentTemplate> 
        </asp:UpdatePanel>   

Step 5
Add a connectionstring on web.config file,it is look like this

<connectionStrings>
        <add name="ConStr" connectionString="Data Source=shree\shree;Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>

Step 6
Add app_code folder in the solution and add a new folder inside the app_code folder and give folder name as ORD,it is look like this

Click on image for better view

Step 7

Add a Linq to Sql class,Select the ORD folder,right click on Add new Item,select LINQ to SQL classes from installed Visual Studio templates and name it NorthwindDC and click on add button,it is look like this

Click on image for better view

Step 8

Open a O/R Designer by double click on NorthwindDC.dbml,it is look like this

Click on image for better view

Click on image for better view

Visual stdio provides an object-relational mapping designer,called the O/R Designer which allows you to visually design the object to database mapping.

Step 9

Create a Employee object.

in this example we have to work with employees table from the northwind database,create a employee object that will use LINQ to SQL to map to this table.go to the server explorer,select northwind database,go to the tables and select Employees table,it is look like this

Click on image for better view

Drag and drop Employee table from Server explorer onto the design surface of the O/R Designer,it is look like this

Click on image for better view

Step 10

Create a Employee class in app_code folder for retriving an employee data from database,it is look like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;

public class Employee
{
    #region Methods

   /// <summary>
    /// Get the Employee data from northwind database
   /// </summary>
    /// <param name="StartRowIndex">StartRowIndex provided by ObjectDataSource Control</param>
    /// <param name="MaximumRows">MaximumRows provided by ObjectDataSource Control</param>
   /// <returns>IList</returns>
    public IList GetEmployeesData(int StartRowIndex, int MaximumRows)
    {
        try
        {
            // Create a object of DataContext and specify the connectionstring in datacontext constructor
            // The NorthwindDCDataContext object is an object of type of DataContext
            // This object works with the connectionstring and connect to the database for any required operation

            ORD.NorthwindDCDataContext DC = new ORD.NorthwindDCDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);

            // SELECT LINQ Query
            var Query = (from Q in DC.Employees
                              select new
                              {
                                FirstName=Q.FirstName,
                                LastName=Q.LastName,
                                Title=Q.Title,
                                City=Q.City,
                                Country=Q.Country 
                              }
                           ).Skip(StartRowIndex).Take(MaximumRows);

            return Query.ToList(); // return Query
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

   /// <summary>
   /// Count total rows of employees table.
   /// </summary>
   /// <returns>int</returns>
    public int GetEmpolyeesDataCount()
    {
        try
        {
            // Create a object of DataContext and specify the connectionstring in datacontext constructor
            // The NorthwindDCDataContext object is an object of type of DataContext
            // This object works with the connectionstring and connect to the database for any required operation

            ORD.NorthwindDCDataContext DC = new ORD.NorthwindDCDataContext(System.Configuration.ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);

            // Count Query
            int TotalCount = (from Q in DC.Employees
                              select Q
                                ).Count();

            return TotalCount; // return Count
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    #endregion
}

GetEmployeesData
The LINQ query that fetches the employee records.We use Skip and Take operator to fetch the records that belongs to the current page.

GetEmpolyeesDataCount
The GetEmpolyeesDataCount() method will get the number of employees available in the table using LINQ query to construct the page numbers.

Step 11
Now we can configure the ObjectDataSource control with these methods.it is look like this

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
                 EnablePaging="true" TypeName="Employee"
                 SelectMethod="GetEmployeesData" SelectCountMethod="GetEmpolyeesDataCount"
                 StartRowIndexParameterName="StartRowIndex" MaximumRowsParameterName="MaximumRows"      
                >
                
                </asp:ObjectDataSource> 

Configuring ObjectDataSource Control for Custom Paging

1.Type Name - This property needs to be configured with the class that has the implementation of SelectCountMethod and SelectMethod.

2.EnablePaging - This property accepts a Boolean to enable paging with ObjectDataSource control.

3.SelectCountMethod - We need to specify the name of the method that can fetch the total number of records available in the database.

4.SelectMethod - This property will accept the name of the method that fetches actual database record.

5.StartRowIndexParameterName - This property will accept the start row index of the record to fetch from database.

6.MaximumRowsParameterName - This property will accept the maximum number of rows that can be fetched at one time. This will be equivalent to page size.


Step 12
Bind the employee data in gridview.it is look like this

protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (IsPostBack == false)
            {
                BindEmployeeData(); 
            }
        }
        catch (Exception)
        { 
        }
    }

    #region Methods
    /// <summary>
    /// Bind the employees data into gridview
    /// </summary>
    private void BindEmployeeData()
    {
        try
        {
            GvEmployee.DataSourceID = ObjectDataSource1.ID;
            GvEmployee.DataBind();  
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    #endregion

Run the project.

Download
Download Source Code


Full Code

1. .aspx code

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server">
        </asp:ScriptManager>  

        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <asp:GridView ID="GvEmployee" runat="server" AllowPaging="True" CellPadding="4" 
                    EnableModelValidation="True" ForeColor="#333333" GridLines="None" PageSize="5">
                    <AlternatingRowStyle BackColor="White" />
                    <EditRowStyle BackColor="#7C6F57" />
                    <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#E3EAEB" />
                    <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
                </asp:GridView> 

                <asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
                 EnablePaging="true" TypeName="Employee"
                 SelectMethod="GetEmployeesData" SelectCountMethod="GetEmpolyeesDataCount"
                 StartRowIndexParameterName="StartRowIndex" MaximumRowsParameterName="MaximumRows"      
                >
                
                </asp:ObjectDataSource>  
            </ContentTemplate> 
        </asp:UpdatePanel>   
    </div>
    </form>
</body>
</html>

2. Code behind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (IsPostBack == false)
            {
                BindEmployeeData(); 
            }
        }
        catch (Exception)
        { 
        }
    }

    #region Methods
    /// <summary>
    /// Bind the employees data into gridview
    /// </summary>
    private void BindEmployeeData()
    {
        try
        {
            GvEmployee.DataSourceID = ObjectDataSource1.ID;
            GvEmployee.DataBind();  
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);  
        }
    }

    #endregion
}