Deleting Data

Chapter 7 - Updating and Deleting Records
byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

The procedure for deleting records from a database is similar to the one for updating them - in fact, it's a bit simpler. Just as you saw above, you can create a command object and use it to execute an SQL DELETE query (or stored procedure), or you can use a dataset in conjunction with a data adapter to delete some records in memory, and then perform a batch delete in the database.

SQL DELETE Statements

SQL defines a DELETE statement to delete one or more existing records, but unlike SELECT and UPDATE, we can only delete from one table at a time. The generic form of the DELETE statement's syntax is represented below:

     DELETE [FROM]     { table_name }     [WHERE condition1 AND|OR condition2 AND|OR ... AND|OR conditionN] 

Where:

• DELETE is the SQL keyword

• FROM is an optional keyword that can be omitted

• table_name is the name of the table containing the records to be deleted

• WHERE is a SQL keyword that specifies one or more conditions that qualify the records to be deleted, and follows the standard format you've seen earlier in the piece

All of this being the case, the DELETE statement below will delete the category that we inserted and updated earlier in the chapter:

    DELETE Categories WHERE CategoryName = 'Books/Magazines' 

Specifically, this will delete all records in which the CategoryName field has a value of Books/Magazines. In this case, there is only one record that satisfies the condition. If you'd run the INSERT command we used earlier multiple times, you'll have several records with a CategoryName of Books/Magazines; running this DELETE operation once will delete all of them.

Note 

If you fail to specify a WHERE clause in a DELETE SQL statement, all of the rows in the table will be deleted.

To follow the methodology of our earlier discussions, let's have a quick look at how to update records using a command object. We will then look at the DataSet solution later in the chapter.

Using a Command Object to Delete Records

The sequence of operations for using a command object to delete the records in a database is almost identical to the one for updating records:

• Create a database connection

• Create a command object and specify an SQL DELETE query (or a stored procedure)

• Link the command object to the database connection

• Invoke the ExecuteNonQuery() method of the command object to execute the DELETE query

• Close the connection

The skeleton code below demonstrates a simple database delete operation.

    Dim strConnection As String = ConfigurationSettings.AppSettings("NWind")    Dim objConnection As New SqlConnection(strConnection)    Dim strSQL As String = "DELETE Categories   "   & _                           "WHERE CategoryName = 'Books/Magazines'"    Dim dbComm As New SqlCommand(strSQL, objConnection)    objConnection.Open()    dbComm.ExecuteNonQuery    objConnection.Close() 

In a nutshell, that's how you use the command object to execute a DELETE query. Rather than running through an example that would be almost identical to those we've already looked at, let's jump to see how to delete records using a DataSet and a data adapter object.

Deleting Records using a Dataset

Deleting records from a DataSet is also very similar to an update operation - you first delete them in the DataSet, and then use a data adapter to delete them physically from the database. Assuming that you've filled a DataSet with records from the Products table in the Northwind database, as illustrated in the code snippet below...

    Dim strSQLSelect As String = "SELECT ProductID, ProductName, UnitPrice " & _                                 "FROM Products"    Dim adapter As New SqlDataAdapter(strSQLSelect, objConnection)    Dim ds As New DataSet()    adapter.Fill(ds, "ProductTable")    objConnection.Close 

...you can then search for records in the DataSet, and delete them.

Deleting Records In a Dataset

With the DataSet in your hands, you can delete one or more of the records it contains. The code snippet below demonstrates how to delete a product:

    Dim tbl As DataTable = ds.Tables("ProductTable")    tbl.PrimaryKey = New DataColumn() _                     { _                       tbl.Columns("ProductID") _                     }    Dim row As DataRow = tbl.Rows.Find(1)    row.Delete() 

The DataRow. Delete () function marks the record as deleted by changing the row. RowState property to Deleted - but the row itself remains in the table's Rows collection. An alternative is to remove a row from the Rows collection by using the Rows. Remove () method, which you could call by replacing the last line of the above code with this:

    tbl.Rows.Remove(row) 

This changes the row. RowState property to Detached, but once again, only the DataSet is affected. However, there is a very important difference between the two techniques we just discussed.

When you've 'deleted' a row with the DataRow. Delete() method, you can undelete it by calling the RejectChanges() method of either the DataRow object, or its parent DataTable object. Either way, the state of the row reverts to Unchanged. When a record is marked as Deleted, you can later attach a data adapter object to the DataSet, and invoke the Update() method on the data adapter. This has the result of deleting the record from the database, and the row from the DataSet.

On the other hand, once you've called the Rows. Remove() method, the row is removed from the DataSet and its RowState is changed to Detached. Importantly, however, the corresponding row will not be removed from the database when you invoke the Update() method of an associated data adapter object. Therefore, this method is more useful when you only need to manipulate data in memory. If you intend later to delete the record from the database, you should use DataRow.Delete().

Deleting Records in a Database

To delete the records in the database that correspond to the rows deleted from the DataSet, you must reconnect the DataSet to the database:

     Dim cb As New SqlCommandBuilder(adapter)     objConnection.Open()     adapter.Update(ds,  "ProductTable")     objConnection.Close() 

The Update() method examines each row's RowState property. If it's Deleted, the Update () method executes the DELETE operation to remove the corresponding record from the database.

Try It Out - Deleting Records from the Database

Let's modify the ASP.NET page that we created in the last exercise to add the ability to delete a product from the database.

1. First, add a new Delete button column to the dgProducts DataGrid by adding a couple of lines to the HTML section of our ASPX file. In fact, it would be a good idea to make these changes to a new version of the file: Delete_DataGrid.aspx.

       <asp:DataGrid  runat="server"                  CellPadding="5" AutoGenerateColumns="False"                  OnEditCommand="EditRecord"                  OnCancelCommand="CancelEdit"                  OnUpdateCommand="UpdateRecord"                  OnDeleteCommand="DeleteRecord">      <Columns>        <asp:BoundColumn DataField="ProductID" ReadOnly="True"                      Visible="False" />        <asp:BoundColumn DataField="ProductName" ReadOnly="True"                      HeaderText="Name" />        <asp:BoundColumn DataField="UnitPrice" HeaderText="Price" />        <asp:EditCommandColumn ButtonType="LinkButton"                      UpdateText="Save" CancelText="Cancel" EditText="Edit" />        <asp:ButtonColumn Text="Delete" CommandName="Delete" />      </Columns>    </asp:DataGrid> 

2. Next, add a handler for the DataGrid's DeleteCommand event.

    Public Sub DeleteRecord(ByVal Sender As Object,                         ByVal E As DataGridCommandEventArgs)   ' Retrieve the ID of the product to be deleted   Dim ProductID As Int32 = Convert.ToInt32(E.Item.Cells(0).Text)   dgProducts.EditItemIndex = -1   DeleteProduct(ProductID)   ' Display the remaining items in the DataGrid      dgProducts.DataBind() End Sub Private Sub DeleteProduct(ByVal ProductID As Long)   ' Create and load a DataSet with records from Northwind's Products table   Connect()   Dim adapter As New SqlDataAdapter(strSQLSelect, objConnection)   Dim ds As New DataSet()   adapter.Fill(ds, ProductTableName)   Disconnect()   ' Mark the product as Deleted in the DataSet   Dim tbl As DataTable = ds.Tables(ProductTableName)   tbl.PrimaryKey = New DataColumn() _                    { _                      tbl.Columns("ProductID")                    }   Dim row As DataRow = tbl.Rows.Find(ProductID)   row.Delete()   ' Reconnect the DataSet and delete the record from the database   Dim cb As New SqlCommandBuilder(adapter)   Connect()   adapter.Update(ds, ProductTableName)    dgProducts.DataSource = ds.Tables(ProductTableName) Disconnect() End Sub 

3. With this new code in place, browsing to the Delete_DataGrid.aspx file should result in the following display:

   

How It Works

You'll see at once that the pair of procedures we've added here is similar to the UpdateRecord() event handler and its associated UpdateProduct() procedure, but slightly simpler. If you try to click on one of the Delete links here, however, you'll get an unpleasant error:

 DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'. 

In the Northwind database, items in the Products table can also appear in the Order Details table - and all of the beverages in the Products table have these kinds of relationships. Deleting them is therefore not allowed, as it will leave some order items without a related product. In production code, you'd need to catch these exceptions before they found their way to the user. In this example, to demonstrate that our code is working properly, we can use the Page_Load() handler to add a new entry to the Products table that we can be sure is legal to delete:

 Private Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)   If Not IsPostBack Then     Dim strSQL As String = "INSERT INTO Products (" & _                            "ProductName, CategoryID, UnitPrice) " & _                            "VALUES ('RolaBolaCola', 1, 15)"     Connect()     Dim dbComm As New SqlCommand(strSQL, objConnection)     dbcomm.ExecuteNonQuery()     Disconnect()     LoadGrid()   End If End Sub 

This new code, which will only run when the page is first loaded, adds a beverage called "RolaBolaCola" to the Products table that will appear in the browser. When you click Delete, the entry is removed from both the grid and the database, as described above.