How to delete multiple selected records/items based on CheckBox in GridView in asp.net

Introduction: In this article I am going to explain with example How to bind, delete items and delete multiple selected items/ records/ rows from Grid View in asp.net using both C# and VB.Net languages.

Click on the image to enlarge

  Description:  Basically you will learn the following through this article.

• How to bind GridView from Sql server database table.
• How to delete items/records/rows from grid view.
• How to show Checkbox with each row/record in GridView
• How to delete multiple records from GridView by selecting multiple items using CheckBox in grid view.
• How to implement the Confirmation before Deleting gridview records
• How to implement Paging in GridView

In previous article i explained How to Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView and WCF Service to bind,insert,edit,update,delete from sql server database in asp.net and Send email to multiple users based on CheckBox selection inside GridView and Get CheckBoxList selected items in comma separated format and Bind,upload,download,delete image files from the GridView and Display Serial/Row Number automatically in GridView

Implementation: Let’s create a sample web application to understand the concept practically.

Source Code:

• In the design page (.aspx) place GridView control and set it as:

<div>

    <fieldset style="width:415px;">

    <legend>Bind,Delete,Multiple delete example in gridview</legend>  

    <asp:GridView ID="grdEmp" runat="server" AllowSorting="True" ShowFooter="true"

            DataKeyNames="Emp_Id"  CssClass="rowHover" RowStyle-CssClass="rowHover"

            AutoGenerateColumns="False" 

            EmptyDataText="No records found"

            AllowPaging="True" onrowdeleting="grdEmp_RowDeleting"

            onpageindexchanging="grdEmp_PageIndexChanging"

            PageSize="10"         

            CellPadding="4" ForeColor="#333333"

            GridLines="None">          

            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />                     

         <Columns>

         <asp:BoundField HeaderText="Emp Name" DataField="EmpName" />

         <asp:BoundField HeaderText="Age" DataField="Age" />

         <asp:BoundField HeaderText="Salary" DataField="Salary" />

         <asp:BoundField HeaderText="Address" DataField="Address" />

         <asp:TemplateField HeaderText="Delete"  HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center">

            <ItemTemplate>

                 <asp:ImageButton ID="imgDelete" runat="server" CommandName="Delete"  ImageUrl="~/Images/Delete.png" OnClientClick="return confirm('Are you sure you want to delete selected record ?')" ToolTip="Delete"/>

            </ItemTemplate>           

             <HeaderStyle HorizontalAlign="Center" />

             <ItemStyle HorizontalAlign="Center" />

         </asp:TemplateField>

       

        <asp:TemplateField HeaderText="Multiple Delete" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center">

        <ItemTemplate>

          <asp:CheckBox ID="chkDel" runat="server" />

        </ItemTemplate>

        <FooterTemplate>

            <asp:Button ID="btnDelete" runat="server" Text="Multiple Delete"

            OnClientClick="return confirm('Are you sure you want to delete selected records?')"

            onclick="btnDelete_Click" />

         </FooterTemplate>        

            <HeaderStyle HorizontalAlign="Center" />

            <ItemStyle HorizontalAlign="Center" />

        </asp:TemplateField>      

        </Columns>        

            <EditRowStyle BackColor="#999999" />

            <FooterStyle BackColor="#ffffff" Font-Bold="True" ForeColor="White" />

            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />

<RowStyle CssClass="rowHover" BackColor="#F7F6F3" ForeColor="#333333"></RowStyle>

            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />

            <SortedAscendingCellStyle BackColor="#E9E7E2" />

            <SortedAscendingHeaderStyle BackColor="#506C8C" />

            <SortedDescendingCellStyle BackColor="#FFFDF8" />

            <SortedDescendingHeaderStyle BackColor="#6F8DAE" />

<EmptyDataRowStyle Width = "410px" ForeColor="Red" Font-Bold="true"

   HorizontalAlign = "Center"/> 

        </asp:GridView>

         </fieldset>

    </div>

Note: Create a folder in the root directory and name it “Images” and place the “delete image icon” in this folder. You can search on google for the term “delete png icons”.

• Now Create a Database in Sql server e.g. “Emp_DB” and create a Table with the columns and the Data type as shown below and name it “Emp_Tb”.

Column Name	Data Type
Emp_Id	Int( Primary Key. So set Is Identity=True)
EmpName	varchar(100)
Age	int
Salary	int
Address	varchar(500)

• Create a stored procedure to fetch the employee records and bind gridview  

CREATE PROCEDURE BindEmpGrid_Sp            

AS

BEGIN

                SELECT * FROM Emp_Tb

END    

• Create a stored procedure to delete the employee records

CREATE PROCEDURE DeleteEmpRecord_Sp  

                @EmpId INT

AS

BEGIN

                DELETE FROM Emp_Tb WHERE Emp_Id=@EmpId

END

• In the web.con fig file create the connection string to connect the asp.net web application with the Sql server database.

<connectionStrings>

    <add name="con" connectionString="Data Source=Lalit;Initial Catalog=Emp_DB;Integrated Security=True"/>

  </connectionStrings>

Note: Replace the Data Source and Initial catalog (i.e. Database Name) as per your application.

C#.Net Code to bind,delete and multiple deletion using checkbox from gridview

• In the code behind file (.aspx.cs) write the code as:

First include the required namespaces and then write the below mentioned code:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)

    {

        if (con.State == ConnectionState.Closed)

        {

            con.Open();

        }

        if (!Page.IsPostBack)

        {

            BindEmpGrid();

        }

    }

    #region "Bind GridView"

    private void BindEmpGrid()

    {      

       SqlDataAdapter adp = new SqlDataAdapter();

       DataTable dt = new DataTable();

        try

        {

            adp = new SqlDataAdapter("BindEmpGrid_Sp", con);

            adp.Fill(dt);

            if (dt.Rows.Count > 0)

            {

                grdEmp.DataSource = dt;

                grdEmp.DataBind();

            }

            else

            {

                grdEmp.DataSource = null;

                grdEmp.DataBind();

            }

        }

        catch (Exception ex)

        {

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);                     

        }

        finally

        {

            dt.Clear();

            dt.Dispose();

            adp.Dispose();

            con.Close();

        }

    }

    #endregion

    #region "GridView Paging"

    protected void grdEmp_PageIndexChanging(object sender, GridViewPageEventArgs e)

    {

        grdEmp.PageIndex = e.NewPageIndex;

        BindEmpGrid();

    }

    #endregion

    #region "Deletion in gridview"

    protected void grdEmp_RowDeleting(object sender, GridViewDeleteEventArgs e)

    {

        SqlCommand cmd = new SqlCommand();

        try

        {

            //get EmpId from DatakeyNames from gridview

            int empId = Convert.ToInt32(grdEmp.DataKeys[e.RowIndex].Value);

            cmd = new SqlCommand("DeleteEmpRecord_Sp", con);

            cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId;

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.ExecuteNonQuery();

            BindEmpGrid();

            ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), Guid.NewGuid().ToString(), "alert('Record has been deleted successfully');", true);

        }

        catch (Exception ex)

        {

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);                      

        }

        finally

        {

            cmd.Dispose();

            con.Close();

        }

    }

    #endregion

    #region "To delete multiple record"

    protected void btnDelete_Click(object sender, EventArgs e)

    {

        SqlCommand cmd = new SqlCommand();

        try

        {

            //Loop through all the rows in gridview

            foreach (GridViewRow grv in grdEmp.Rows)

            {

                //Finiding checkbox control in gridview for particular row

                CheckBox chk = (CheckBox)grv.FindControl("chkDel");

                if (chk.Checked)

                {

                    //get EmpId from DatakeyNames from gridview

                    int empid = Convert.ToInt32(grdEmp.DataKeys[grv.RowIndex].Value);

                    cmd = new SqlCommand("DeleteEmpRecord_Sp", con);

                    cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = empid;

                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.ExecuteNonQuery();

                }

            }

            grdEmp.EditIndex = -1;

            BindEmpGrid();

            ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), Guid.NewGuid().ToString(), "alert('Selected Records has been deleted successfully');", true);

          }

        catch (Exception ex)

        {

            ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);                     

        }

        finally

        {

            cmd.Dispose();

            con.Close();

        }

    }

    #endregion

VB.Net Code to bind,delete and multiple deletion using checkbox from gridview

• Design the page as shown above in the source code but delete the onrowdeleting="grdEmp_RowDeleting"            onpageindexchanging="grdEmp_PageIndexChanging" from the grid view source code.

• In the code behind file (.aspx.vb) write the code as:

First include the required namespaces and then write the below mentioned code

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

  Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        If con.State = ConnectionState.Closed Then

            con.Open()

        End If

        If Not Page.IsPostBack Then

            BindEmpGrid()

        End If

    End Sub

#Region "Bind GridView"

    Private Sub BindEmpGrid()

        Dim adp As New SqlDataAdapter()

        Dim dt As New DataTable()

        Try

            adp = New SqlDataAdapter("BindEmpGrid_Sp", con)

            adp.Fill(dt)

            If dt.Rows.Count > 0 Then

                grdEmp.DataSource = dt

                grdEmp.DataBind()

            Else

                grdEmp.DataSource = Nothing

                grdEmp.DataBind()

            End If

        Catch ex As Exception

            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Error occured : " & ex.Message.ToString() & "');", True)

        Finally

            dt.Clear()

            dt.Dispose()

            adp.Dispose()

        End Try

    End Sub

#End Region

#Region "GridView paging"

    Protected Sub grdEmp_PageIndexChanging(sender As Object, e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles grdEmp.PageIndexChanging

        grdEmp.PageIndex = e.NewPageIndex

        BindEmpGrid()

    End Sub

#End Region

    Protected Sub grdEmp_RowDeleting(sender As Object, e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles grdEmp.RowDeleting

        Dim cmd As New SqlCommand()

        Try

            'get EmpId from DatakeyNames from gridview

            Dim empId As Integer = Convert.ToInt32(grdEmp.DataKeys(e.RowIndex).Value)

            cmd = New SqlCommand("DeleteEmpRecord_Sp", con)

            cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = empId

            cmd.CommandType = CommandType.StoredProcedure

            cmd.ExecuteNonQuery()

            BindEmpGrid()

            ScriptManager.RegisterClientScriptBlock(Page, Page.[GetType](), Guid.NewGuid().ToString(), "alert('Record has been deleted successfully');", True)

        Catch ex As Exception

            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Error occured : " & ex.Message.ToString() & "');", True)

        Finally

            cmd.Dispose()

            con.Close()

        End Try

    End Sub

    Protected Sub btnDelete_Click(sender As Object, e As System.EventArgs)

        Dim cmd As New SqlCommand()

        Try

            'Loop through all the rows in gridview

            For Each grv As GridViewRow In grdEmp.Rows

                'Finiding checkbox control in gridview for particular row

                Dim chk As CheckBox = DirectCast(grv.FindControl("chkDel"), CheckBox)

                If chk.Checked Then

                    'get EmpId from DatakeyNames from gridview

                    Dim empid As Integer = Convert.ToInt32(grdEmp.DataKeys(grv.RowIndex).Value)

                    cmd = New SqlCommand("DeleteEmpRecord_Sp", con)

                    cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = empid

                    cmd.CommandType = CommandType.StoredProcedure

                    cmd.ExecuteNonQuery()

                End If

            Next

            grdEmp.EditIndex = -1

            BindEmpGrid()

            ScriptManager.RegisterClientScriptBlock(Page, Page.[GetType](), Guid.NewGuid().ToString(), "alert('Selected Records has been deleted successfully');", True)

        Catch ex As Exception

            ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Error occured : " & ex.Message.ToString() & "');", True)

        Finally

            cmd.Dispose()

            con.Close()

        End Try

    End Sub

Now over to you:

"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."

• Tweet
• Share
• Share
• Share

Related Post