Delete multiple records from asp.net gridview with checkbox selection | Preserving checkbox state on paging | Check/uncheck all checkbox in gridview
Introduction: In this article i will explain How to delete multiple selected records/items from GridView based on Checkbox selection while paging is enabled in asp.net with both the C# and VB.Net language and using stored procedure and Sql Server as a back end database.
 |
| Click on the image to view enlarged demo |
Description: Basically you will learn the following through this article.
- How to bind GridView from sql server database table using stored procedure
- How to implement Check/Uncheck All checkbox feature using javascript to delete all the records from the gridview
- How to show/implement checkbox in gridview rows to delete the selected records.
- How to preserve checkbox state on paging using viewstate
- How to maintain checkbox selections when paging is enabled using viewstate.
In previous article i explained How to Bind,Save,Edit,Update,Cancel,Delete,Paging example in GridView and How to Bind,save,edit,update,delete records from DataList and Send email to multiple users based on CheckBox selection inside GridView and Validate asp.net CheckBoxList using jQuery and Validate CheckBoxList using JavaScript .
Implementation: Let's create an asp.net application to see the work in action.
- First of all create a database in Sql server e.g. Emp_DB and create a table with the columns and 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
|
decimal(10, 2)
|
City
|
varchar(100)
|
Address
|
varchar(500)
|
- Create a stored procedure to fetch the employee records and bind the Grid View
CREATE PROCEDURE BindEmpGrid_Sp
AS
BEGIN
SELECT * FROM Emp_Tb
END
- Create a stored procedure to delete the employee record.
CREATE PROCEDURE DeleteEmpRecord_Sp
@EmpId INT
AS
BEGIN
DELETE FROM Emp_Tb WHERE Emp_Id=@EmpId
END
- In the web.config file create the connection string to connect the asp.net 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.
- In the <Head> tag of the design page e.g. default.aspx, create or copy paste the following java script function to implement the check all/uncheck all checkbox on Gridview header.
<script type = "text/javascript">
function grdHeaderCheckBox(objRef) {
var grd = objRef.parentNode.parentNode.parentNode;
var inputList = grd.getElementsByTagName("input");
for (var i = 0; i < inputList.length; i++) {
var row = inputList[i].parentNode.parentNode;
if (inputList[i].type == "checkbox" && objRef != inputList[i]) {
if (objRef.checked) {
inputList[i].checked = true;
}
else {
inputList[i].checked = false;
}
}
}
}
</script>
- In the <Body> tag of the default.aspx ,design the page as:
Source Code:
<fieldset style="width:380px;">
<legend>Delete multiple records from gridview example</legend>
<asp:GridView ID="grdEmp" runat="server" AllowSorting="True" ShowFooter="true"EmptyDataText="No records found"
DataKeyNames="Emp_Id" CssClass="rowHover" RowStyle-CssClass="rowHover"ShowHeader="true"
AutoGenerateColumns="False"
AllowPaging="True"
onpageindexchanging="grdEmp_PageIndexChanging"
PageSize="5"
CellPadding="4" ForeColor="#333333"
GridLines="None">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:TemplateField HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center">
<HeaderTemplate>
<asp:CheckBox ID="chkAll" runat="server" Text="All"
onclick = "grdHeaderCheckBox(this);" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="chkRow" 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>
</asp:TemplateField>
<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" />
</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 = "550px" ForeColor="Red" Font-Bold="true"
HorizontalAlign = "Center"/>
</asp:GridView>
</fieldset>
Note: In the very first line of the default.aspx page set the EnableEventValidation="false"
So that it look like <%@ Page Language="C#" AutoEventWireup="true"EnableEventValidation="false" CodeFile="Default.aspx.cs" Inherits="_Default" %>
How to maintain the state of the checkbox while paging is enabled?
Suppose paging is enabled in the gridview and there are 10 records per page and when you select some records from first page and got to next page and select some more records. Now when you come back on first page then the selected checkbox are unchecked i.e. the state of the checkbox are lost. This is due to fact that checkbox does not maintain state on navigation if paging is enabled. So to save the selected checkbox of all the pages i have created two functions:
- SaveSelectedData: This function is to retrieve the selected records i.e. the records that are checked by the checkbox, adds them to an ArrayList and then saves the ArrayList to ViewState.
- SetSelectedData: This function is to restore the saved state of the checkboxes from the ViewState.
C#.Net code to Delete multiple records from asp.net grid view with check box selection
- In the page behind file (default.aspx.cs) write the code as:
First of all include the required namespaces and write the code as:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
if (Page.IsPostBack)
{
SaveSelectedData();
}
BindEmpGrid();
}
#region "Bind Employee records in 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
{
con.Close();
adp.Dispose();
}
}
#endregion
#region "GridView Paging"
protected void grdEmp_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
grdEmp.PageIndex = e.NewPageIndex;
grdEmp.DataBind();
SetSelectedData();
}
#endregion
private void SaveSelectedData()
{
ArrayList arrLst;
//Check the ViewState. If it has data then add to arraylist
if (ViewState["CheckedRecords"] != null)
arrLst = (ArrayList)ViewState["CheckedRecords"];
else
arrLst = new ArrayList();
//Find the "All" checkbox from the GridView Header
CheckBox chkAll = (CheckBox)grdEmp.HeaderRow.Cells[0].FindControl("chkAll");
for (int i = 0; i < grdEmp.Rows.Count; i++)
{
Int32 Emp_Id;
//check if the "All" checkbox is checked or not
if (chkAll.Checked)
{
// If checked, then if the arraylist doesn't contain the Emp_Id then add it to the arraylist.
Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
if (!arrLst.Contains(Emp_Id))
{
arrLst.Add(Emp_Id);
}
}
// When "All" checkbox not checked
else
{
//Find the checkbox from the GridView rows
CheckBox chkRow = (CheckBox)grdEmp.Rows[i].Cells[0].FindControl("chkRow");
//check if the checkbox inside the gridview rows is checked or not
if (chkRow.Checked)
{
// If checked, then if the arraylist doesn't contain the Emp_Id then add it to the arraylist.
Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
if (!arrLst.Contains(Emp_Id))
{
//add Emp_id in Array list
arrLst.Add(Emp_Id);
}
}
// When checkbox inside gridview rows is not checked
else
{
Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
// if array list contains the Emp_Id
if (arrLst.Contains(Emp_Id))
{
//Remove Emp_id from Array list
arrLst.Remove(Emp_Id);
}
}
}
}
//Storing the contents of array list i.e. Emp_id into ViewState.
ViewState["CheckedRecords"] = arrLst;
}
private void SetSelectedData()
{
// int currentCount = 0;
//Find the "All" checkbox from the GridView Header
CheckBox chkAll = (CheckBox)grdEmp.HeaderRow.Cells[0].FindControl("chkAll");
chkAll.Checked = true;
// adding Checked records from ViewState to Arraylist
ArrayList arrLst = (ArrayList)ViewState["CheckedRecords"];
for (int i = 0; i < grdEmp.Rows.Count; i++)
{
//Find the checkbox from the GridView Rows
CheckBox chkRow = (CheckBox)grdEmp.Rows[i].Cells[0].FindControl("chkRow");
if (chkRow != null)
{
Int32 Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
chkRow.Checked = arrLst.Contains(Emp_Id);
if (!chkRow.Checked)
{
chkAll.Checked = false;
}
}
}
}
#region "Delete multiple selected records"
protected void btnDelete_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand();
try
{
SetSelectedData();
grdEmp.AllowPaging = false;
grdEmp.DataBind();
ArrayList arrLst = (ArrayList)ViewState["CheckedRecords"];
for (int i = 0; i < grdEmp.Rows.Count; i++)
{
Int32 Emp_Id = Convert.ToInt32(grdEmp.DataKeys[i].Value);
if (arrLst.Contains(Emp_Id))
{
cmd = new SqlCommand("DeleteEmpRecord_Sp", con);
cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = Emp_Id;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
arrLst.Remove(Emp_Id);
con.Close();
}
}
ViewState["CheckedRecords"] = arrLst;
grdEmp.AllowPaging = true;
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(),Guid.NewGuid().ToString(), "alert('Selected Records has been deleted successfully');", true);
BindEmpGrid();
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", true);
}
finally
{
cmd.Dispose();
}
}
#endregion
VB.Net Code to
- In the code behind file (default.aspx.vb) write the code as:
First import the following required namespaces and write the code as:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Dim con As NewSqlConnection(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 Page.IsPostBack Then
SaveSelectedData()
End If
BindEmpGrid()
End Sub
#Region "Bind Employee records in 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
con.Close()
adp.Dispose()
End Try
End Sub
#End Region
#Region "paging in GridView"
Protected Sub grdEmp_PageIndexChanging(sender As Object, e AsSystem.Web.UI.WebControls.GridViewPageEventArgs) Handles grdEmp.PageIndexChanging
grdEmp.PageIndex = e.NewPageIndex
grdEmp.DataBind()
SetSelectedData()
End Sub
#End Region
Private Sub SaveSelectedData()
Dim arrLst As ArrayList
'Check the ViewState. If it has data then add to arraylist
If ViewState("CheckedRecords") IsNot Nothing Then
arrLst = DirectCast(ViewState("CheckedRecords"), ArrayList)
Else
arrLst = New ArrayList()
End If
'Find the "All" checkbox from the GridView Header
Dim chkAll As CheckBox =DirectCast(grdEmp.HeaderRow.Cells(0).FindControl("chkAll"), CheckBox)
For i As Integer = 0 To grdEmp.Rows.Count - 1
Dim Emp_Id As Int32
'check if the "All" checkbox is checked or not
If chkAll.Checked Then
' If checked, then if the arraylist doesn't contain the Emp_Id then add it to the arraylist.
Emp_Id = Convert.ToInt32(grdEmp.DataKeys(i).Value)
If Not arrLst.Contains(Emp_Id) Then
arrLst.Add(Emp_Id)
End If
Else
' When "All" checkbox not checked
'Find the checkbox from the GridView rows
Dim chkRow As CheckBox =DirectCast(grdEmp.Rows(i).Cells(0).FindControl("chkRow"), CheckBox)
'check if the checkbox inside the gridview rows is checked or not
If chkRow.Checked Then
' If checked, then if the arraylist doesn't contain the Emp_Id then add it to the arraylist.
Emp_Id = Convert.ToInt32(grdEmp.DataKeys(i).Value)
If Not arrLst.Contains(Emp_Id) Then
'add Emp_id in Array list
arrLst.Add(Emp_Id)
End If
Else
' When checkbox inside gridview rows is not checked
Emp_Id = Convert.ToInt32(grdEmp.DataKeys(i).Value)
' if array list contains the Emp_Id
If arrLst.Contains(Emp_Id) Then
'Remove Emp_id from Array list
arrLst.Remove(Emp_Id)
End If
End If
End If
Next
'Storing the contents of array list i.e. Emp_id into ViewState.
ViewState("CheckedRecords") = arrLst
End Sub
Private Sub SetSelectedData()
' int currentCount = 0;
'Find the "All" checkbox from the GridView Header
Dim chkAll As CheckBox =DirectCast(grdEmp.HeaderRow.Cells(0).FindControl("chkAll"), CheckBox)
chkAll.Checked = True
' adding Checked records from ViewState to Arraylist
Dim arrLst As ArrayList = DirectCast(ViewState("CheckedRecords"), ArrayList)
For i As Integer = 0 To grdEmp.Rows.Count - 1
'Find the checkbox from the GridView Rows
Dim chkRow As CheckBox =DirectCast(grdEmp.Rows(i).Cells(0).FindControl("chkRow"), CheckBox)
If chkRow IsNot Nothing Then
Dim Emp_Id As Int32 = Convert.ToInt32(grdEmp.DataKeys(i).Value)
chkRow.Checked = arrLst.Contains(Emp_Id)
If Not chkRow.Checked Then
chkAll.Checked = False
End If
End If
Next
End Sub
Protected Sub btnDelete_Click(sender As Object, e As System.EventArgs)
Dim cmd As New SqlCommand()
Try
SetSelectedData()
grdEmp.AllowPaging = False
grdEmp.DataBind()
Dim arrLst As ArrayList = DirectCast(ViewState("CheckedRecords"), ArrayList)
For i As Integer = 0 To grdEmp.Rows.Count - 1
Dim Emp_Id As Int32 = Convert.ToInt32(grdEmp.DataKeys(i).Value)
If arrLst.Contains(Emp_Id) Then
cmd = New SqlCommand("DeleteEmpRecord_Sp", con)
cmd.Parameters.Add("@EmpId", SqlDbType.Int).Value = Emp_Id
cmd.CommandType = CommandType.StoredProcedure
con.Open()
cmd.ExecuteNonQuery()
arrLst.Remove(Emp_Id)
con.Close()
End If
Next
ViewState("CheckedRecords") = arrLst
grdEmp.AllowPaging = True
ScriptManager.RegisterClientScriptBlock(Page, Page.[GetType](),Guid.NewGuid().ToString(), "alert('Selected Records has been deleted successfully');", True)
BindEmpGrid()
Catch ex As Exception
ScriptManager.RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Error occured : " + ex.Message.ToString() + "');", True)
Finally
cmd.Dispose()
End Try
End Sub
No comments:
Post a Comment