Monday, July 27, 2009

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




Please visit my new Web Site WWW.Codedisplay.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.

2 comments:

Anonymous said...

can you explain me, how to delete a single row in asp.net grid view. but i don't have any primary key on that table. so if the user press the button then the particular field should be deleted..
i tried but the error was displayed as " must declare the Scalar variable"
here i cant specify a DataKeyName know?
Thank you...

Shawpnendu said...

HI Anonymous,
Since you can display data in GridView so that i can assume that you can filter your data based on combination of more than one column. Am i right?

If so then add all criteia column(which makes the row unique) in your GridView & if you dont want to display those columns then hide using the below CSS:
.hide { display:none; }

Sample Gridview Column:
<asp:BoundField DataField="ID" HeaderText="Name" >
<ItemStyle CssClass="hide" />
<HeaderStyle CssClass="hide" />
</asp:BoundField>

Now use cell[0...n] text of corresponding gridview row to generate sql query which will delete record from database.

You can read my post on "Delete or remove duplicate rows from SQL Server ta..." under June 2009 archieve.

Let me know if any other issue.

Want to say something?
I WOULD BE DELIGHTED TO HEAR FROM YOU

Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Asp.net
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
     
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash