How to delete multiple rows in a GridView using Asp.net

Please visit my new Web Site https://coderstechzone.com

You knew that GridView allow us to delete a single row at a time. Here i would like to show you "How we can remove multiple GridView rows like Gmail Deletion". First of all i assume that you can Bind Sql server data into a GridView. So to make easier this article i will show only the delete operation. Plus i will discuss on deletion problem of master child data later on this article.To do that add an extra template column in GridView to give the multiple selection to the user. User will check the checkboxes to make his multiple selection for deletion. The GridView UI HTML code looks like:

Fig: Sample UI

<asp:GridView runat="server" ID="gvBrand" DataKeyNames="ID">
<Columns>
<asp:TemplateField HeaderText="Select">
<ItemTemplate>
<asp:CheckBox runat="server" ID="chk"/>
</ItemTemplate>
<ItemStyle BackColor="#DCE8FA" HorizontalAlign="Center" />
<HeaderStyle HorizontalAlign="Center" />
<HeaderTemplate>
<input id="chkAll" onclick="javascript:GridSelectAllColumn(this, 'chk');" runat="server" type="checkbox" value="" />
</HeaderTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Name" HeaderText="Name">
</asp:BoundField>
<asp:BoundField DataField="Status" HeaderText="Status">
</asp:BoundField>
</Columns>
</asp:GridView>

<asp:Button runat="server" ID="cmdDlete" Text="Delete"
OnClientClick="return confirm('Are you sure to delete?')" OnClick="cmdDlete_Click" />

CLICK HERE to read How to implement select all within the GridView.

Now look at the cmdDelete button HTML code that here i added a property named OnClientClick to prompt message "Are you sure to delete?" the user before deletion. If user click on OK then the server side event will raise & delete multiple rows from the GridView as well as from databse server. So now look at the codeto delete multiple rows from GridView:

protected void cmdDlete_Click(object sender, EventArgs e)
{
DBUtility oUtility = new DBUtility();
if (oUtility.PerformDelete(gvBrand,"tblBrand"))
RefreshGridView();
}

Here i need to explain a bit more how the above code will work. To make the above code workable you need to add a class named DBUtility in your project & write the method named PerformDelete(). The class file code is given below:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI.WebControls;

public class DBUtility
{

public DBUtility(){}

public bool PerformDelete(GridView GV,string sTableName)
{
bool bSaved = false;
string sClause = "''";
string sSQL = "";
string sConstr = "";
SqlConnection Conn;
SqlCommand comm;

sConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
foreach (GridViewRow oItem in GV.Rows)
{
if (((CheckBox)oItem.FindControl("chk")).Checked)
sClause += "," + GV.DataKeys[oItem.DataItemIndex].Value;
}

sSQL = "DELETE FROM " + sTableName + " WHERE " + GV.DataKeyNames.GetValue(0) + " IN(" + sClause + ")";
Conn = new SqlConnection(sConstr);
using (Conn)
{
try
{
Conn.Open();
comm = new SqlCommand(sSQL, Conn);
using (comm)
{
comm.CommandTimeout = 0;
comm.ExecuteNonQuery();
bSaved = true;
}
}
catch (Exception Ex)
{
bSaved = false;
// You can through error from here.
}
}

return bSaved;
}
}

Why i add this extra class to delete multiple rows from griview? Because most of the every developers need to do this in the maximum number of pages of a project. If i can write a common method to handle each deletion then it will keep our code clean & more managable. I knew that lot of architecture now available to ensure the reusability of code but for a beginner i think the above stated way is more easier. After that he can incorporate the above code segment into his DAL. For an example: I want to develop an Ecommerce site where i will sale different type of products. If i don't consider product stock then only the order module or shopping cart is the transactional page but the rest of the pages were basic data entry page like Category, Brand, Customer, Product, Product variation, Kit etc. In all of this page either in user or admin we need to add Multiple Delete Functionality. And here you just instantiate the DBUtility object & pass the GridView to the PerformDelete() method. Thats it just two lines of code will take care deletion issues.

Another concern:
Most the times developer has a problem to delete master table data since there is relation between master child table. Look at my above example if i have a reference table named Product to Brand table then you can not delete Brand table data. You will receive the below error:

The DELETE statement conflicted with the REFERENCE constraint "FK_tblProduct_tblBrand". The conflict occurred in database "XXXX", table "dbo.tblProduct", column 'BrandID'.
The statement has been terminated.

Fig: Relationship between Brand & Product

Hope now you can understand why error occured. To resolve this issue we have two options:
1. Add ON DELETE CASCADE constraint.
2. Delete first child data.

Add ON DELETE CASCADE constraint:
If you add ON DELETE CASCADE constraint into a table then child data automatically deleted when user perform any operation on its master table. This is easy but a bit risky. To do that first DROP the constarint like:

ALTER TABLE tblProduct
DROP CONSTRAINT FK_tblProduct_tblBrand

Now add ON DELETE CASCADE constraint in the following way:

ALTER TABLE tblProduct
ADD CONSTRAINT FK_tblProduct_tblBrand
FOREIGN KEY (BrandID)
REFERENCES tblBrand (ID) ON DELETE CASCADE

First delete child data:
Use the above technique by giving a Brand filtering option in the product page.

Hope now you can handle each issues on deletion for all basic entry pages.