Delete multiple rows from asp.net gridview - Part 28

Suggested Videos 
Part 25 - GridView insert update delete without using datasource controls
Part 26 - Displaying summary data in asp.net gridview footer
Part 27 - EmptyDataText and EmptyDataTemplate properties of asp.net gridview control



In this video we will discuss about deleting multiple rows from gridview control using checkbox.

We will be using tblEmployee table for this demo. For SQL script to create and populate this table, please refer to, Part 13 - Deleting data from gridview using sqldatasource control.



Step 1: Create an asp.net web application. Right click on the web application project and add a class file with name EmployeeDataAccessLayer.cs. Copy and paste the following code in EmployeeDataAccessLayer.cs file.
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;

namespace Demo
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
    }

    public class EmployeeDataAccessLayer
    {
        public static List<Employee> GetAllEmployees()
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("Select * from tblEmployee", con);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.Name = rdr["Name"].ToString();
                    employee.Gender = rdr["Gender"].ToString();
                    employee.City = rdr["City"].ToString();

                    listEmployees.Add(employee);
                }
            }

            return listEmployees;
        }

        public static void DeleteEmployees(List<string> EmployeeIds)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                List<string> parameters = EmployeeIds.Select((s, i) => "@Parameter" + i.ToString() ).ToList();
                string inClause = string.Join(",", parameters);
                string deleteCommandText = "Delete from tblEmployee where EmployeeId IN (" + inClause + ")";
                SqlCommand cmd = new SqlCommand(deleteCommandText, con);

                for (int i = 0; i < parameters.Count; i++)
                {
                    cmd.Parameters.AddWithValue(parameters[i], EmployeeIds[i]);
                }
                
                con.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }
}

Step 2: Copy and paste the following HTML in WebForm1.aspx. 
<div style="font-family:Arial">
<asp:Button ID="btnDelete" runat="server" Text="Delete" 
    onclick="btnDelete_Click" />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
    BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" 
    BorderWidth="1px" CellPadding="3" CellSpacing="2">
    <Columns>
        <asp:TemplateField>
            <HeaderTemplate>
                <asp:CheckBox ID="cbDeleteHeader" runat="server" 
                    AutoPostBack="True" 
                    oncheckedchanged="cbDeleteHeader_CheckedChanged" />
            </HeaderTemplate>
            <ItemTemplate>
                <asp:CheckBox ID="cbDelete" runat="server" 
                    AutoPostBack="true" 
                    oncheckedchanged="cbDelete_CheckedChanged" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Id">
            <ItemTemplate>
                <asp:Label ID="lblEmployeeId" runat="server" 
                     Text='<%# Bind("EmployeeId") %>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Gender" HeaderText="Gender" />
        <asp:BoundField DataField="City" HeaderText="City" />
    </Columns>
    <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
    <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
    <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
    <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
    <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" 
        ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#FFF1D4" />
    <SortedAscendingHeaderStyle BackColor="#B95C30" />
    <SortedDescendingCellStyle BackColor="#F1E5CE" />
    <SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
<asp:Label ID="lblMessage" runat="server" Font-Bold="true">
</asp:Label>
</div>

Step 3: Copy and paste the following code in WebForm1.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Demo
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            // Load data on initial get request
            if (!IsPostBack)
            {
                GetData();
            }
        }

        // Method to retrieve data and bind to gridview
        public void GetData()
        {
            GridView1.DataSource = EmployeeDataAccessLayer.GetAllEmployees();
            GridView1.DataBind();
        }

        // Eventhandler method to select or deselect all rows depending
        // on if the checkbox in header row is selected or deselected
        protected void cbDeleteHeader_CheckedChanged(object sender, EventArgs e)
        {
            foreach (GridViewRow gridViewRow in GridView1.Rows)
            {
                ((CheckBox)gridViewRow.FindControl("cbDelete")).Checked = ((CheckBox)sender).Checked;
            }
        }

        // Select or deselect the checkbox in the header row, when the selection
        // of checkboxes in gridview data rows change
        protected void cbDelete_CheckedChanged(object sender, EventArgs e)
        {
            CheckBox headerCheckBox = 
                (CheckBox)GridView1.HeaderRow.FindControl("cbDeleteHeader");
            if (headerCheckBox.Checked)
            {
                headerCheckBox.Checked = ((CheckBox)sender).Checked;
            }
            else
            {
                bool allCheckBoxesChecked = true;
                foreach (GridViewRow gridViewRow in GridView1.Rows)
                {
                    if (!((CheckBox)gridViewRow.FindControl("cbDelete")).Checked)
                    {
                        allCheckBoxesChecked = false;
                        break;
                    }
                }
                headerCheckBox.Checked = allCheckBoxesChecked;
            }
        }

        // Method to delete selected employee rows in gridview
        protected void btnDelete_Click(object sender, EventArgs e)
        {
            List<string> lstEmployeeIdsToDelete = new List<string>();
            foreach (GridViewRow gridViewRow in GridView1.Rows)
            {
                if(((CheckBox)gridViewRow.FindControl("cbDelete")).Checked)
                {
                    string employeeId = 
                        ((Label)gridViewRow.FindControl("lblEmployeeId")).Text;
                    lstEmployeeIdsToDelete.Add(employeeId);
                }
            }
            if (lstEmployeeIdsToDelete.Count > 0)
            {
                EmployeeDataAccessLayer.DeleteEmployees(lstEmployeeIdsToDelete);
                GetData();
                lblMessage.ForeColor = System.Drawing.Color.Navy;
                lblMessage.Text = lstEmployeeIdsToDelete.Count.ToString() + 
                    " row(s) deleted";
            }
            else
            {
                lblMessage.ForeColor = System.Drawing.Color.Red;
                lblMessage.Text = "No rows selected to delete";
            }
        }
    }
}