In this article I will explain with an example, how to implement Custom Paging in ASP.Net GridView control using C# and VB.Net.
Custom Paging in GridView will be implemented with the help of Stored Procedure in ASP.Net using C# and VB.Net.
 
 
Database
Here I am making use of Microsoft’s Northwind Database. You can download it from here.
 
 
Stored Procedure for Paging
The following Stored Procedure makes use of ROW_NUMBER function to fetch records Page Wise from SQL Server Database Table.
The PageIndex and PageSize values are passed as parameter and the Stored Procedure returns the Total records of the Table using the RecordCount Output parameter.
Note: For more details, please refer my article Paging in SQL Server Stored Procedure with Total Row Count.
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE GetCustomersPageWise
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [CustomerID] ASC
      )AS RowNumber
      ,[CustomerID]
      ,[CompanyName]
      ,[ContactName]
      INTO #Results
      FROM [Customers]
     
      SELECT @RecordCount = COUNT(*)
      FROM #Results
           
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
     
      DROP TABLE #Results
END
GO
 
 

HTML Markup
The following HTML Markup consists of a GridView, a DropDownList for selecting the Page Size and a Repeater which will be used for populating the pager.
The DropDownList is populated with some static values which will be later used for setting the Page Size of the GridView control.
PageSize:
<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="true" OnSelectedIndexChanged="PageSize_Changed">
    <asp:ListItem Text="10" Value="10" />
    <asp:ListItem Text="25" Value="25" />
    <asp:ListItem Text="50" Value="50" />
</asp:DropDownList>
<hr />
<asp:GridView ID="GridView1" 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>
<br />
<asp:Repeater ID="rptPager" runat="server">
    <ItemTemplate>
        <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
            Enabled='<%# Eval("Enabled") %>' OnClick="Page_Changed"></asp:LinkButton>
    </ItemTemplate>
</asp:Repeater>
 
 
Namespaces
You will need to import the following namespaces.
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
 
VB.Net
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
 
 
Binding the GridView
Inside the Page Load event, the GetCustomersPageWise function is called with PageIndex parameter value as 1.
Inside the function, the Stored Procedure is executed and it fetches the records based on PageIndex and PageSize (fetched from the DropDownList) from Customers Table of the Northwind database using DataReader.
Finally, the DataReader is used to populate the GridView.
After execution of DataReader, the Total Record Count value is fetched using the RecordCount Output parameter.
The Total Record Count value is passed to the PopulatePager method along with the PageIndex.
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.GetCustomersPageWise(1);
    }
}
 
private void GetCustomersPageWise(int pageIndex)
{
    string constring = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("GetCustomersPageWise", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", int.Parse(ddlPageSize.SelectedValue));
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
            con.Open();
            IDataReader idr = cmd.ExecuteReader();
            GridView1.DataSource = idr;
            GridView1.DataBind();
            idr.Close();
            con.Close();
            int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
            this.PopulatePager(recordCount, pageIndex);
        }
    }
}
 
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.GetCustomersPageWise(1)
    End If
End Sub
 
Private Sub GetCustomersPageWise(ByVal pageIndex As Integer)
    Dim constring As String = ConfigurationManager.ConnectionStrings("constring").ConnectionString
    Using con As SqlConnection = New SqlConnection(constring)
        Using cmd As SqlCommand = New SqlCommand("GetCustomersPageWise", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
            cmd.Parameters.AddWithValue("@PageSize", Integer.Parse(ddlPageSize.SelectedValue))
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4)
            cmd.Parameters("@RecordCount").Direction = ParameterDirection.Output
            con.Open()
            Dim idr As IDataReader = cmd.ExecuteReader()
            GridView1.DataSource = idr
            GridView1.DataBind()
            idr.Close()
            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
The PopulatePager method accepts the count of the total records present in the Table and the current PageIndex.
Then some calculations are done to find the first and the last page of the pager and the 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)
{
    double dblPageCount = (double)((decimal)recordCount / decimal.Parse(ddlPageSize.SelectedValue));
    int pageCount = (int)Math.Ceiling(dblPageCount);
    List<ListItem> pages = new List<ListItem>();
    if (pageCount > 0)
    {
        pages.Add(new ListItem("First", "1", currentPage > 1));
        for (int i = 1; i <= pageCount; i++)
        {
            pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
        }
        pages.Add(new ListItem("Last", pageCount.ToString(), currentPage < pageCount));
    }
    rptPager.DataSource = pages;
    rptPager.DataBind();
}
 
VB.Net
Private Sub PopulatePager(ByVal recordCount As Integer, ByVal currentPage As Integer)
    Dim dblPageCount As Double = CType((CType(recordCount, Decimal) / Decimal.Parse(ddlPageSize.SelectedValue)), Double)
    Dim pageCount As Integer = CType(Math.Ceiling(dblPageCount), Integer)
    Dim pages As New List(Of ListItem)
    If (pageCount > 0) Then
        pages.Add(New ListItem("First", "1", (currentPage > 1)))
        Dim i As Integer = 1
        Do While (i <= pageCount)
            pages.Add(New ListItem(i.ToString, i.ToString, (i <> currentPage)))
            i = (i + 1)
        Loop
        pages.Add(New ListItem("Last", pageCount.ToString, (currentPage < pageCount)))
    End If
    rptPager.DataSource = pages
    rptPager.DataBind()
End Sub
 
 
Binding the GridView on DropDownList Change
Following event handler is executed when the Page Size DropDownList is changed.
This method simply calls the GetCustomersPageWise method.
C#
protected void PageSize_Changed(object sender, EventArgs e)
{
    this.GetCustomersPageWise(1);
}
 
VB.Net
Protected Sub PageSize_Changed(ByVal sender As ObjectByVal e As EventArgs)
    Me.GetCustomersPageWise(1)
End Sub
 
 
Binding the GridView on Pager LinkButton Click
Following event handler is executed when the Page Number LinkButton is clicked.
Inside this event handler, the PageIndex is referenced using LinkButton CommandArgument property.
Finally, GetCustomersPageWise method is called by passing the PageIndex value to it.
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(ByVal sender As ObjectByVal e As EventArgs)
    Dim pageIndex As Integer = Integer.Parse(CType(sender, LinkButton).CommandArgument)
    Me.GetCustomersPageWise(pageIndex)
End Sub
 
 
Screenshot
Custom Paging in ASP.Net GridView using SQL Server Stored Procedure
 
 
Demo
 
 
Downloads