Paging in GridView

This artilce explains how to paging in GridView. I have created a database called Test in SQL which contains a Emplyee table having Name,Country,City and Age as column.

1. Place a GridView control on the aspx page with pagesize as 10 and OnPageIndexChanging="MyGrid_PageIndexChanging" which will call code behind method for paging. PageSize denotes number of rows to be appeared on at a time

<asp:GridView ID="MyGrid" runat="server" AllowPaging="True" PageSize="10" OnPageIndexChanging="MyGrid_PageIndexChanging"></asp:GridView>

2. Import below namespace to get data from the database using SQL objects

using System.Data;
using System.Data.SqlClient;

3. Create SQL connection and command object to retrieve data from database.

SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("GetEmployee", conn);
cmd.CommandType = CommandType.StoredProcedure;

4. Create SQLDataAdapter object and populate datatable with data

SqlDataAdapter mySqlAdapter = new SqlDataAdapter(cmd);
DataTable myDataTable = new DataTable();
mySqlAdapter.Fill(myDataTable);

5. Create Stored Procedure to get data from database.

CREATE  PROCEDURE GetEmployee
AS
BEGIN
 Select Name,Country,City,Age  FROM EMPLOYEE
END

6. Bind GridView to DataTable

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

7. Create Page indexing method for paging

protected void MyGrid_PageIndexChanging(object sender, GridViewPageEventArgs e)
{ 
   MyGrid.PageIndex = e.NewPageIndex;
   BindGridView();
}

8. Page_Load will contain call to BindGridView() method to bind the data to GridView first time when page loads.

protected void Page_Load(object sender, EventArgs e)
{
     if(!IsPostBack)
        BindGridView();
}

9. Let's put all together

i) Put gridview control on aspx page

<asp:GridView ID="MyGrid" runat="server" AllowPaging="True" PageSize="10" OnPageIndexChanging="MyGrid_PageIndexChanging"></asp:GridView>

ii) Code Behind code

protected void Page_Load(object sender, EventArgs e)
{
     if(!IsPostBack)
          BindGridView();
}

protected void MyGrid_PageIndexChanging(object sender, GridViewPageEventArgs e)
{ 
     MyGrid.PageIndex = e.NewPageIndex; 
     BindGridView();
}

public void BindGridView()
{
     string connectionString = "Data Source=localhost;Initial      Catalog=Test;Integrated Security=True";
     SqlConnection conn = null;
     string strName = string.Empty;
     try
     {
          conn = new SqlConnection(connectionString);
          conn.Open();
          SqlCommand cmd = new SqlCommand("GetEmployee", conn);
          cmd.CommandType = CommandType.StoredProcedure;
          SqlDataAdapter mySqlAdapter = new SqlDataAdapter(cmd);
          DataTable myDataTable = new DataTable();
          mySqlAdapter.Fill(myDataTable);
          MyGrid.DataSource = myDataTable;
          MyGrid.DataBind();
     }
     catch (Exception ex)
     {
          Response.Write(ex.Message);
     }
     finally
     {
          //Closing connection in finally block in case of any exception, conn should close.
          // close connection
          if (conn != null)
          {
               conn.Close();
          }
     }
}