How to easily implement ASP.NET GridView Custom Paging

Last Updated: June 6, 2021

ASP.NET GridView now supports custom paging too. By using Custom Paging your GridView will never slow down as it only fetches the records needed for the current page only. The GridView Custom Paging supports the developers to make their application much faster and light weight too.

You don’t need controls like GridView to implement Custom Paging. You can do it by using just a HTML table, see this tutorial – Custom Paging in ASP.NET without using controls like GridView and Repeater.

GridView Paging VS GridView Custom Paging

Previously in GridView Paging, it fetches all the records from the database then takes the current page records while discarding the rest. This approach is slow when the number of records are large, and makes the application slower.

Consider this fact if your database has 1000 records and GridView’s page size is 10, then for every page it fetches 1000 record and take only 10 records for the current page. The remaining 990 records are discarded.

To solve this problem, GridView Custom Paging is used where only the relevant records of a page are fetched. So here, for every page only 10 records are fetches making the whole procedure efficient and super fast.

Procedure to use Custom Paging in GridView

If you have used GridView in your code then you can update the code with GridView Custom Paging. I have given the procedure to help you accomplish it, follow it:

1. Add the Custom Paging Stored procedure in your database. The work of this stored procedure is to fetch only the records of the current page for the GridView. This stored procedure is given at How to implement Custom Paging with SQL Server using row_number() method.
2. Add AllowCustomPaging=”true” & AllowPaging=”True” to the GridView code in your .aspx page
Example:

<asp:GridView ID="gridView" runat="server" AllowCustomPaging="true" 
AutoGenerateColumns="false" AllowPaging="True"
OnPageIndexChanging="gridView_PageIndexChanging">
    <Columns>
    ...... 
    </Columns>
</asp:GridView>

3. Set VirtualItemCount property of GridView to the total number of records in your data source.
Example:

gridView.VirtualItemCount = totalRecords;

Note – ‘totalRecords’ is an int variable. It contains the count of all the records in all the pages of the GridView.

4. Set the DataSource property of GridView to the DataTable which contains all the records of the current page. Then call the DataBind() method.

Example:

gridView.DataSource = myDataTable;
gridView.DataBind();

5. Create PageIndexChanging event of the GridView.

Example:

protected void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gridView.PageIndex = e.NewPageIndex;
    Search(e.NewPageIndex + 1);
}

Note – I have called the Search() function whose work is to bind the next page’s records to the GridView.

6. Full Page Coding is given below

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        gridView.PageIndex = 0;
        Search(1);
    }
}
 
protected void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gridView.PageIndex = e.NewPageIndex;
    Search(e.NewPageIndex + 1);
}
 
protected void Search(int pageNo)
{
    gridView.PageSize=10;
    Int totalRecords;
    DataTable myDataTable = new DataTable();
     
    //Call the Custom Paging Stored Procedure to fetch the records of 
    //the page (value contained by pageNo). The Stored procedure should 
    //return the total number of records. Set the total number of 
    //records to the totalRecords variable.
 
    gridView.VirtualItemCount = totalRecords;
    gridView.DataSource = myDataTable;
    gridView.DataBind();        
}

Example – Binding Northwind Database Products Table

Let us show you how to bind Northwind Database Products Table to a GridView using Custom Paging. I will set GridView’s pages size as 5.

This table has 77 records in total so there will be 16 pages in total.

binding nothwind database products table

First Create the Custom Paging Stored Procedure that will take 2 parameters (pageNo and pageSize), and fetch the records of that page.

Create PROCEDURE [dbo].[sp_GetProductByCustomPaging]  
    @PageNo  INT,
    @pageSize    INT   
AS           
BEGIN
    DECLARE @qry  NVARCHAR(1000);   
    SET @qry='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ProductID DESC) AS ''RowNum'',* FROM Products WHERE 1=1) a WHERE a.RowNum BETWEEN ('+CAST(@pageNo AS NVARCHAR(5))+'-1)*'+CAST(@pageSize AS NVARCHAR(5))+'+1 AND ('+CAST(@pageNo AS NVARCHAR(5))+'*'+CAST(@pageSize AS NVARCHAR(5))+')'
    SET @qry=@qry+';SELECT Count(*) As ''Total'' From Products'
    EXEC Sp_executesql @qry
END

Now create the GridView in the .aspx page:

<asp:GridView ID="gridView" runat="server" AllowCustomPaging="true" AllowPaging="true" OnPageIndexChanging="gridView_PageIndexChanging" PagerStyle-CssClass="pagingDiv"></asp:GridView>

Some CSS to make it look Good:

<style>
#gridView {
    margin: 25px auto 0 auto;
}
 
    #gridView > tbody > tr:nth-child(1) {
        background-color: #0f40e0;
    }
 
    #gridView > tbody > tr:not(:nth-child(1)) {
        background-color: #ff6a00;
    }
 
    #gridView > tbody > tr.pagingDiv {
        background-color: #f2f2f2;
    }
 
        #gridView > tbody > tr.pagingDiv table {
            padding-left: 10px;
            width: 35%;
         }
 
             #gridView > tbody > tr.pagingDiv table td {
                 display: inline;
             }
 
.pagingDiv a, .pagingDiv span {
    display: inline-block;
    padding: 0px 9px;
    margin-right: 4px;
    border-radius: 3px;
    border: solid 1px #c0c0c0;
    background: #e9e9e9;
    box-shadow: inset 0px 1px 0px rgba(255,255,255, .8), 0px 1px 3px rgba(0,0,0, .1);
    font-size: .875em;
    font-weight: bold;
    text-decoration: none;
    color: #717171;
    text-shadow: 0px 1px 0px rgba(255,255,255, 1);
}
 
    .pagingDiv a:hover {
        background: #fefefe;
        background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#FEFEFE), to(#f0f0f0));
        background: -moz-linear-gradient(0% 0% 270deg,#FEFEFE, #f0f0f0);
    }
 
    .pagingDiv a.active {
        border: none;
        background: #616161;
        box-shadow: inset 0px 0px 8px rgba(0,0,0, .5), 0px 1px 0px rgba(255,255,255, .8);
        color: #f0f0f0;
        text-shadow: 0px 0px 3px rgba(0,0,0, .5);
    }
 
    .pagingDiv span {
        color: #f0f0f0;
        background: #616161;
    }
</style>

Next add the C# code in the .aspx.cs page:

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        BindGridView(1);
    }
}
 
protected void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gridView.PageIndex = e.NewPageIndex;
    BindGridView(e.NewPageIndex + 1);
}
 
public void BindGridView(int pageNo)
{
    int pageSize = 5;
    gridView.PageSize = pageSize;
 
    SqlConnection conn = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    DataSet dataSet= new DataSet();
 
    conn.ConnectionString = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
 
    cmd.Connection = conn;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "[dbo].[sp_GetProductByCustomPaging]";
 
    cmd.Parameters.AddWithValue("@PageNo", pageNo);
    cmd.Parameters.AddWithValue("@pageSize", pageSize);
 
    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
    sqlDataAdapter.SelectCommand = cmd;
 
    try
    {
        sqlDataAdapter.Fill(dataSet);
 
        gridView.VirtualItemCount = Convert.ToInt32(dataSet.Tables[1].Rows[0]["Total"]);
        gridView.DataSource = dataSet.Tables[0];
        gridView.DataBind();
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally
    {
    }
}

Download link:

DOWNLOAD

Would you like to dig deep into the world of database programming with C#? Then you can check this series of tutorials on Entity Framework Core that help you to learn database programming within a few hours time.