In this article I’ll explain how to implement Custom Paging in ASP.Net GridView using Stored Procedure in MySql database using C# and VB.Net.
 
Using MySQL with ASP.Net
This portion is extensively covered in my article Use and connect to MySQL Database in ASP.Net Application using MySQLConnector.
 
Database
Here I am making use of Customers Table of the Microsoft’s Northwind Database in MySql.
Custom Paging in ASP.Net GridView using MySql Stored Procedure
 
Note: The instructions to download and install the database are provided in the below article.
 
 
Need for Custom Pagination
Now the question arises why there’s need to take so much pain when ASP.Net GridView control has in built paging mechanism.
The answer is that ASP.Net GridView fetches all the records and then displays one page from the fetched records. Thus for example if your table has 1000 records and you need to display only 50 records per page, GridView will fetch all 1000 records discard the 950 records and display the 50 records based on the page index selected by the users.
Thus the above approach is quite inefficient in terms of both bandwidth and performance. With custom pagination we will fetch records per page based on the page index. Thus if our table has 1000 records and we need to display only 50 records per page, then we will fetch only 50 records based on page index. Thus this will boost the performance of the application.
 
Database Pagination in MySql Stored Procedure
MySql database does not have function similar to the SQL Server’s ROW_NUMBER function that allows us to give Row Numbers (Indexes) to the records that we select from the table.
In MySql, a variable can be used in SELECT statement with its value incremented with one. This way we get a functionality similar to that of the SQL Server’s ROW_NUMBER function.
The fetched records are inserted into a Temporary Table which is processed as follows.
1. The Total Count of the records present in the Table are fetched and assigned to the RecordCount Output parameter.
2. Finally records are fetched from the Temporary Table by matching the RowNumber column with the Start and End records count calculated based on the PageIndex and the PageSize passed as parameters.
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetCustomers_Pager`(
   _PageIndex INT
   ,_PageSize INT
   ,OUT _RecordCount INT
)
BEGIN
       SET @RowNumber:=0;
 
       CREATE TEMPORARY TABLE Results
       SELECT @RowNumber:=@RowNumber+1 RowNumber
              ,CustomerID
              ,ContactName
              ,CompanyName
       FROM Customers;
 
       SET _RecordCount =(SELECT COUNT(*) FROM Results);
 
       SELECT * FROM Results
       WHERE RowNumber BETWEEN(_PageIndex -1) * _PageSize + 1 AND(((_PageIndex -1) * _PageSize + 1) + _PageSize) - 1;
 
       DROP TEMPORARY TABLE Results;
END$$
DELIMITER ;
 
 
HTML Markup
The HTML markup is quite simple it has a GridView for populating the Customer records and a Repeater control which will be used for populating the pager control.
GridView for populating records
The GridView control consists of three BoundField columns.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField HeaderText="CustomerId" DataField="CustomerID" />
        <asp:BoundField HeaderText="ContactName" DataField="ContactName" />
        <asp:BoundField HeaderText="CompanyName" DataField="CompanyName" />
    </Columns>
</asp:GridView>
 
Repeater for pager
Below is the Repeater control for populating the Pager, since the syntax of the inline code is different in C# and VB.Net, I given their respective HTML Markups. The HTML Markup has some C# and VB.Net code to swap the CSS style classes for the Current Page and the remaining pages so that the Current Page is viewed distinguished from others.
C#
<asp:Repeater ID="rptPager" runat="server">
    <ItemTemplate>
        <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
            CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
            OnClick="Page_Changed" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
    </ItemTemplate>
</asp:Repeater>
 
VB.Net
<asp:Repeater ID="rptPager" runat="server">
    <ItemTemplate>
        <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
            CssClass='<%# If(Convert.ToBoolean(Eval("Enabled")), "page_enabled", "page_disabled")%>'
            OnClick="Page_Changed" OnClientClick='<%# If(Not Convert.ToBoolean(Eval("Enabled")), "return false;", "") %>'></asp:LinkButton>
    </ItemTemplate>
</asp:Repeater>
 
 
Namespaces
You will need to import the following namespaces
C#
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
 
VB.Net
Imports System.Data
Imports System.Configuration
Imports MySql.Data.MySqlClient
 
 
Binding the GridView with Customized Paged Data
In the Page Load event of the page, a call is made to the method GetCustomersPageWise and it executes the stored procedure discussed earlier and binds the records to the GridView control.
Since the PageSize value will be required throughout, I have created private variable for it and set its value to 6.
The Record Count of the records is fetched from the Output Parameter and is passed to the PopulatePager method along with the PageIndex.
C#
private int PageSize = 10;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.GetCustomersPageWise(1);
    }
}
 
private void GetCustomersPageWise(int pageIndex)
{
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (MySqlConnection con = new MySqlConnection(constring))
    {
        using (MySqlCommand cmd = new MySqlCommand("GetCustomers_Pager", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("_PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("_PageSize", PageSize);
            cmd.Parameters.Add("_RecordCount", MySqlDbType.Int32, 4);
            cmd.Parameters["_RecordCount"].Direction = ParameterDirection.Output;
            con.Open();
            using (IDataReader idr = cmd.ExecuteReader())
            {
                gvCustomers.DataSource = idr;
                gvCustomers.DataBind();
            }
            con.Close();
            int recordCount = Convert.ToInt32(cmd.Parameters["_RecordCount"].Value);
            this.PopulatePager(recordCount, pageIndex);
        }
    }
}
 
VB.Net
Private PageSize As Integer = 10
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Me.GetCustomersPageWise(1)
    End If
End Sub
 
Private Sub GetCustomersPageWise(pageIndex As Integer)
    Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New MySqlConnection(constring)
        Using cmd As New MySqlCommand("GetCustomers_Pager", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("_PageIndex", pageIndex)
            cmd.Parameters.AddWithValue("_PageSize", PageSize)
            cmd.Parameters.Add("_RecordCount", MySqlDbType.Int32, 4)
            cmd.Parameters("_RecordCount").Direction = ParameterDirection.Output
            con.Open()
            Using idr As IDataReader = cmd.ExecuteReader()
                gvCustomers.DataSource = idr
                gvCustomers.DataBind()
            End Using
            con.Close()
            Dim recordCount As Integer = Convert.ToInt32(cmd.Parameters("_RecordCount").Value)
            Me.PopulatePager(recordCount, pageIndex)
        End Using
    End Using
End Sub
 
 
Populating the Pager control
In the last line of the above method we are calling the method described below to populate the pager Repeater control based on the record count that we fetch from the database. Here we pass the total of the records present in the database table and the Current Page that was fetched and displayed in the Repeater. Then some calculations are done to find the first and the last page of the pager and using a loop a List of Pages are populated and finally are bound to the Repeater control for populating the Pager.
C#
private void PopulatePager(int recordCount, int currentPage)
{
    List<ListItem> pages = new List<ListItem>();
    int startIndex, endIndex;
    int pagerSpan = 5;
 
    //Calculate the Start and End Index of pages to be displayed.
    double dblPageCount = (double)((decimal)recordCount / Convert.ToDecimal(PageSize));
    int pageCount = (int)Math.Ceiling(dblPageCount);
    startIndex = currentPage > 1 && currentPage + pagerSpan - 1 < pagerSpan ? currentPage : 1;
    endIndex = pageCount > pagerSpan ? pagerSpan : pageCount;
    if (currentPage > pagerSpan % 2)
    {
        if (currentPage == 2)
        {
            endIndex = 5;
        }
        else
        {
            endIndex = currentPage + 2;
        }
    }
    else
    {
        endIndex = (pagerSpan - currentPage) + 1;
    }
 
    if (endIndex - (pagerSpan - 1) > startIndex)
    {
        startIndex = endIndex - (pagerSpan - 1);
    }
 
    if (endIndex > pageCount)
    {
        endIndex = pageCount;
        startIndex = ((endIndex - pagerSpan) + 1) > 0 ? (endIndex - pagerSpan) + 1 : 1;
    }
 
    //Add the First Page Button.
    if (currentPage > 1)
    {
        pages.Add(new ListItem("First", "1"));
    }
 
    //Add the Previous Button.
    if (currentPage > 1)
    {
        pages.Add(new ListItem("<<", (currentPage - 1).ToString()));
    }
 
    for (int i = startIndex; i <= endIndex; i++)
    {
        pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
    }
 
    //Add the Next Button.
    if (currentPage < pageCount)
    {
        pages.Add(new ListItem(">>", (currentPage + 1).ToString()));
    }
 
    //Add the Last Button.
    if (currentPage != pageCount)
    {
        pages.Add(new ListItem("Last", pageCount.ToString()));
    }
    rptPager.DataSource = pages;
    rptPager.DataBind();
}
 
VB.Net
Private Sub PopulatePager(recordCount As Integer, currentPage As Integer)
    Dim pages As New List(Of ListItem)()
    Dim startIndex As Integer, endIndex As Integer
    Dim pagerSpan As Integer = 5
 
    'Calculate the Start and End Index of pages to be displayed.
    Dim dblPageCount As Double = CDbl(CDec(recordCount) / Convert.ToDecimal(PageSize))
    Dim pageCount As Integer = CInt(Math.Ceiling(dblPageCount))
    startIndex = If(currentPage > 1 AndAlso currentPage + pagerSpan - 1 < pagerSpan, currentPage, 1)
    endIndex = If(pageCount > pagerSpan, pagerSpan, pageCount)
    If currentPage > pagerSpan Mod 2 Then
        If currentPage = 2 Then
            endIndex = 5
        Else
            endIndex = currentPage + 2
        End If
    Else
        endIndex = (pagerSpan - currentPage) + 1
    End If
 
    If endIndex - (pagerSpan - 1) > startIndex Then
        startIndex = endIndex - (pagerSpan - 1)
    End If
 
    If endIndex > pageCount Then
        endIndex = pageCount
        startIndex = If(((endIndex - pagerSpan) + 1) > 0, (endIndex - pagerSpan) + 1, 1)
    End If
 
    'Add the First Page Button.
    If currentPage > 1 Then
        pages.Add(New ListItem("First", "1"))
    End If
 
    'Add the Previous Button.
    If currentPage > 1 Then
        pages.Add(New ListItem("<<", (currentPage - 1).ToString()))
    End If
 
    For i As Integer = startIndex To endIndex
        pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
    Next
 
    'Add the Next Button.
    If currentPage < pageCount Then
        pages.Add(New ListItem(">>", (currentPage + 1).ToString()))
    End If
 
    'Add the Last Button.
    If currentPage <> pageCount Then
        pages.Add(New ListItem("Last", pageCount.ToString()))
    End If
    rptPager.DataSource = pages
    rptPager.DataBind()
End Sub
 
Finally the below event is executed when the page number LinkButton is clicked. This event makes a database call to get new set of records based on the PageIndex fetched from the CommandArgument property of the clicked LinkButton.
C#
protected void Page_Changed(object sender, EventArgs e)
{
    int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    this.GetCustomersPageWise(pageIndex);
}
 
VB.Net
Protected Sub Page_Changed(sender As Object, e As EventArgs)
    Dim pageIndex As Integer = Integer.Parse(TryCast(sender, LinkButton).CommandArgument)
    Me.GetCustomersPageWise(pageIndex)
End Sub
 
 
Screenshot
Custom Paging in ASP.Net GridView using MySql Stored Procedure
 
 
Demo
View Demo
 
 
Downloads
Download Code