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

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.