<asp:GridView ID="gvAll" runat="server"
AutoGenerateColumns = "false" Font-Names = "Arial"
Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B"
HeaderStyle-BackColor = "green" AllowPaging ="true"
OnPageIndexChanging = "OnPaging" DataKeyNames = "CustomerID"
PageSize = "10" >
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="chkAll" runat="server"
/>
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="chk" runat="server"
/>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField ItemStyle-Width = "150px" DataField = "ContactName"
HeaderText = "Contact Name"/>
<asp:BoundField ItemStyle-Width = "150px" DataField = "Country"
HeaderText = "Country"/>
<asp:BoundField ItemStyle-Width = "150px" DataField = "City"
HeaderText = "City"/>
</Columns>
<AlternatingRowStyle BackColor="#C2D69B" />
</asp:GridView>
<asp:HiddenField ID="hfCount" runat="server" Value = "0" />
<asp:Button ID="btnDelete" runat="server" Text="Delete Checked Records"
OnClientClick = "return ConfirmDelete();" OnClick="btnDelete_Click" />
You’ll
notice above I have added a template field with checkboxes also I have
added a check all checkbox in the Header Template of the ASP.net
GridView Control. There’s a hidden field to store the count of the
selected records and a delete button to trigger the delete operation.
Paging is enabled for the GridView and there’s OnPageIndexChanging event that will be described later. CustomerID is assigned to the DataKeyNames property of the GridView which will allow us to uniquely identify each row
Data Binding the ASP.Net GridView control
I have used the Customers
table from the Northwind Database for this example. The Customers table
has some relationships with other tables hence it will not allow delete
directly without removing the foreign key references. Hence to make it
simple will create a new table called TestCustomers and use that instead. To create a clone table execute the following query.
SELECT * INTO TestCustomers
FROM Customers
The
above query will create a new table TestCustomers and copy all the
records from Customers table into TestCustomers. Thus now we can use it
for out tutorial. The following function is used to databind the ASP.Net
GridView control
C#
private void BindGrid()
{
string constr = ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
string query = "select * from TestCustomers";
SqlConnection con = new SqlConnection(constr);
SqlDataAdapter sda = new SqlDataAdapter(query, con);
DataTable dt = new DataTable();
sda.Fill(dt);
gvAll.DataSource = dt;
gvAll.DataBind();
}
VB.Net
Private Sub BindGrid()
Dim constr As String = ConfigurationManager _
.ConnectionStrings("conString").ConnectionString()
Dim query As String = "select * from TestCustomers"
Dim con As New SqlConnection(constr)
Dim sda As New SqlDataAdapter(query, con)
Dim dt As New DataTable()
sda.Fill(dt)
gvAll.DataSource = dt
gvAll.DataBind()
End Sub
Maintaining the state of Checkboxes while paging
This
is the core part of this example since its job is to keep track of the
selected rows irrespective of which page it belongs and also maintain
the state of the checkboxes while pagination so that user selections are
not lost when he navigates from one page to another.
To facilitate this i have made use of the following two functions
1. GetData
The
GetData function simply retrieves the records for which the user has
checked the checkbox, adds them to an ArrayList and then saves the
ArrayList to ViewState
C#
private void GetData()
{
ArrayList arr;
if (ViewState["SelectedRecords"] != null)
arr = (ArrayList)ViewState["SelectedRecords"];
else
arr = new ArrayList();
CheckBox chkAll = (CheckBox)gvAll.HeaderRow
.Cells[0].FindControl("chkAll");
for (int i = 0; i < gvAll.Rows.Count; i++)
{
if (chkAll.Checked)
{
if (!arr.Contains(gvAll.DataKeys[i].Value))
{
arr.Add(gvAll.DataKeys[i].Value);
}
}
else
{
CheckBox chk = (CheckBox)gvAll.Rows[i]
.Cells[0].FindControl("chk");
if (chk.Checked)
{
if (!arr.Contains(gvAll.DataKeys[i].Value))
{
arr.Add(gvAll.DataKeys[i].Value);
}
}
else
{
if (arr.Contains(gvAll.DataKeys[i].Value))
{
arr.Remove(gvAll.DataKeys[i].Value);
}
}
}
}
ViewState["SelectedRecords"] = arr;
}
VB.Net
Private Sub GetData()
Dim arr As ArrayList
If ViewState("SelectedRecords") IsNot Nothing Then
arr = DirectCast(ViewState("SelectedRecords"), ArrayList)
Else
arr = New ArrayList()
End If
Dim chkAll As CheckBox = DirectCast(gvAll.HeaderRow _
.Cells(0).FindControl("chkAll"), CheckBox)
For i As Integer = 0 To gvAll.Rows.Count - 1
If chkAll.Checked Then
If Not arr.Contains(gvAll.DataKeys(i).Value) Then
arr.Add(gvAll.DataKeys(i).Value)
End If
Else
Dim chk As CheckBox = DirectCast(gvAll.Rows(i).Cells(0) _
.FindControl("chk"), CheckBox)
If chk.Checked Then
If Not arr.Contains(gvAll.DataKeys(i).Value) Then
arr.Add(gvAll.DataKeys(i).Value)
End If
Else
If arr.Contains(gvAll.DataKeys(i).Value) Then
arr.Remove(gvAll.DataKeys(i).Value)
End If
End If
End If
Next
ViewState("SelectedRecords") = arr
End Sub
GetData is called in the Page Load event of the ASP.Net web page in the following way
C#
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
GetData();
BindGrid();
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If IsPostBack Then
GetData()
End If
BindGrid()
End Sub
2. SetData
The SetData function simply restores the saved state of the checkboxes from the ViewState
C#
private void SetData()
{
int currentCount = 0;
CheckBox chkAll = (CheckBox)gvAll.HeaderRow
.Cells[0].FindControl("chkAll");
chkAll.Checked = true;
ArrayList arr = (ArrayList)ViewState["SelectedRecords"];
for (int i = 0; i < gvAll.Rows.Count; i++)
{
CheckBox chk = (CheckBox)gvAll.Rows[i]
.Cells[0].FindControl("chk");
if (chk != null)
{
chk.Checked = arr.Contains(gvAll.DataKeys[i].Value);
if (!chk.Checked)
chkAll.Checked = false;
else
currentCount++;
}
}
hfCount.Value = (arr.Count - currentCount).ToString();
}
VB.Net
Private Sub SetData()
Dim currentCount As Integer = 0
Dim chkAll As CheckBox = DirectCast(gvAll.HeaderRow _
.Cells(0).FindControl("chkAll"), CheckBox)
chkAll.Checked = True
Dim arr As ArrayList = DirectCast(ViewState("SelectedRecords") _
, ArrayList)
For i As Integer = 0 To gvAll.Rows.Count - 1
Dim chk As CheckBox = DirectCast(gvAll.Rows(i).Cells(0) _
.FindControl("chk"), CheckBox)
If chk IsNot Nothing Then
chk.Checked = arr.Contains(gvAll.DataKeys(i).Value)
If Not chk.Checked Then
chkAll.Checked = False
Else
currentCount += 1
End If
End If
Next
hfCount.Value = (arr.Count - currentCount).ToString()
End Sub
The SetData method is called up in the OnPageIndexChanging event of the ASP.Net GridView in the following way
C#
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
gvAll.PageIndex = e.NewPageIndex;
gvAll.DataBind();
SetData();
}
VB.Net
Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
gvAll.PageIndex = e.NewPageIndex
gvAll.DataBind()
SetData()
End Sub
Deleting multiple selected rows
Now when the delete button is clicked by the user the following event if triggered
C#
protected void btnDelete_Click(object sender, EventArgs e)
{
int count = 0;
SetData();
gvAll.AllowPaging = false;
gvAll.DataBind();
ArrayList arr = (ArrayList)ViewState["SelectedRecords"];
count = arr.Count;
for (int i = 0; i < gvAll.Rows.Count; i++)
{
if (arr.Contains(gvAll.DataKeys[i].Value))
{
DeleteRecord(gvAll.DataKeys[i].Value.ToString());
arr.Remove(gvAll.DataKeys[i].Value);
}
}
ViewState["SelectedRecords"] = arr;
hfCount.Value = "0";
gvAll.AllowPaging = true;
BindGrid();
ShowMessage(count);
}
VB.Net
Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim count As Integer = 0
SetData()
gvAll.AllowPaging = False
gvAll.DataBind()
Dim arr As ArrayList = DirectCast(ViewState("SelectedRecords") _
, ArrayList)
count = arr.Count
For i As Integer = 0 To gvAll.Rows.Count - 1
If arr.Contains(gvAll.DataKeys(i).Value) Then
DeleteRecord(gvAll.DataKeys(i).Value.ToString())
arr.Remove(gvAll.DataKeys(i).Value)
End If
Next
ViewState("SelectedRecords") = arr
hfCount.Value = "0"
gvAll.AllowPaging = True
BindGrid()
ShowMessage(count)
End Sub
In
the above event handler I am simply looping through the GridView and
checking if the record exists in the ArrayList, if the record exists
then it is simply deleted using the DeleteRecord method. Finally I display the a JavaScript Alert Box to notify the user that the delete operation is finished using the ShowMessage method
DeleteRecord Method
The delete record method simply deletes the record from the database table based on CustomerID.
C#
private void DeleteRecord(string CustomerID)
{
string constr = ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
string query = "delete from TestCustomers " +
"where CustomerID=@CustomerID";
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@CustomerID", CustomerID);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
VB.Net
Private Sub DeleteRecord(ByVal CustomerID As String)
Dim constr As String = ConfigurationManager _
.ConnectionStrings("conString").ConnectionString
Dim query As String = "delete from TestCustomers where" & _
" CustomerID=@CustomerID"
Dim con As New SqlConnection(constr)
Dim cmd As New SqlCommand(query, con)
cmd.Parameters.AddWithValue("@CustomerID", CustomerID)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Sub
ShowMessage Method
The ShowMessage method simply displays the amount of records deleted through a JavaScript Alert
C#
private void ShowMessage(int count)
{
StringBuilder sb = new StringBuilder();
sb.Append("<script type = 'text/javascript'>");
sb.Append("alert('");
sb.Append(count.ToString());
sb.Append(" records deleted.');");
sb.Append("</script>");
ClientScript.RegisterStartupScript(this.GetType(),
"script", sb.ToString());
}
VB.Net
Private Sub ShowMessage(ByVal count As Integer)
Dim sb As New StringBuilder()
sb.Append("<script type = 'text/javascript'>")
sb.Append("alert('")
sb.Append(count.ToString())
sb.Append(" records deleted.');")
sb.Append("</script>")
ClientScript.RegisterStartupScript(Me.GetType(), _
"script", sb.ToString())
End Sub
Client Side Scripting
I have made use of some JavaScript methods for the following methods
1. Check-all checkbox functionality
2. Confirmation before delete.
Check-all checkbox functionality
Check-all checkbox functionality has already explained by me in past. You can simply refer my article
Confirmation before delete
It
would be a great feature to display a confirm box to the user stating
that these many rows or records selected by you will be deleted. If the
user presses OK delete operation will be performed else not. The
following JavaScript function does it for us.
<script type = "text/javascript">
function ConfirmDelete()
{
var count = document.getElementById("<%=hfCount.ClientID %>").value;
var gv = document.getElementById("<%=gvAll.ClientID%>");
var chk = gv.getElementsByTagName("input");
for(var i=0;i<chk.length;i++)
{
if(chk[i].checked && chk[i].id.indexOf("chkAll") == -1)
{
count++;
}
}
if(count == 0)
{
alert("No records to delete.");
return false;
}
else
{
return confirm("Do you want to delete " + count + " records.");
}
}
</script>
