How to implement Custom Paging in ASP.Net GridView using SQL Server Stored Procedure

Introduction

In this post I am explain how to implement Custom Paging in ASP.Net GridView using SQL Server Stored Procedure

Here I am writing this article to implement Custom Paging in ASP.Net GridView using SQL Server Stored Procedure to efficiently retrieve only the specific rows it needs from the database, without pull back hundreds, or even thousands of results which is done in ASP.NET Gridview default pagination for better performance.

Steps :

Step - 1 : Create New Project.

Go to File > New > Project > Select asp.net web forms application > Entry Application Name > Click OK.

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create table for fetch data.

Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.

In this example, I have used one tables as below

Step-4: Create a Stored Procedure in Sql Server for efficiently retrieve only the specific rows.

Open Database > Right Click on Stored Procedure > Add New Stored Procedure > Write below Sql Code > Save.

            CREATE PROCEDURE dbo.getData
             @PageNo int,
             @NoOfRecord int,
             @TotalRecord int output
            AS
             -- Here Get Total No of record 
             Select @TotalRecord = Count(*) from CityData
 
             Select * from
             (
              Select 
               Row_number() over( Order by SLID ASC) as RowNo,
               SLID,
               Country,
               State,
               City
              From
               CityData
             ) as Tab
             Where
              Tab.RowNo between ((@PageNo - 1) * @NoOfRecord) + 1 and (@PageNo * @NoOfRecord)
             Order by SLID ASC
 
            RETURN

Step-5: Add a Webpage and Design for Show Data in Gridview With Custom Paging option

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select web form/ web form using master page under Web > Enter page name > Add.

HTML Code

            <h3>Custom paging in ASP.NET Gridview</h3><br />
            <div>
 
                <asp:GridView ID="GridView1" runat="server" CellSpacing="5" AutoGenerateColumns="false" Width="442px">
                    <Columns>
                        <asp:BoundField DataField="SLID" HeaderText="SL No." />
                        <asp:BoundField DataField="Country" HeaderText="Country Name" />
                        <asp:BoundField DataField="State" HeaderText="State Name" />
                        <asp:BoundField DataField="City" HeaderText="City Name" />
                    </Columns>
                </asp:GridView>
                <%-- Here panel placed for contain Custom button for paging --%>
                <asp:Panel ID="Panel1" runat="server"></asp:Panel>
            </div>

Step-6: Write following code in Page_Load event for Show data in Gridview and Genarate button for Custom Paging.

            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    PopulateData(1, 5);
                }
 
                AddpagingButton();
            }

Step-7: Write below function for fetch & Show data in Gridview.

            private void PopulateData(int pageNo, int noOfRecord)
            {
                // this method is for getting data from database based on selected page
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand("getData", con);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@PageNo", pageNo);
                    cmd.Parameters.AddWithValue("@NoOfRecord", noOfRecord);
 
                    SqlParameter TotalRecordSP = new SqlParameter("@TotalRecord", System.Data.SqlDbType.Int);
                    TotalRecordSP.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(TotalRecordSP);
 
                    DataTable dt = new DataTable();
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    dt.Load(cmd.ExecuteReader());
 
                    int totalRecord = 0;
                    if (TotalRecordSP.Value != null)
                    {
                        int.TryParse(TotalRecordSP.Value.ToString(), out totalRecord);
                    }
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
 
                    // Store Total Record & No of record per page into view state for use in Generate Paging button method
                    ViewState["TotalRecord"] = totalRecord;
                    ViewState["NoOfRecord"] = noOfRecord; 
                }
            }

Step-8: Write below function for Genarate button for Custom Paging.

            private void AddpagingButton()
            {
                // this method for generate custom button for Custom paging in Gridview
                int totalRecord = 0;
                int noofRecord = 0;
                totalRecord = ViewState["TotalRecord"] != null ? (int)ViewState["TotalRecord"] : 0;
                noofRecord = ViewState["NoOfRecord"] != null ? (int)ViewState["NoOfRecord"] : 0;
                int pages = 0;
                if (totalRecord >0 && noofRecord > 0)
                {
                    // Count no of pages 
                    pages = (totalRecord / noofRecord) + ((totalRecord % noofRecord) > 0 ? 1 : 0);
                    for (int i = 0; i < pages; i++)
                    {
                        Button b = new Button();
                        b.Text = (i + 1).ToString();
                        b.CommandArgument = (i + 1).ToString();
                        b.ID = "Button_" + (i + 1).ToString();
                        b.Click += new EventHandler(this.b_click);
                        Panel1.Controls.Add(b);
                    }
                }
 
            }
 
            protected void b_click(object sender, EventArgs e)
            {
                // this is for Get data from Database on button (paging button) click
                string pageNo = ((Button)sender).CommandArgument;
                PopulateData(Convert.ToInt32(pageNo), 5);
            }

Step-9: Run Application.

Download Live Demo