ASP.NET GridView Custom Paging

ASP.NET GridView Custom Paging

Asp.net GridView now supports custom paging too. By using Custom Paging your GridView will never slows 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.

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 page. The remaining 990 records are discarded.

To solve this problem GridView Custom Paging is used where only 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. We have given the procedure to help you accomplish 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 Custom Paging with SQL Server 2012.
  • 2. Add AllowCustomPaging=”true” & AllowPaging=”True” to GridView in your .aspx page
    Eg.

             
    <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.
    Eg.

    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 ‘DataBind’ method.
    Eg.

    gridView.DataSource = myDataTable;
    gridView.DataBind();
    
  • 5. Create ‘PageIndexChanging’ event of GridView
    Eg.

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

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

  • 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. We 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 @[email protected]+';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
        {
        }
    }
    

    DEMO DOWNLOAD

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on “WordPress, SEO, jQuery, HTML” and more.