Batch Deleting (VB)
Learn how to delete multiple database records in a single operation. In the User Interface Layer we build upon an enhanced GridView created in an earlier tutorial. In the Data Access Layer we wrap the multiple Delete operations within a transaction to ensure that all deletions succeed or all deletions are rolled back.
Introduction
The preceding tutorial explored how to create a batch editing interface using a fully-editable GridView. In situations where users are commonly editing many records at once, a batch editing interface will require far fewer postbacks and keyboard-to-mouse context switches, thereby improving the end user s efficiency. This technique is similarly useful for pages where it is common for users to delete many records in one go.
Anyone who has used an online email client is already familiar with one of the most common batch deleting interfaces: a checkbox in each row in a grid with a corresponding Delete All Checked Items button (see Figure 1). This tutorial is rather short because we ve already done all of the hard work in previous tutorials in creating both the web-based interface and a method to delete a series of records as a single atomic operation. In the Adding a GridView Column of Checkboxes tutorial we created a GridView with a column of checkboxes and in the Wrapping Database Modifications within a Transaction tutorial we created a method in the BLL that would use a transaction to delete a List<T> of ProductID values. In this tutorial, we will build upon and merge our previous experiences to create a working batch deleting example.
Figure 1: Each Row Includes a Checkbox (Click to view full-size image)
Step 1: Creating the Batch Deleting Interface
Since we already created the batch deleting interface in the Adding a GridView Column of Checkboxes tutorial, we can simply copy it to BatchDelete.aspx rather than creating it from scratch. Start by opening the BatchDelete.aspx page in the BatchData folder and the CheckBoxField.aspx page in the EnhancedGridView folder. From the CheckBoxField.aspx page, go to the Source view and copy the markup between the <asp:Content> tags as shown in Figure 2.
Figure 2: Copy the Declarative Markup of CheckBoxField.aspx to the Clipboard (Click to view full-size image)
Next, go to the Source view in BatchDelete.aspx and paste the contents of the clipboard within the <asp:Content> tags. Also copy and paste the code from within the code-behind class in CheckBoxField.aspx.vb to within the code-behind class in BatchDelete.aspx.vb (the DeleteSelectedProducts Button s Click event handler, the ToggleCheckState method, and the Click event handlers for the CheckAll and UncheckAll Buttons). After copying over this content, the BatchDelete.aspx page s code-behind class should contain the following code:
Partial Class BatchData_BatchDelete
Inherits System.Web.UI.Page
Protected Sub DeleteSelectedProducts_Click(sender As Object, e As EventArgs) _
Handles DeleteSelectedProducts.Click
Dim atLeastOneRowDeleted As Boolean = False
' Iterate through the Products.Rows property
For Each row As GridViewRow In Products.Rows
' Access the CheckBox
Dim cb As CheckBox = row.FindControl("ProductSelector")
If cb IsNot Nothing AndAlso cb.Checked Then
' Delete row! (Well, not really...)
atLeastOneRowDeleted = True
' First, get the ProductID for the selected row
Dim productID As Integer = _
Convert.ToInt32(Products.DataKeys(row.RowIndex).Value)
' "Delete" the row
DeleteResults.Text &= String.Format _
("This would have deleted ProductID {0}<br />", productID)
'... To actually delete the product, use ...
' Dim productAPI As New ProductsBLL
' productAPI.DeleteProduct(productID)
'............................................
End If
Next
' Show the Label if at least one row was deleted...
DeleteResults.Visible = atLeastOneRowDeleted
End Sub
Private Sub ToggleCheckState(ByVal checkState As Boolean)
' Iterate through the Products.Rows property
For Each row As GridViewRow In Products.Rows
' Access the CheckBox
Dim cb As CheckBox = row.FindControl("ProductSelector")
If cb IsNot Nothing Then
cb.Checked = checkState
End If
Next
End Sub
Protected Sub CheckAll_Click(sender As Object, e As EventArgs) _
Handles CheckAll.Click
ToggleCheckState(True)
End Sub
Protected Sub UncheckAll_Click(sender As Object, e As EventArgs) _
Handles UncheckAll.Click
ToggleCheckState(False)
End Sub
End Class
After copying over the declarative markup and source code, take a moment to test BatchDelete.aspx by viewing it through a browser. You should see a GridView listing the first ten products in a GridView with each row listing the product s name, category, and price along with a checkbox. There should be three buttons: Check All, Uncheck All, and Delete Selected Products. Clicking the Check All button selects all checkboxes, while Uncheck All clears all checkboxes. Clicking Delete Selected Products displays a message that lists the ProductID values of the selected products, but does not actually delete the products.
Figure 3: The Interface from CheckBoxField.aspx has been Moved to BatchDeleting.aspx (Click to view full-size image)
Step 2: Deleting the Checked Products Using Transactions
With the batch deleting interface successfully copied over to BatchDeleting.aspx, all that remains is to update the code so that the Delete Selected Products button deletes the checked products using the DeleteProductsWithTransaction method in the ProductsBLL class. This method, added in the Wrapping Database Modifications within a Transaction tutorial, accepts as its input a List(Of T) of ProductID values and deletes each corresponding ProductID within the scope of a transaction.
The DeleteSelectedProducts Button s Click event handler currently uses the following For Each loop to iterate through each GridView row:
' Iterate through the Products.Rows property
For Each row As GridViewRow In Products.Rows
' Access the CheckBox
Dim cb As CheckBox = row.FindControl("ProductSelector")
If cb IsNot Nothing AndAlso cb.Checked Then
' Delete row! (Well, not really...)
atLeastOneRowDeleted = True
' First, get the ProductID for the selected row
Dim productID As Integer = _
Convert.ToInt32(Products.DataKeys(row.RowIndex).Value)
' "Delete" the row
DeleteResults.Text &= String.Format _
("This would have deleted ProductID {0}<br />", productID)
'... To actually delete the product, use ...
' Dim productAPI As New ProductsBLL
' productAPI.DeleteProduct(productID)
'............................................
End If
Next
For each row, the ProductSelector CheckBox Web control is programmatically referenced. If it is checked, the row s ProductID is retrieved from the DataKeys collection and the DeleteResults Label s Text property is updated to include a message indicating that the row was selected for deletion.
The above code does not actually delete any records as the call to the ProductsBLL class s Delete method is commented out. Were this delete logic to be applied, the code would delete the products but not within an atomic operation. That is, if the first few deletes in the sequence succeeded, but a later one failed (perhaps due to a foreign key constraint violation), an exception would be thrown but those products already deleted would remain deleted.
In order to assure atomicity, we need to instead use the ProductsBLL class s DeleteProductsWithTransaction method. Because this method accepts a list of ProductID values, we need to first compile this list from the grid and then pass it as a parameter. We first create an instance of a List(Of T) of type Integer. Within the For Each loop we need to add the selected products ProductID values to this List(Of T). After the loop this List(Of T) must be passed to the ProductsBLL class s DeleteProductsWithTransaction method. Update the DeleteSelectedProducts Button s Click event handler with the following code:
Protected Sub DeleteSelectedProducts_Click(sender As Object, e As EventArgs) _
Handles DeleteSelectedProducts.Click
' Create a List to hold the ProductID values to delete
Dim productIDsToDelete As New System.Collections.Generic.List(Of Integer)
' Iterate through the Products.Rows property
For Each row As GridViewRow In Products.Rows
' Access the CheckBox
Dim cb As CheckBox = CType(row.FindControl("ProductSelector"), CheckBox)
If cb IsNot Nothing AndAlso cb.Checked Then
' Save the ProductID value for deletion
' First, get the ProductID for the selected row
Dim productID As Integer = _
Convert.ToInt32(Products.DataKeys(row.RowIndex).Value)
' Add it to the List...
productIDsToDelete.Add(productID)
' Add a confirmation message
DeleteResults.Text &= String.Format _
("ProductID {0} has been deleted<br />", productID)
End If
Next
' Call the DeleteProductsWithTransaction method and show the Label
' if at least one row was deleted...
If productIDsToDelete.Count > 0 Then
Dim productAPI As New ProductsBLL()
productAPI.DeleteProductsWithTransaction(productIDsToDelete)
DeleteResults.Visible = True
' Rebind the data to the GridView
Products.DataBind()
End If
End Sub
The updated code creates a List(Of T) of type Integer (productIDsToDelete) and populates it with the ProductID values to delete. After the For Each loop, if there is at least one product selected, the ProductsBLL class s DeleteProductsWithTransaction method is called and passed this list. The DeleteResults Label is also displayed and the data rebound to the GridView (so that the newly-deleted records no longer appear as rows in the grid).
Figure 4 shows the GridView after a number of rows have been selected for deletion. Figure 5 shows the screen immediately after the Delete Selected Products button has been clicked. Note that in Figure 5 the ProductID values of the deleted records are displayed in the Label beneath the GridView and those rows are no longer in the GridView.
Figure 4: The Selected Products Will Be Deleted (Click to view full-size image)
Figure 5: The Deleted Products ProductID Values are Listed Beneath the GridView (Click to view full-size image)
Note
To test the DeleteProductsWithTransaction method s atomicity, manually add an entry for a product in the Order Details table and then attempt to delete that product (along with others). You will receive a foreign key constraint violation when attempting to delete the product with an associated order, but note how the other selected products deletions are rolled back.
Summary
Creating a batch deleting interface involves adding a GridView with a column of checkboxes and a Button Web control that, when clicked, will delete all of the selected rows as a single atomic operation. In this tutorial we built such an interface by piecing together work done in two previous tutorials, Adding a GridView Column of Checkboxes and Wrapping Database Modifications within a Transaction. In the first tutorial we created a GridView with a column of checkboxes and in the latter we implemented a method in the BLL that, when passed a List(Of T) of ProductID values, deleted them all within the scope of a transaction.
In the next tutorial we'll create an interface for performing batch inserts.
Happy Programming!
About the Author
Scott Mitchell, author of seven ASP/ASP.NET books and founder of 4GuysFromRolla.com, has been working with Microsoft Web technologies since 1998. Scott works as an independent consultant, trainer, and writer. His latest book is Sams Teach Yourself ASP.NET 2.0 in 24 Hours. He can be reached at mitchell@4GuysFromRolla.com. or via his blog, which can be found at http://ScottOnWriting.NET.
Special Thanks To
This tutorial series was reviewed by many helpful reviewers. Lead reviewers for this tutorial were Hilton Giesenow and Teresa Murphy. Interested in reviewing my upcoming MSDN articles? If so, drop me a line at mitchell@4GuysFromRolla.com.