Deleting (or Deactivating) a Horse

[Previous] [Next]

As we mentioned earlier, with ADO you can choose from two ways to update, insert, or delete data. We chose one way to update and insert horses and the other way to delete them. We decided to send just a HorseId value to the transactional component and to have that component issue an action query. Here's the code:

Public Sub Delete(lngId As Long) Dim con As ADODB.Connection, strSQL As String strSQL = "DELETE FROM Horses " & _ "WHERE HorseId = " & lngId Set con = CreateObject("ADODB.Connection") con.ConnectionString = strConn con.Open con.Execute strSQL End Sub

As you might recall from the talks about business rules, there's a good chance that a trigger in the database will change the deletion to a deactivation. For the time being, we won't add what's needed to take care of any such messages from the database. We'll save that for when we take another look at each of our business rules in Chapter 22, "What About the Business Rules?"

Concurrency Control When Deleting

The delete operation, implemented as in the preceding code, doesn't take simultaneous users into consideration. It doesn't necessarily have to, but we must consider the possible need for this operation to control concurrency. To help us do that, we'll use the SQL Server Profiler that's included in SQL Server 7.0. Using this profiler, we can see exactly which SQL statements ADO sends to SQL Server.

We decided to delete the old horse named Brunte. (Brunte is a typical name for brown Swedish country horses. A good translation of the name is probably Old Faithful.) Not surprisingly, the code SQL Server executed was much the same as the one we put in our method:

DELETE FROM Horses WHERE HorseId = 2163

Our next step was to reinsert Brunte in the database. We wanted to use our application to make a change to the data we had for Brunte. We didn't want to make him a gelding because that would be too brutal. Instead, we could change his birth year. That should be less brutal.

We included all the data for Brunte on our test form and changed his birth year from 1990 to 1989. We then clicked the Save button, thus calling the recordset's Save method, and then took another look in the profiler. Interestingly, the combination of ADO, OLE DB, and the OLE DB provider for SQL Server sent the following call to SQL Server:

sp_executesql N'UPDATE "MSPress_ADBRacing".."Horses" SET "Birthyear"=@P1 WHERE "HorseId"=@P2 AND "Birthyear"=@P3', N'@P1 smallint, @P2 int, @P3 smallint', 1989, 2164, 1990

This code is interesting in several ways. The sp_executesql stored procedure, as Ron Soukup and Kalen Delaney¹ express it, is halfway between ad hoc caching of ordinary SQL statements and stored procedures. As we explain in Chapter 17, SQL Server always needs an execution plan before it can execute a query. Sometimes, as in the case of a stored procedure, such an execution plan might already exist and can be reused. Reusing an execution plan can save as much as 40 percent of the execution time, sometimes even more. As we also explain in Chapter 17, the sp_executesql procedure helps SQL Server reuse the execution plan of similar SQL queries executed earlier. Its job is to make it easier for SQL Server to recognize the similarity between this query and a query plan that might already exist in SQL Server's memory.

As you can see, the preceding expression uses three parameters:

• @P1, representing the new Birthyear value, 1989.
• @P2, representing the unique key that identifies Brunte the horse. Its value here is 2164.
• @P3, representing the original Birthyear value, 1990.

After we replaced the actual parameters of the query sent to sp_executesql, and after we removed the quotations (to make the statement easier to read), here follows the actual SQL query executed by SQL Server:

UPDATE MSPress_ADBRacing..Horses SET Birthyear= 1989 WHERE HorseId= 2164 AND Birthyear= 1990

There are two interesting things to note in this statement:

• ADO, OLE DB, and the OLE DB provider for SQL Server have classified the table name by putting the name of the database and the owner of the table before the table name. This helps SQL Server reuse an existing plan (or later reuse the one compiled for this statement) as further described in Chapter 17.
• ADO, OLE DB, and the OLE DB provider for SQL Server have added AND Birthyear = 1990 to the WHERE clause. If another user has already changed the Birthyear value to something other than the one we originally fetched, this UPDATE statement won't find the row to update. Therefore, our update will fail.

Figure 13-4 shows a simple error message, displayed in a message box, telling the user that his or her update has failed because of concurrency issues. Some other user must have changed the value (or one of the values) we wanted to update after we fetched the record but before we sent our update statement to the database.

Figure 13-4. Concurrency control. Since another user has changed the birth year of Brunte, our update fails.

Our strategy so far has been to use the HorseId value only to delete a horse. As we said earlier in this chapter, we could have used another strategy. We could have called the recordset's Delete method, thereby marking the record as deleted. Then, using the Save method now used only for updates and insertions, we could send the recordset to the server components and have them issue an UpdateBatch command. If we use that strategy, what happens to concurrency? Let's find out.

We performed our own investigation in as simple a way as possible. We opened the test form with Brunte in focus. Then we didn't press the Delete button. Instead, we clicked the Save button, which made the form send our unchanged recordset to the server components. There we followed the execution step by step, using the Visual Basic debugger, until we were in the HorseTrSrvcs component. Before we continued, we used the Immediate window in Visual Basic's IDE to manually issue the following command:

rs.Delete

Then we continued running the program, allowing it to issue its normal UpdateBatch command. Looking again in the SQL Server Profiler, we weren't surprised to find the following Transact SQL statement issued:

sp_executesql N'DELETE FROM "MSPress_ADBRacing".."Horses" WHERE "HorseId"=@P1', N'@P1 int, 2164

This code translates to the following SQL statement:

DELETE FROM MSPress_ADBRacing..Horses WHERE HorseId = 2164

This statement is almost exactly the same as the one we originally sent as an action query. The only difference is that the table name is qualified with the name of the database and the implicit database owner (MSPress_ADBRacing..), making it easier for SQL Server to reuse existing plans. As far as concurrency goes, there's no difference between this statement and the one issued when we used a separate delete operation, giving it only the ID of the horse rather than the full recordset. As you recall, for an update operation ADO would add one or more AND clauses to help concurrency controls; as you can see now, this isn't the case for delete operations. If you want that, you'll have to furnish it yourself.

As far as deletions are concerned, we've shown you that it doesn't matter whether you use ADO recordsets or action queries, as we decided to do. The only differences between the approaches are the following:

• If you use ADO recordsets for deletions as well as for insertions and updates, you must manually set the filter of the recordset to adFilterPendingRecords. This has the effect of hiding any record not touched by an update, delete, or add new operation. If you don't set such a filter, a deletion won't reach the database. It will stay in an invisible and deleted record in the recordset.
• If you use ADO recordsets for deletions, you automatically get the table name qualification that makes it easier for SQL Server to reuse execution plans. This benefit saves you machine cycles and time and adds to the scalability of your application.
• If you want concurrency control for deletions, making sure that any in-between change stops deletions as well as updates, you must furnish it yourself. Furthermore, you can't do it using recordset.UpdateBatch. You must do it with an action query, and you must set up the SQL command yourself.