Deleting Multiple Rows in a GridView

If you have used Hotmail or any other similar email client, you might have observed that  we have the option of selecting multiple rows (using the checkbox provided) and perform a set of operations on them. In this article, we will replicate this scenario for a gridview. A gridview allows us to delete only a single row at a time. We will extend this functionality to select multiple rows and delete all of the selected rows in a single stroke. In this article, I assume that you are aware of creating asp.net web applications and have worked with gridview.

The sample makes use of the Northwind database. We will be pulling data from the Employee table. For this sample to work, drop all the Foreign Key relationships on the Employee Table. To do so, in Sql Server Management Studio, browse to the Northwind database and open the Employee table in design view. Right click in the Table designer on the right hand side and choose ‘Relationships’. Select all the relationships like FK_Orders_Employees,  FK_EmployeeTerritories_Employees etc and delete them. This step is necessary as we will get a constraint violation exception if we do not do so.

Once we are through with the task of removing the relationships in the Employee table, let us explore the steps to create a gridview with functionality to delete multiple rows at a time.

Perform the following steps :

Step 1: Create an .aspx page and add a GridView and a SqlDataSource control to it.

Step 2: Configure the connection of SqlDataSource to point to the Northwind database.  Create queries for the Select and Delete commands. The resultant code will look similar as given below :

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"

    SelectCommand="SELECT EmployeeID, LastName, City FROM Employees"

    DeleteCommand="DELETE FROM Employees WHERE [EmployeeID] = @EmployeeID"

    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" >

       <DeleteParameters>

           <asp:Parameter Name="EmployeeID" />

       </DeleteParameters>

</asp:SqlDataSource>                                   

Step 3: Once the SqlDataSource has been configured, bind the gridview with this data source.

Step 4: To create a checkbox in each row, follow these steps:

1.    Create a TemplateField inside the <Columns> to add custom content to each column.

2.    Inside the TemplateField, create an ItemTemplate with a CheckBox added to it.

<asp:TemplateField>

       <ItemTemplate>

             <asp:CheckBox ID="chkRows" runat="server"/>

      </ItemTemplate>

 </asp:TemplateField>

This will add a checkbox to each row in the grid.

Step 5: Add a button control, and rename it to btnMultipleRowDelete.

The resultant markup in the design view will look similar to the code below :

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">

  

   <Columns>

      <asp:TemplateField>

          <ItemTemplate>

            <asp:CheckBox ID="cbRows" runat="server"/>

          </ItemTemplate>

       </asp:TemplateField>

 

<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" />

<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />

<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />

   </Columns>

</asp:GridView>

 

<asp:SqlDataSource ID="SqlDataSource1" Runat="server"

SelectCommand="SELECT EmployeeID, LastName, City FROM Employees"

DeleteCommand="DELETE FROM Employees WHERE [EmployeeID] = @EmployeeID"

ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" >

   <DeleteParameters>

       <asp:Parameter Name="EmployeeID" />

   </DeleteParameters>

</asp:SqlDataSource>

 

<asp:Button

   ID="btnMultipleRowDelete"

   OnClick="btnMultipleRowDelete_Click"

   runat="server"

   Text="Delete Rows" />

 

In Code behind file (.cs) for C# and (.vb) for VB.NET, code the button click event. Our code will first loop through all the rows in the GridView. If a row is checked, the code retrieves the EmployeeID and passes the selected value to the Delete Command.

C#

 

 

protected void btnMultipleRowDelete_Click(object sender, EventArgs e)

{

        // Looping through all the rows in the GridView

        foreach (GridViewRow row in GridView1.Rows)

        {

            CheckBox checkbox = (CheckBox)row.FindControl("cbRows");

 

            //Check if the checkbox is checked.

//value in the HtmlInputCheckBox's Value property is set as the //value of the delete command's parameter.

            if (checkbox.Checked)

            {

                // Retreive the Employee ID

int employeeID = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value);

// Pass the value of the selected Employye ID to the Delete //command.

SqlDataSource1.DeleteParameters["EmployeeID"].DefaultValue = employeeID.ToString();

                SqlDataSource1.Delete();

            }

        }

 }

 

VB.NET

Protected Sub btnMultipleRowDelete_Click(ByVal sender As Object, ByVal e As EventArgs)
' Looping through all the rows in the GridView

For Each row As GridViewRow In GridView1.Rows
Dim checkbox As CheckBox = CType(row.FindControl("cbRows"), CheckBox)
'Check if the checkbox is checked.
'value in the HtmlInputCheckBox's Value property is set as the //value of the delete command's parameter.



If checkbox.Checked Then' Retreive the Employee IDDim employeeID As Integer = Convert.ToInt32(GridView1.DataKeys(row.RowIndex).Value)' Pass the value of the selected Employye ID to the Delete //command.SqlDataSource1.DeleteParameters("EmployeeID").DefaultValue = employeeID.ToString()
SqlDataSource1.Delete()
End IfNext rowEnd Sub

Run the code, and select a few rows in the grid. ‘Delete Rows’ button, the selected rows get deleted. Rather than deleting rows one at a time, deleting them in a batch is a good practice. I would encourage you to read Scott Mitchell’s article for the same.

 

Conclusion

 

By default, the gridview provides the functionality to delete a single row at a time. In this article, we explored how to extend the functionality of the grid view and delete multiple rows. I hope this article was useful and I thank you for viewing it.