GridView Examples for ASP.NET 2.0: Deleting a GridView's Underlying Data
Click here to return to the TOC.
All of the examples we've seen thus far have been read-only GridViews. The examples might have allowed for drilling down into data or sorting or paging data, but regardless the end user was only able to view data. There are times, though, when you will want to let users delete or update the data that powers your Web application. There are various techniques that can be used to edit and delete data, one of them being utilizing the GridView's built-in deletion and editing capabilities.
As with paging or sorting data, deleting data that comes directly from a database by means of a SqlDataSource is easy to accomplish, requiring only a few points and clicks of the mouse. To delete data from a GridView that is populated by means of an ObjectDataSource, however, the underlying data access layer class must provide a method for deleting the data.
In this section we'll see how to delete data from a GridView that utilizes both a SqlDataSource and an ObjectDataSource. We'll also look at how to add client-side confirmation to the GridView's delete capabilities to help protect your users from accidentally deleting a record.
Deleting Data Coming from a SqlDataSource
In the Filtering the Data Shown in a GridView section we saw how to display the order details in a GridView for a particular product selected from a DropDownList. Let's enhance this demo to include the capability to delete an order detail.
In order to enable deletion from the GridView we need to enhance the SqlDataSource to include a DELETE statement in addition to its SELECT statement. The easiest way to add a DELETE statement is through the SqlDataSource's wizard. On step 2, where you pick the table or view and its fields to return, there's an Advanced button that, when clicked, displays the dialog box shown in Figure 35.
.gif "ms972940.gridview_fg35(en-us,MSDN.10).gif")
Figure 35
By clicking the top checkbox—Generate Insert, Update, and Delete Statements—the SqlDataSource will automatically create the needed INSERT, UPDATE, and DELETE statements, along with the required parameter declarations. Note that the dialog box in Figure 35 also has a second checkbox, Use optimistic concurrency. If this box is checked when the user updates or deletes a record through the GridView a check is made to ensure that the data for the altered row hasn't been changed since they loaded the GridView. If the record has changed the update or delete will fail because a row won't be matched; it is up to you, the page developer, to decide what course of action to take in this case. If Use optimisitic concurrency is left unchecked, the row to be updated or deleted is simply matched by its primary key value(s), ignoring the values of the non-primary key fields.
In essence, check this second checkbox if you want to ensure that user's updates or deletes won't occur if they step on other user's changes; leave it unchecked if you just want to last update/delete to prevail. Checking or leaving this checkbox unchecked simply sets the SqlDataSource's ConflictDetection property to CompareAllValues or OverwriteChanges, respectively. As we'll see in the next section, the ObjectDataSource also has a ConflictDetection property that is used to specify whether or not optimistic concurrency should be used.
Note When configuring the DataSource to support deleting through a GridView, be sure that the SELECT query returns the field(s) that makeup the underlying data's primary key, even if you don't plan on displaying these field(s) in the GridView. As you can see below, the SelectCommand for the SqlDataSource that retrieves records from the Order Details table is bringing back OrderID and ProductID, the composite primary key fields. Furthermore, the **GridView'**s DataKeyNames property must be set to the primary key field(s). (If there is more than one primary key field, separate each by a comma.)
As the first checkbox's title implies, checking it will generate not only a DELETE statement, but INSERT and UPDATE statements, as well. After checking this checkbox the SqlDataSource's declarative syntax has expanded to:
<asp:SqlDataSource
ConnectionString="<%$ ConnectionStrings:NWConnectionString %>"
DeleteCommand="DELETE FROM [Order Details] WHERE [OrderID] =
@original_OrderID AND [ProductID] = @original_ProductID"
ID="SqlDataSource1" InsertCommand="INSERT INTO [Order Details]
([OrderID], [ProductID], [UnitPrice], [Quantity]) VALUES (@OrderID,
@ProductID, @UnitPrice, @Quantity)"
runat="server" SelectCommand="SELECT [OrderID], [ProductID],
[UnitPrice], [Quantity] FROM [Order Details] WHERE ([ProductID] =
@ProductID)"
UpdateCommand="UPDATE [Order Details] SET [UnitPrice] = @UnitPrice,
[Quantity] = @Quantity WHERE [OrderID] = @original_OrderID AND
[ProductID] = @original_ProductID">
<DeleteParameters>
<asp:Parameter Name="original_OrderID" Type="Int32" />
<asp:Parameter Name="original_ProductID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="UnitPrice" Type="Decimal" />
<asp:Parameter Name="Quantity" Type="Int16" />
<asp:Parameter Name="original_OrderID" Type="Int32" />
<asp:Parameter Name="original_ProductID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="productSelector"
Name="ProductID" PropertyName="SelectedValue"
Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="OrderID" Type="Int32" />
<asp:Parameter Name="ProductID" Type="Int32" />
<asp:Parameter Name="UnitPrice" Type="Decimal" />
<asp:Parameter Name="Quantity" Type="Int16" />
</InsertParameters>
</asp:SqlDataSource>
Note the additional <DeleteParameters>, <UpdateParameters>, <InsertParameters>, and DeleteCommand, InsertCommand, and UpdateCommand properties. Since we are only interested in deleting in this example, you can safely remove the InsertCommand, UpdateCommand, <UpdateParameters>, and <InsertParameters>.
Once you have configured the SqlDataSource to have a DeleteCommand, the associated GridView's Smart Tag will include a checkbox titled Enable Deleting. If you check this, a CommandField with a Delete button is added to the GridView (see Figure 36).
.gif "Click here for larger image.")
Figure 36 (Click on the graphic for a larger image)
And that's all there is to it! No code is required. One downside is that there's no confirmation upon deleting from the GridView. That is, as soon as the Delete button is clicked for a particular GridView row the page is posted back and that row is lost. Ideally end users would be presented with a prompt asking them to confirm the delete. We'll see how to accomplish this in an upcoming section, Utilizing Client-Side Script to Confirm Deletions.
The following shows the ASP.NET page's declarative syntax. When studying this markup be sure to take note of the following:
- The orderDetailsGridView GridView's DataKeyNames field is set to the primary key fields for the Order Details table—OrderID and ProductID. When the Delete button in the GridView is clicked, the page is posted back and the SqlDataSource's DeleteCommand is executed, using the clicked GridView row's DataKeyNames values for the OrderID and ProductID parameter values in the DELETE statement.
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h2>You Can Delete Order Detail Information for Orders
that Have Included Shipments
of the Selected Product
</h2>
<asp:SqlDataSource ID="productListingDataSource"
ConnectionString="<%$ ConnectionStrings:NWConnectionString %>"
SelectCommand="SELECT [ProductID], [ProductName] FROM
[Products]" Runat="server">
</asp:SqlDataSource>
<asp:DropDownList ID="productSelector" Runat="server"
DataSourceID="productListingDataSource"
DataTextField="ProductName" DataValueField="ProductID"
AutoPostBack="True">
</asp:DropDownList>
<asp:SqlDataSource ID="orderDetailsForProduct"
DataSourceMode="DataReader"
ConnectionString="<%$ ConnectionStrings:NWConnectionString %>"
SelectCommand="SELECT [OrderID], [ProductID], [UnitPrice],
[Quantity] FROM [Order Details] WHERE ([ProductID] =
@ProductID2)"
Runat="server" DeleteCommand="DELETE FROM [Order Details]
WHERE [OrderID] = @original_OrderID AND [ProductID] =
@original_ProductID">
<DeleteParameters>
<asp:Parameter Type="Int32"
Name="OrderID"></asp:Parameter>
<asp:Parameter Type="Int32"
Name="ProductID"></asp:Parameter>
</DeleteParameters>
<SelectParameters>
<asp:ControlParameter Name="ProductID2"
Type="Int32" ControlID="productSelector"
PropertyName="SelectedValue"></asp:ControlParameter>
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="orderDetailsGridView" Runat="server"
AutoGenerateColumns="False"
DataSourceID="orderDetailsForProduct"
DataKeyNames="OrderID,ProductID" BorderColor="Tan"
CellPadding="2" BackColor="LightGoldenrodYellow"
BorderWidth="1px" ForeColor="Black" GridLines="None"
OnRowDeleting="orderDetailsGridView_RowDeleting">
<FooterStyle BackColor="Tan"></FooterStyle>
<PagerStyle ForeColor="DarkSlateBlue"
HorizontalAlign="Center"
BackColor="PaleGoldenrod"></PagerStyle>
<HeaderStyle Font-Bold="True" BackColor="Tan"></HeaderStyle>
<AlternatingRowStyle
BackColor="PaleGoldenrod"></AlternatingRowStyle>
<Columns>
<asp:CommandField DeleteText="Delete Order Line Item"
ShowDeleteButton="True"></asp:CommandField>
<asp:BoundField HeaderText="Order ID" DataField="OrderID"
SortExpression="OrderID">
<ItemStyle HorizontalAlign="Center"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Quantity" DataField="Quantity"
SortExpression="Quantity" DataFormatString="{0:d}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Unit Price"
DataField="UnitPrice" SortExpression="UnitPrice"
DataFormatString="{0:c}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="GhostWhite"
BackColor="DarkSlateBlue"></SelectedRowStyle>
</asp:GridView> </div>
</form>
</body>
</html>
To aid with deleting, the GridView provides a RowDeleting event that fires before the underlying data is actually deleted. If you want to only allow the data to be deleted if some conditions are met, you can create an event handler for this event. The event handler is passed as its second parameter an object of type GridViewDeleteEventArgs, which has a couple of helpful properties:
- Values, which provides data on the values of the row being deleted, and
- Cancel, which, if set to True, short-circuits the deletion.
There's also a RowDeleted event that is fired after the underlying record has been successfully deleted.
Deleting Data Coming from an ObjectDataSource
Deleting a record in a GridView that is bound to an ObjectDataSource is fundamentally the same as deleting a record from a GridView bound to a SqlDataSource. The only difference is in the back-end: the data access layer class that the ObjectDataSource is accessing must provide a method for deleting a record.
When manipulating the data exposed by an ObjectDataSource, there are two methods to work with this data. The ObjectDataSource has a ConflictDetection property that can be one of two values:
- OverwriteChanges (the default), or
- CompareAllValues
The OverwriteChanges option is intended to be used if you don't care about the possibility of some other user stepping on the data being deleted or updated by the current user. In this scenario, whatever user commits their deletion or update last is the winner. There are times, though, where you may want to short circuit a user's changes if you detect someone else has made a modification to the underlying data. In such scenarios, use the CompareAllValues option.
The reason I mention the ObjectDataSource's ConflictDetection property is because the signature for the Delete method depends on its setting. Clearly, the Delete method must accept as many parameters as are needed to uniquely identify the record being deleted. For example, if we wanted to create a method to delete a product from the Products table then the method, at a minimum, would need to accept a ProductID integer input, since each product is uniquely identified by a ProductID. When using the OverwriteChanges mode, the Delete method only needs to accept this minimal set of input(s). If, however, you use the CompareAllValues option, the Delete method signature must accept not only the primary key field(s), but also the other fields defined in the GridView.
This may sound a bit confusing at this point, and understandably so. Things will become clearer, though, with a concrete example. The Order Details table has a composite primary key, one that is made up of two fields: ProductID and OrderID. The GridView shows three fields: OrderID, Quantity, and UnitPrice. Now, if we wanted to perform a delete with the ObjectDataSource's ConflictDetection property set to OverwriteChanges, the Delete method would need to look like:
' Visual Basic .NET
Public Shared Sub DeleteMethod(ByVal original_OrderID As Integer, _
ByVal original_ProductID As Integer)
...
End Sub
// C#
public static void DeleteMethod(int original_OrderID, int original_ProductID)
{
...
}
If, however, the ConflictDetection property was set to OverwriteChanges, the Delete method would need to look like:
' Visual Basic .NET
Public Shared Sub DeleteMethod(ByVal original_OrderID As Integer, _
ByVal original_ProductID As Integer, _
ByVal original_Quantity as Integer, _
ByVal original_UnitPrice as Decimal)
...
End Sub
// C#
public static void DeleteMethod(int original_OrderID,
int original_ProductID, int original_Quantity,
decimal original_UnitPrice)
{
...
}
For this demo I used the OverwriteChanges approach. The following, then, shows the Delete method in the OrderDetailDAL class:
The DeleteOrderDetail Method (Visual Basic)
Public Class OrderDetailDAL
...
Public Shared Sub DeleteOrderDetail(ByVal original_OrderID _
As Integer,
ByVal original_ProductID As Integer)
' deletes a specified Order Details record
' from the Northwind Products table
Dim sql As String = _
"DELETE FROM [Order Details] WHERE OrderID = " & _
"@OrderID AND ProductID = @ProductID"
Using myConnection As New _
SqlConnection(ConfigurationManager.ConnectionStrings("NWConnectionString")
.ConnectionString)
Dim myCommand As New SqlCommand(sql, myConnection)
myCommand.Parameters.Add(New SqlParameter("@OrderID", _
original_OrderID))
myCommand.Parameters.Add(New SqlParameter("@ProductID", _
original_ProductID))
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Using
End Sub
End Class
The DeleteOrderDetail Method (C#)
public class OrderDetailDAL
{
...
public static void DeleteOrderDetail(int original_OrderID,
int original_ProductID)
{
// deletes a specified Order Details record
// from the Northwind Products table
string sql = "DELETE FROM [Order Details] WHERE OrderID = " +
"@OrderID AND ProductID = @ProductID";
using (SqlConnection myConnection =
new SqlConnection(
ConfigurationManager.ConnectionStrings["NWConnectionString"].ConnectionString))
{
SqlCommand myCommand = new SqlCommand(sql, myConnection);
myCommand.Parameters.Add(new SqlParameter("@OrderID",
original_OrderID));
myCommand.Parameters.Add(new SqlParameter("@ProductID",
original_ProductID));
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
}
}
Configuring the ObjectDataSource for deletion is a trifle different than with the SqlDataSource. After selecting the class to use, you can specify a method to delete data by browsing to the Delete tab and selecting the method name in the drop-down list, as shown in Figure 37.
.gif "ms972940.gridview_fg37(en-us,MSDN.10).gif")
Figure 37
Once you have configured the ObjectDataSource for deletion, the steps of configuring the GridView for deletion are the same as with the SqlDataSource, as is the end result. The following shows the declarative syntax of the ASP.NET page:
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<div>
<h2>You Can Delete Order Detail Information
for Orders that Have Included Shipments
of the Selected Product
</h2>
<asp:ObjectDataSource ID="productListingDataSource"
Runat="server" TypeName="ProductDAL"
SelectMethod="GetProducts">
</asp:ObjectDataSource>
<asp:DropDownList ID="productSelector" Runat="server"
DataSourceID="productListingDataSource"
DataTextField="ProductName" DataValueField="ProductID"
AutoPostBack="True">
</asp:DropDownList>
<asp:ObjectDataSource ID="orderDetailsForProduct"
Runat="server" SelectMethod="GetOrderDetailsByProductID"
TypeName="OrderDetailDAL" DeleteMethod="DeleteOrderDetail">
<DeleteParameters>
<asp:Parameter Type="Int32"
Name="original_OrderID"></asp:Parameter>
<asp:Parameter Type="Int32"
Name="original_ProductID"></asp:Parameter>
</DeleteParameters>
<SelectParameters>
<asp:ControlParameter Name="productID"
Type="Int32" ControlID="productSelector"
PropertyName="SelectedValue"></asp:ControlParameter>
</SelectParameters>
</asp:ObjectDataSource>
<asp:GridView ID="orderDetailsGridView" Runat="server"
AutoGenerateColumns="False"
DataSourceID="orderDetailsForProduct"
DataKeyNames="OrderID,ProductID" BorderColor="Tan"
CellPadding="2" BackColor="LightGoldenrodYellow"
BorderWidth="1px" ForeColor="Black" GridLines="None">
<FooterStyle BackColor="Tan"></FooterStyle>
<PagerStyle ForeColor="DarkSlateBlue" HorizontalAlign="Center"
BackColor="PaleGoldenrod"></PagerStyle>
<HeaderStyle Font-Bold="True" BackColor="Tan"></HeaderStyle>
<AlternatingRowStyle
BackColor="PaleGoldenrod"></AlternatingRowStyle>
<Columns>
<asp:CommandField DeleteText="Delete Order Line Item"
ShowDeleteButton="True"></asp:CommandField>
<asp:BoundField HeaderText="Order ID" DataField="OrderID"
SortExpression="OrderID">
<ItemStyle HorizontalAlign="Center"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Quantity"
DataField="Quantity" SortExpression="Quantity"
DataFormatString="{0:d}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Unit Price"
DataField="UnitPrice" SortExpression="UnitPrice"
DataFormatString="{0:c}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="GhostWhite"
BackColor="DarkSlateBlue"></SelectedRowStyle>
</asp:GridView> </div>
</div>
</form>
</body>
</html>
Utilizing Client-Side Script to Confirm Deletions
The two deletion examples we just examined do not provide confirmation upon delete. That is, as soon as an end user clicked the Delete button, a postback occurred and the record was deleted. To help prevent accidental deletions, it's good practice to provide the end user with a confirmation of some sort, verifying that they do, indeed, want to delete the record before doing so. One common confirmation technique is to use a client-side confirm messagebox. The confirm messagebox is one that is displayed with an OK and Cancel button that causes the form submission to be short circuited if the user clicks Cancel.
The ASP.NET 2.0 Button, LinkButton, and ImageButton Web controls all contain an OnClientClick property in which you can specify client-side JavaScript that should be executed when the Button is clicked. If you use JavaScript like the following:
return confirm(msg);
The user will be shown a confirm messagebox upon clicking the button (where msg is the string you want to appear in the confirm messagebox). If the user clicks the confirm messagebox's Cancel button the form will not be posted back; clicking the OK button will submit the form.
Unfortunately, the GridView's CommandField does not include an OnClientClick property. However, any Button in a GridView with a CommandName of Delete, when clicked, will cause the GridView to delete the associated record. Therefore we can create our own Delete button by adding a TemplateField that contains a Button (or LinkButton or ImageButton) with a CommandName of Delete. This added Button, then, can have an appropriate OnClientClick property value. The following shows how to modify the GridView to provide a confirmation messagebox when the Delete button is clicked:
<asp:GridView ID="orderDetailsGridView" Runat="server"
DataSourceID="orderDetailsForProduct"
AutoGenerateColumns="False" DataKeyNames="OrderID,ProductID"
BorderColor="Tan"
CellPadding="2" BackColor="LightGoldenrodYellow" BorderWidth="1px"
ForeColor="Black"
GridLines="None">
<FooterStyle BackColor="Tan"></FooterStyle>
<PagerStyle ForeColor="DarkSlateBlue" HorizontalAlign="Center"
BackColor="PaleGoldenrod"></PagerStyle>
<HeaderStyle Font-Bold="True" BackColor="Tan"></HeaderStyle>
<AlternatingRowStyle BackColor="PaleGoldenrod"></AlternatingRowStyle>
<Columns>
<asp:TemplateField><ItemTemplate>
<asp:LinkButton ID="LinkButton1" Runat="server"
OnClientClick="return confirm('Are you sure you
want to delete this record?');"
CommandName="Delete">Delete Order Line Item</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="Order ID" DataField="OrderID"
SortExpression="OrderID">
<ItemStyle HorizontalAlign="Center"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Quantity" DataField="Quantity"
SortExpression="Quantity"
DataFormatString="{0:d}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Unit Price"
DataField="UnitPrice" SortExpression="UnitPrice"
DataFormatString="{0:c}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="GhostWhite"
BackColor="DarkSlateBlue"></SelectedRowStyle>
</asp:GridView>
Note that the TemplateField has a LinkButton with its CommandName property set to Delete and its OnClientClick property set to return confirm('Are you sure you want to delete this record?');. As Figure 38 shows, now when a Delete button is clicked a confirm messagebox is displayed and the record is only deleted if the messagebox's OK button is clicked.
.gif "ms972940.gridview_fg38(en-us,MSDN.10).gif")
Figure 38