Post
Ask Question

Custom paging


Introduction

In this article, we'll see the implementation of custom paging on GridView control. The Gridview offers two paging models 1) Default paging and 2) Custom paging.

It's easy to configure default paging by only just enabling allowpaing property to true but less efficient in performance. The simple paging model fetches all records for each pageindexchanging event and displays the appropirate subset of all returned records.

For example, you have 5000 records in your database's table. You want to display 100 records per page. Now, when page first loaded it will fetch 5000, but the GridView will display the first set of 100 records. Next, when user navigates to the second page again 5000 records will be fetched and display the second set of 100 records.

In custom paging, we have to do some more work, rather just enabling allowpaging property to true. We have to develop custom logic to fetch selected records for the specific page index instead fetching all records and display subset of them.

There are so many websites that explains the advantages and performance comparison over default paging. There are so many websites too, that provide example of custom paging but mostly I found those examples developed using datasource controls.

Setting up database and tables in SQL Server 2005

  1. Create new database EmployeeDB SQL Sever 2005.
    2.  
  2. Create new table Employee in EmployeeDB.
     
  3. Create table fields same as below.

    4. Id Int
    FName nvarchar(50)
    MName nvarchar(50)
    LName nvarchar(50)
    BirthDate datetime
    MaritalStatus char(1)
    Gender char(1)
    Designation nvarchar(50)
    Mobile nvarchar(15)

     

  4. Insert 20 to 30 records.
     
  5. Create new stored procedure that reterives the subset of records according to current page index.

    CREATE PROCEDURE dbo.USP_GET_EMPLOYEES

    (

    @P_CurrentPageIndex INT,

          @P_PageSize INT,

                @P_TotalRecords INT OUTPUT

    )

     

    AS

    BEGIN

          SET NOCOUNT ON

     

          DECLARE @StartRowIndex INT

          DECLARE @MaxRowIndex INT

     

          SET @MaxRowIndex = @P_PageSize * @P_CurrentPageIndex

          SET @StartRowIndex = (@MaxRowIndex - @P_PageSize) + 1

     

          SELECT * FROM

          (

                SELECT ROW_NUMBER() OVER(ORDER BY Id) AS RowNum,

    Id, FName, MName, LName, BirthDate, MaritalStatus,

    Gender, Designation, Mobile FROM Employee.

    ) AS DerivedTable

          WHERE RowNum BETWEEN @StartRowIndex AND @MaxRowIndex
         
          --Return total no of records available as an output parameter
      
          SELECT @P_TotalRecords = COUNT(*) FROM EMPLOYEE

     

    END

Web.config

1. Set appropriate conncetion string in web.config file.
Eg.

<connectionStrings>

    <add name="EmpDB"

connectionString="Data Source=.;

Initial Catalog=EmployeeDB;

Integrated Security=True"

providerName="System.Data.SqlClient"/>

</connectionStrings>

The aspx page

 <%@ 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>Employee details (Custom paging)</title>
 </head>
<body>
    <form id="form1" runat="server">
    <table width="100%" style="font-family: Verdana; font-size: 12px">
        <tr align="center">
            <td>
                <asp:GridView ID="grdEmployee" runat="server" AutoGenerateColumns="False" BackColor="White"
                    BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="4" Font-Names="verdana"
                    Font-Size="12px" ForeColor="Black" GridLines="Both" Width="80%">
                    <FooterStyle BackColor="#CCCC99" ForeColor="Black" />
                    <PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
                    <SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
                    <Columns>
                        <asp:BoundField DataField="RowNum" HeaderText="Serial No" />
                        <asp:TemplateField HeaderText="Full name">
                            <ItemTemplate>
                                <asp:Label ID="lblFname" runat="Server" Text='<%# Eval("FName") %>'></asp:Label>
                                <asp:Label ID="lblMname" runat="server" Text='<%# Eval("MName") %>'></asp:Label>
                                <asp:Label ID="lblLname" runat="server" Text='<%# Eval("LName") %>'></asp:Label>
                            </ItemTemplate>
                            <ItemStyle HorizontalAlign="Left" Wrap="False" />
                        </asp:TemplateField>
                        <asp:BoundField DataField="BirthDate" HeaderText="Birthday" DataFormatString="{0:MM/dd/yyyy}"
                            HtmlEncode="False" />
                        <asp:TemplateField HeaderText="Marital">
                            <ItemStyle HorizontalAlign="Left" />
                            <ItemTemplate>
                                <asp:Label ID="lblMarital" runat="Server" Text='<%# Eval("MaritalStatus") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Gender">
                            <ItemStyle HorizontalAlign="Left" />
                            <ItemTemplate>
                                <asp:Label ID="lblGender" runat="Server" Text='<%# Eval("Gender") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:BoundField DataField="Designation" HeaderText="Designation">
                            <ItemStyle HorizontalAlign="Left" />
                        </asp:BoundField>
                        <asp:BoundField DataField="Mobile" HeaderText="Contact">
                            <ItemStyle HorizontalAlign="Left" />
                        </asp:BoundField>
                    </Columns>
                </asp:GridView>
            </td>
        </tr>
        <tr>
            <td align="center">
                <asp:LinkButton ID="lnkFirstPage" runat="server" Text="First"></asp:LinkButton>
                &nbsp;||&nbsp;
                <asp:LinkButton ID="lnkPrevious" runat="server" Text="Previous"></asp:LinkButton>
                &nbsp;||&nbsp;
                <asp:LinkButton ID="lnkNext" runat="server" Text="Next"></asp:LinkButton>
                &nbsp;||&nbsp;
                <asp:LinkButton ID="lnkLastPage" runat="server" Text="Last"></asp:LinkButton>
            </td>
        </tr>
    </table>
    </form>
 </body>
</html>


The code behind (aspx.cs)

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    #region [INITIALIZATION]

    SqlConnection con;
    SqlCommand cmd;
    SqlDataAdapter da;
    DataTable dt;

    string conStr = ConfigurationManager.ConnectionStrings["EmpDB"].ConnectionString;
    int totalRecords;

    #endregion

    #region [PAGE EVENTS]

    protected void Page_Init(object sender, EventArgs e)
    {
        EventHandler clickHandler = new EventHandler(PagingButtonClick);
        lnkFirstPage.Click += clickHandler;
        lnkLastPage.Click += clickHandler;
        lnkNext.Click += clickHandler;
        lnkPrevious.Click += clickHandler;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            lnkFirstPage.Enabled = false;       // Disable First and last button
            lnkPrevious.Enabled = false;        // on first time page load

            ViewState["pageindex"] = 1;         // Manages current page index.

            LoadGrid(Convert.ToInt16(ViewState["pageindex"]));                         //

            SetViewState();
        }
    }

    #endregion

    #region [USER FUNCTIONS]

    /// <summary>
    /// Makes connection to the database
    /// and bind the gridview
    /// </summary>
    /// <param name="currentPageIndex"></param>
    private void LoadGrid(int currentPageIndex)
    {
        con = new SqlConnection(conStr);
        cmd = new SqlCommand("USP_GET_EMPLOYEES", con);
        cmd.Parameters.Add("@P_CurrentPageIndex", SqlDbType.Int).Value = currentPageIndex;
        cmd.Parameters.Add("@P_PageSize", SqlDbType.Int).Value = grdEmployee.PageSize;
        cmd.Parameters.Add("@P_TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
        cmd.CommandType = CommandType.StoredProcedure;
 
        try
        {
            if (con != null && con.State == ConnectionState.Closed)
            {
                con.Open();
 
                da = new SqlDataAdapter();
                dt = new DataTable();
 
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
        }
        finally
        {
            if (con != null && con.State != ConnectionState.Closed)
            {
                con.Close();
            }
        }

        grdEmployee.DataSource = dt;
        grdEmployee.DataBind();
    }

    /// <summary>
    /// set the viewstate for
    /// 1.  Total records
    /// 2.  Total page
    /// </summary>
    private void SetViewState()
    {
        int _totalRecords = (int) cmd.Parameters["@P_TotalRecords"].Value;
        int _pageSize = grdEmployee.PageSize;
        int _totalPages = 0;

        // Make sure totalrecords are morethan gridview's paging size
        if (_totalRecords > _pageSize)
        {
            // Get the total no of pages need to be created
            if ((_totalRecords % _pageSize) == 0)
            {
                _totalPages = _totalRecords / _pageSize;
            }
            else
            {
                _totalPages = (_totalRecords / _pageSize) + 1;
            }
        }
        ViewState["totalrecord"] = _totalRecords;
        ViewState["totalpage"] = _totalPages;
    }
    /// <summary>
    /// Single custom button click event
    /// for paging buttons
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void PagingButtonClick(object sender, EventArgs e)
    {
        if (sender is LinkButton)
        {
            LinkButton clickedButton = (LinkButton)sender;
             #region switch case for button click
            switch (clickedButton.Text)
            {
                case "Next":
                    lnkFirstPage.Enabled = true;
                    lnkPrevious.Enabled = true;
                    ViewState["pageindex"] = Convert.ToInt32(ViewState["pageindex"]) + 1;
                    if (Convert.ToInt32(ViewState["totalpage"]) == Convert.ToInt32(ViewState["pageindex"]))
                    {
                        lnkNext.Enabled = false;
                        lnkLastPage.Enabled = false;
                    }
                    break;
 
                case "Previous":
                    lnkNext.Enabled = true;
                    lnkLastPage.Enabled = true;
                    ViewState["pageindex"] = Convert.ToInt32(ViewState["pageindex"]) - 1;
                    if (Convert.ToInt32(ViewState["pageindex"]) == 1)
                    {
                        lnkNext.Enabled = true;
                        lnkLastPage.Enabled = true;
                        lnkPrevious.Enabled = false;
                        lnkFirstPage.Enabled = false;
                    }
                    break;
 
                case "First":
                    if (Convert.ToInt32(ViewState["pageindex"] = 1) == 1)
                    {
                        lnkNext.Enabled = true;
                        lnkLastPage.Enabled = true;
                        lnkPrevious.Enabled = false;
                        lnkFirstPage.Enabled = false;
                    }
                    break;

                case "Last":
                    lnkFirstPage.Enabled = true;
                    lnkPrevious.Enabled = true;
                    ViewState["pageindex"] = Convert.ToInt32(ViewState["totalpage"]);
                    if (Convert.ToInt32(ViewState["totalpage"]) == Convert.ToInt32(ViewState["pageindex"]))
                    {
                        lnkNext.Enabled = false;
                        lnkLastPage.Enabled = false;
                    }
                    break;
            }
             #endregion
            LoadGrid(Convert.ToInt32(ViewState["pageindex"]));
        }
    }
    #endregion

}

Snapshot

snap1.gif

Snapshot 1 the first page

snap2.gif

Snapshot 2 Retrieved data for first page


Similar Articles