Deleting Multiple Rows in GridView in ASP.NET

• Download testingGridDelete.zip - 150 KB

Introduction

The GridView control in ASP.NET is used to represent the information of database in a tabular format. This table format of information is used by developers so they can access large amounts of  information in minimum time. The operations that can be performed with GridView information include inserting new information, updating, deleting, and representing data with appropriate keywords. Today in this article I will show you how to delete multiple Rows/Records from a GridView. I have used a user defined column of type CheckBox control in the GridView, so the user can select multiple rows that he wants to delete.

Using the code

Page_Load(object sender, EventArgs e)
{
    Label2.Text = "Remove Multiple Rows Records Gridview CheckBox before Confirmation";
    if (!IsPostBack)
    {
        bindData();
    }
}

private void bindData()
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = "Data Source=.\\SQLEXPRESS; AttachDBFilename" + 
            "=|DataDirectory|paging.mdf; Integrated Security=True;User Instance=True";
        SqlCommand com = new SqlCommand();
        com.Connection = con;
        com.CommandText = "customerSelect";
        com.CommandType = CommandType.StoredProcedure;
        con.Open();
        SqlDataReader sdr = com.ExecuteReader();
       DataTable dt = new DataTable();
        dt.Load(sdr);
         GridView1.DataSource = dt;
        GridView1.DataBind();
        con.Close();

    }
    catch (Exception ex)
    {
        lblError.Text = ex.ToString();
    }

}

On Delete Button Click

Protected void btnDelete_Click(object sender, EventArgs e)
{
 try
 {
   StringCollection idCollection = new StringCollection();
   string strID = string.Empty;
   for(int i = 0; i < GridView1.Rows.Count; i++)
   {
     CheckBox chkDelete = (CheckBox)GridView1.Rows[i].Cells[0].FindControl("chkSelect");
     if(chkDelete != null)
     {
      if(chkDelete.Checked)
      {
        strID = GridView1.Rows[i].Cells[1].Text;
        idCollection.Add(strID);
      }
     }
   }
  if(idCollection.Count > 0)
  {
    DeleteMultipleRecords(idCollection);
    GridView1.DataBind();
  }
  else
  {
  lblError.Text = "Please select any row to delete";
  }
 }
 catch(Exception ex)
 {
  lblError.Text = ex.ToString();
 }
}

private void DeleteMultipleRecords(StringCollection idCollection)
{
 try
 {
   SqlConnection con = new SqlConnection();
   con.ConnectionString = "Data Source=.\\SQLEXPRESS; AttachDBFilename=" + 
       "|DataDirectory|paging.mdf;Integrated Security=True;User Instance=True";
   SqlCommand com = new SqlCommand();
   string IDs = "";
   foreach(string id in idCollection)
   {
    IDs += id.ToString() + ",";
   }
   try
   {
    string test = IDs.Substring (0, IDs.LastIndexOf(","));
    string sql = "DELETE FROM CUSTOMERS Where ID IN(test)";
    com.CommandType = CommandType.Text;
    com.CommandText = sql;
    com.Connection = con;
    con.Open();
    com.ExecuteNonQuery();
   }
   catch(SqlException ex)
   {
     string errorMsg = "Error in Deletion";
     errorMsg += ex.Message;
     throw new Exception(errorMsg);
   }
   finally
   {
    con.Close();
   }
 }
 catch( Exception ex)
 {
 lblError.Text = ex.ToString();
 }
 bindData();
}