How to delete multiple rows from GridView with Checkboxes

In this article, I am going to explain how to delete one or more rows from GridView.  User can select the rows he wants to delete by selecting individual checkboxes or he can select all rows by selecting the checkbox in the header row.  We also show a confirm popup before proceeding with the delete operation.

Database Connection:
I am using AdventureWorks database for this example. Open web.config and add following entry to connection strings element

<add name="Sql" connectionString="Data Source=<SERVERNAME>;
     Initial Catalog= TEST;User=testuser;Password=testuser;" providerName="System.Data.SqlClient"/> 

DataSource
The SqlDataSource data source control is simply a control that represents data in an SQL relational database to data-bound controls. You can use the SqlDataSource control in conjunction with a data-bound control such as Dropdownlist, gridview etc to retrieve data from a relational database and to display, edit, and sort data on a Web page with little or no code. The ConnectionString property of the SqlDataSource control specifies how to connect to the database. This property can be a hard-coded connection string or can point to a connection string in a Web.config file as shown in the code given above. The SelectCommand property specifies the SQL statement to execute to retrieve the data. The The DeleteCommand represents an SQL query (or stored procedure name), and is used by the Delete method of the SqlDataSource control. You can set the parameters used by the DeleteCommand by populating the DeleteParameters element. In our case it is a comma delimited string of ProductIDs.

<asp:SqlDataSource ID="sqlDSProducts" runat="server" 
    ConnectionString="<%$ ConnectionStrings:Sql %>"
    SelectCommand="Select top 10 * from Production.Product ORDER BY ProductID desc"
    DeleteCommand="DELETE FROM Production.Product  WHERE ProductID in ( SELECT * FROM dbo.[udf_CsvToInt](@ProductIDs) )">
    <DeleteParameters><asp:Parameter Name="ProductIDs" DbType="String" /></DeleteParameters>
</asp:SqlDataSource>

Page Design
Create a new ASP.NET web page. Rename Default.aspx to DeleteProductsView.aspx and update any references.  We are not allowing user to perform any updates. So we render ProductID, Product Number, Product Name & Price in BoundColumns.

<asp:BoundField DataField="ProductID" HeaderText="Product ID"/>
<asp:BoundField DataField="ProductNumber" HeaderText="Product Number"/>
<asp:BoundField DataField="Name" HeaderText="Product Name"/>
<asp:BoundField DataField="ListPrice" HeaderText="Price" DataFormatString="{0:C}">
  <ItemStyle HorizontalAlign="Right" />
</asp:BoundField>

A checkbox control is added to the grid rendered in a TemplateField as shown below both at the header level and row level.  User would select the row level checkbox and hits delete to delete individual products. If he wants to delete all the products, he would select the checkbox on the header which would select checkboxes in all the rows.  If he hits delete now, all selected rows will be deleted.

<asp:TemplateField>
     <HeaderStyle HorizontalAlign="Center" />
     <HeaderTemplate>                        
        <input type="checkbox" name="cbHeaderSelect" onclick="SelectAll(this)">
    </HeaderTemplate>
    <ItemTemplate>
        <asp:CheckBox ID="cbSelProduct" runat="server"/>
    </ItemTemplate>
</asp:TemplateField>

When user clicks on the checkbox in the header column, SelectAll javascript function is invoked and checkbox control itself is passed as an argument. In the function, we initially get reference to the gridview and later iterate through all the rows in the gridview and will apply the uncheck/checked value to all the checkboxes in those rows.

function SelectAll(ctlCheckBox) {

     var checkedValue;
     if (ctlCheckBox.checked == true) {
         checkedValue = true;
     }
     else
         checkedValue = false;

     //get reference to the products gridview control
     var ctlProductsGridView = document.getElementById("<%= this.gvProducts.ClientID %>");
     
     //variable to contain the cell of the grid
     var cell;

     if (ctlProductsGridView.rows.length > 0) {
     
         //loop starts from 1. rows[0] points to the header.
         for (rowIndex = 1; rowIndex < ctlProductsGridView.rows.length; rowIndex++) {
         
             //get the reference to the first column, this is where checkbox is rendered
             cell = ctlProductsGridView.rows[rowIndex].cells[0];

             //loop through all elements in the cell
             for (j = 0; j < cell.childNodes.length; j++) {
             
                 //check if the current element type is checkbox
                 if (cell.childNodes[j].type == "checkbox") {
                     //assign the checked status
                     cell.childNodes[j].checked = checkedValue;
                 }
             }
         }
     }
 }

I have added a simple link button “btnDelete” to the page. Clicking on this button would initially ask for user’s confirmation before sending the request to the server.

<asp:LinkButton ID="btnDelete" runat="server" Text="Delete" 
OnClientClick = 'return confirm("Are you sure you want to delete this entry?");'
OnClick="Delete"/>

Added a message label to notify user about the details of product he has deleted.

<i><asp:Label ID="lblMessage" runat="server"></asp:Label></i>

When page is first requested, Page_Load event is one of the events that get fired on the server side. We reset the message label to empty.  You would notice that we are not fetching any products from database and binding to the grid as we normally do in traditional way. Instead we have set the Datasourceid of the gridview to sqlDSproducts in the design code. The datasource control will execute the query specified in the SelectCommand property of the control and binds the data to the gridview.

protected void Page_Load(object sender, EventArgs e)
{
    lblMessage.Text = String.Empty;
}

When the user clicks on the “Delete” link button,  we show a JAVASCRIPT confirmation box before we submit the page to the postback. Only if the user hits on the “OK” button request will be sent to the server. On the server, “DeleteProducts” event handler is raised in response to the button click. In DeleteProducts function, we iterate through all the gridview rows and will make a list of all products that were selected to be deleted. We set this list to the “ProductIDs” delete parameter of the data source control and will invoke its Delete method to execute the delete query against the database to perform delete.

protected void Delete(object sender, EventArgs e)
{
    Hashtable productList = new Hashtable();
    foreach (GridViewRow row in gvProducts.Rows)
    {
        // Get the checkbox control that contain the user selection. 
        CheckBox cbSelProducts = (CheckBox)row.FindControl("cbSelProduct");
        if (cbSelProducts != null && cbSelProducts.Checked)
        {
            Label lblProductName = (Label)row.FindControl("lblProductName");
            /// Get the product id of the selected product             
            productList.Add(gvProducts.DataKeys[row.RowIndex].Value.ToString(), row.Cells[3].Text);
        }
    }

    if (productList.Count > 0)
    {
        //    DeleteProduct(productList);
        sqlDSProducts.DeleteParameters[0].DefaultValue = ToDelimString(productList, ",");
        sqlDSProducts.Delete();
    }

    //add the list of products that were deleted to a message label
    lblMessage.Text = "Following products were deleted";
    foreach (string key in productList.Keys)
    {
        lblMessage.Text += String.Format("<br/> ProductID: {0}; ProductName: {1}", key, productList[key]);
    }

    //refresh grid
    gvProducts.DataBind();
}

public static string ToDelimString(Hashtable input, string delimiter)
{
    return string.Join(delimiter, (from string name in input.Keys select name).ToArray());
}

SQL:
I have created a simple split function to convert a comma delimited string to a table of ID’s.

CREATE FUNCTION [dbo].[udf_CsvToInt]
( 
   @Array VARCHAR(max)
) 
RETURNS @IntTable TABLE (IntValue INT)
AS
BEGIN
  IF @Array <> '' BEGIN
    DECLARE @separator char(1)
    SET @separator = ','

    DECLARE @separator_position INT 
    DECLARE @array_value VARCHAR(2000) 

    SET @array = @array + ','
    WHILE patindex('%,%' , @array) <> 0 BEGIN
      SELECT @separator_position =  patindex('%,%' , @array)
      SELECT @array_value = LEFT(@array, @separator_position - 1)
      INSERT @IntTable Values (CAST(@array_value AS INT))
      SELECT @array = stuff(@array, 1, @separator_position, '')
    END
  END
RETURN
end

NOTE: Production.Product in AdventureWorks table has dependencies to other tables. Since the focus of this article is not about cascaded deletes, I simple populated some dummy data into that table by running following query.

INSERT INTO [AdventureWorks].[Production].[Product]
           ([Name],
           [ProductNumber],
           [MakeFlag],[FinishedGoodsFlag],[Color],[SafetyStockLevel],[ReorderPoint]
           ,[StandardCost],[ListPrice],[Size],[SizeUnitMeasureCode]
           ,[WeightUnitMeasureCode],[Weight],[DaysToManufacture],[ProductLine]
           ,[Class],[Style],[ProductSubcategoryID],[ProductModelID],[SellStartDate]
           ,[SellEndDate],[DiscontinuedDate]
           ,[rowguid],[ModifiedDate])
     
     SELECT [Name] + ' ' + Convert(varchar, ABS(CAST(NEWID() AS binary(6)) %1000) + 1)
           ,[ProductNumber] + ' ' + Convert(varchar, ABS(CAST(NEWID() AS binary(6)) %1000) + 1)
           ,[MakeFlag],[FinishedGoodsFlag],[Color],[SafetyStockLevel],[ReorderPoint]
           ,[StandardCost],[ListPrice],[Size],[SizeUnitMeasureCode]
           ,[WeightUnitMeasureCode],[Weight],[DaysToManufacture],[ProductLine]
           ,[Class],[Style],[ProductSubcategoryID],[ProductModelID],[SellStartDate]
           ,[SellEndDate],[DiscontinuedDate]
           ,NEWID(),[ModifiedDate]
     FROM Production.Product WHERE ProductID = 1

Run the application and top 10 products ordered by product (desc) are loaded as shown below:

Click on the checkbox button in the header row and all checkboxes in all the rows will be checked too.

Click on “Delete” button and you would see a confirmation pop-up as shown below:

Clicking “OK” on the pop-up will delete the product from the database. You would also see the details of the product that is deleted.

Complete Page Design Code:

<html>
<head runat="server">
    <title>Delete Products</title>
     <link type="text/css" rel="Stylesheet" href="DeleteProductsViewWithConf_2.css" />
     <script type="text/javascript">         
         function SelectAll(ctlCheckBox) {

             var checkedValue;
             if (ctlCheckBox.checked == true) {
                 checkedValue = true;
             }
             else
                 checkedValue = false;

             //get reference to the products gridview control
             var ctlProductsGridView = document.getElementById("<%= this.gvProducts.ClientID %>");
             
             //variable to contain the cell of the grid
             var cell;

             if (ctlProductsGridView.rows.length > 0) {
             
                 //loop starts from 1. rows[0] points to the header.
                 for (rowIndex = 1; rowIndex < ctlProductsGridView.rows.length; rowIndex++) {
                 
                     //get the reference to the first column, this is where checkbox is rendered
                     cell = ctlProductsGridView.rows[rowIndex].cells[0];

                     //loop through all elements in the cell
                     for (j = 0; j < cell.childNodes.length; j++) {
                     
                         //check if the current element type is checkbox
                         if (cell.childNodes[j].type == "checkbox") {
                             //assign the checked status
                             cell.childNodes[j].checked = checkedValue;
                         }
                     }
                 }
             }
         }
</script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
         <asp:SqlDataSource ID="sqlDSProducts" runat="server" 
            ConnectionString="<%$ ConnectionStrings:Sql %>"
            SelectCommand="Select top 10 * from Production.Product ORDER BY ProductID desc"
            DeleteCommand="DELETE FROM Production.Product 
                    WHERE ProductID in ( SELECT * FROM dbo.[udf_CsvToInt](@ProductIDs) )">
            <DeleteParameters>
                <asp:Parameter Name="ProductIDs" DbType="String" />
            </DeleteParameters>
        </asp:SqlDataSource>
        <asp:gridview id="gvProducts"               
            autogeneratecolumns="False"                  
            runat="server" DataKeyNames="ProductID"
            DataSourceID="sqlDSProducts"
            CssClass="GridViewStyle">
          <RowStyle CssClass="RowStyle" />              
          <FooterStyle CssClass="RowStyle" />                        
          <SelectedRowStyle CssClass="SelectedRowStyle" />    
          <HeaderStyle CssClass="HeaderStyle" />              
          <AlternatingRowStyle CssClass="AltRowStyle" />        
          <Columns>
                 <asp:TemplateField>
                     <HeaderStyle HorizontalAlign="Center" />
                     <HeaderTemplate>                        
                        <input type="checkbox" name="cbHeaderSelect" onclick="SelectAll(this)">
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:CheckBox ID="cbSelProduct" runat="server"/>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="ProductID" HeaderText="Product ID"/>
                <asp:BoundField DataField="ProductNumber" HeaderText="Product Number"/>
                <asp:BoundField DataField="Name" HeaderText="Product Name"/>
                <asp:BoundField DataField="ListPrice" HeaderText="Price" DataFormatString="{0:C}">
                    <ItemStyle HorizontalAlign="Right" />
                </asp:BoundField>
            </Columns>
        </asp:gridview>
<br />    
<asp:LinkButton ID="btnDelete" runat="server" Text="Delete" CommandName="Delete" 
OnClientClick = 'return confirm("Are you sure you want to delete this entry?");'
OnClick="Delete"/>
<br />
<i><asp:Label ID="lblMessage" runat="server"></asp:Label></i>
    </div>
    </form>
</body>
</html>

Be Sociable, Share!

•