Grid View with paging facility asp.net C#

 protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            String conStr = ConfigurationManager.ConnectionStrings["connectionStringTest"].ConnectionString;

            using (SqlConnection con = new SqlConnection(conStr))

            {

                using (SqlCommand cmd = new SqlCommand("Select Count(*) from TEST1", con))

                {

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))

                    {

                        con.Open();

                        int count = Convert.ToInt32(cmd.ExecuteScalar());

                        GridView1.VirtualItemCount = count;

                        con.Close();

                    }

                   

                    this.getRecordsTestByStartEndPaging(0, 10);

                }

            }

        }

    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)

    {

        GridView1.PageIndex = e.NewPageIndex;

        this.getRecordsTestByStartEndPaging(e.NewPageIndex, 10);

    }

    private void getRecordsTestByStartEndPaging(int start, int limit)

    {

        //if you want to change the limit, you also have to change the limit in Gridview

        start = (start * 10) + 1;

        int end = start + limit;

        String conStr = ConfigurationManager.ConnectionStrings["connectionStringTest"].ConnectionString;

        using (SqlConnection con = new SqlConnection(conStr))

        {

            using (SqlCommand cmd = new SqlCommand("SELECT * FROM( SELECT * , ROW_NUMBER()" +

                " OVER(ORDER BY ID) AS RowNum FROM Test1 ) AS MyDerivedTable WHERE " +

                "MyDerivedTable.RowNum BETWEEN " + start + " AND " + end, con))

            {

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))

                {

                    DataTable dt = new DataTable();

                    da.Fill(dt);

                    GridView1.DataSource = dt;

                    GridView1.DataBind();

                }

            }

        }

    }

---------------------------*********************------------------------------

UI

<div>

            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" PageSize="10"

                OnPageIndexChanging="GridView1_PageIndexChanging" AllowCustomPaging="True">

                

            </asp:GridView>

        </div>

-----------------------------------******************------------------------------------------

Web.config

<configuration>

  <connectionStrings>

    <add name="connectionStringTest" connectionString="initial catalog=test;user=sa1;password=sa;data source=." />

  </connectionStrings>

</configuration>

-----------------------------------*****************------------------------------------------

Database table design