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();
          }
     }
}

ADO.NET Select Stored Procedure using SQLParameter

This artilce explains how to use parameters in the command. I have created a database called Test in SQL which contains a UserDetail table having UserId and Password as column.

1.Import below namespace

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

2. Create and open SQLConnection

string connectionString = "Data Source=localhost;Initial Catalog=Test;Integrated Security=True";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();

3. Create stored procedure

CREATE PROCEDURE GetUserDetail
@UserId VARCHAR(50),
@Password VARCHAR(50)
AS
BEGIN
 Select UserId From UserDetail WHERE UserId = @UserId AND Password = @Password
END

4. Create SQLCommand Objects

SqlCommand cmd = new SqlCommand("GetUserDetail", conn);
cmd.CommandType = CommandType.StoredProcedure;

5. Create SqlParameter Objects and bind it to SQLCommand objects

SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@UserId", strUserId);
param[1] = new SqlParameter("@Password", strPassword);
//add parameters to command object
cmd.Parameters.Add(param[0]);
cmd.Parameters.Add(param[1]);

6. Get Data from DB

//get data stream
SqlDataReader reader = cmd.ExecuteReader();
//Check whether record exists or not
if (reader.HasRows)
{
     // Loop through each record
     while (reader.Read())
     {
          strName = reader["UserId"].ToString();
     }
}
else
{
     Response.Write("Record not found.");
}

 

Let's Put all together

string connectionString = "Data Source=localhost;Initial Catalog=Test;Integrated Security=True";
SqlConnection conn = null;
SqlDataReader reader = null;
string strUserId = "myName";
string strPassword = "myPassword";
string strName = string.Empty;
try
{
     conn = new SqlConnection(connectionString);
     conn.Open();
     SqlCommand cmd = new SqlCommand("GetUserDetail", conn);
     cmd.CommandType = CommandType.StoredProcedure;
     SqlParameter[] param = new SqlParameter[2];
     param[0] = new SqlParameter("@UserId", strUserId);
     param[1] = new SqlParameter("@Password", strPassword);
     //add new parameter to command object
     cmd.Parameters.Add(param[0]);
     cmd.Parameters.Add(param[1]);
     //get data stream
     reader = cmd.ExecuteReader();
     //Check whether record exists or not
     if (reader.HasRows)
     {
          // Loop through each record
          while (reader.Read())
          {
               strName = reader["UserId"].ToString();
          }
     }
     else
     {
          Response.Write("Record not found.");
     }
}
catch (Exception ex)
{
}
finally
{
     //Closing reader and connection in finally block in case  of any exception reader and conn should close.
     //Code in finally block executes in normal scenario as well as in case of exception thrown.
     if (reader != null)
     {
          reader.Close();
     }
     // close connection
     if (conn != null)
     {
          conn.Close();
     }
}

 

Happy Coding :)