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.

Bind ,Delete,Multiple deletion using checkboxcontrol from gridview in asp.net

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>

<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">

<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">

<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>

</asp:TemplateField>

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

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

</ItemTemplate>

<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>

</asp:TemplateField>

</Columns>

<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

BEGIN

SELECT * FROM Emp_Tb

END

Create a stored procedure to delete the employee records

CREATE PROCEDURE DeleteEmpRecord_Sp

@EmpId INT

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>

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

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

GN SOMRO .NET CODING

Monday, 17 March 2014

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

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

No comments:

Post a Comment