Updating Data Sources with Transactions

One of the features of most database systems, and some other types of data store, is the ability to use transactions . Simply put, a transaction is a series of events that are all completed, or of which none are completed “there is never an intermediate result where some but not all of the events within the transaction occur.

The name transaction comes from real-world scenarios such as purchasing an item in a store where you give the seller money in exchange for goods. Unless one of you gets cheated, the transaction will either succeed with both parties happy at the outcome (you pay your money and get your goods), or fail where neither action occurs. There should never be an outcome where you pay money and don't get the goods, or where you get goods but don't pay the money.

In this section, you'll look at two types of transactions:

• Database transactions , where database-specific statements control the transaction and it is carried out within the database itself. Usually the stored procedure within the database contains the transaction statements.

• Connection-based transactions , where the statements that control the transaction, and the execution and management of that transaction are outside the database. Usually these are a feature of the Connection object that executes a SQL statement or stored procedure.

  Note

  While it is possible to write stored procedures that perform transactions across different databases on the same server, this is outside the scope of this chapter. It is also possible to use the services of another application, such as Windows 2000 Component Services (or MTS in Windows NT4) to perform a distributed transaction, where a series of events spread across different databases and applications on different servers are managed as a single transaction. Chapter 17 looks briefly at this topic.

Database Transactions

In a database system such as SQL Server, you can specify transaction operations within a stored procedure using vendor-specific statements like BEGIN TRANSACTION to start a new transaction, COMMIT TRANSACTION to accept all the updates and permanently commit the changes to the data, and ROLLBACK TRANSACTION to cancel all the changes made within the current transaction.

We've provided an example page that uses a transacted stored procedure. The stored procedure, named DoBookArchive , is created within the WroxBooks database by the SQL script provided with the samples.

The DoBookArchive Stored Procedure

The DoBookArchive stored procedure moves a row from the BookList table into another table named ArchiveBooks , within the same database. If the process succeeds, the transaction is committed and the updates are permanently applied to the database tables. If there is an error when writing to the ArchiveBooks table, or when deleting the book from the BookList table, both actions are rolled back and the tables are left in exactly the same state as before “neither is affected by the procedure.

However, to make it repeatable while you are experimenting with the example, the stored procedure always starts by deleting any existing book with the same ISBN (the primary key) in the ArchiveBooks table. This action will also be rolled back if the complete transaction fails, so if a book has been archived (and hence deleted from the BookList table) it will not be deleted from the ArchiveBooks table if you run the stored procedure again with the same ISBN. In this case, the INSERT statement will fail because the book is not in the BookList table, and so the entire transaction is rolled back undoing the DELETE operation on the ArchiveBooks table. The code for the stored procedure follows :

  CREATE PROCEDURE DoBookArchive   @ISBN varchar(12), @Result integer output AS   DECLARE @verror int   BEGIN TRANSACTION   DELETE FROM ArchiveBooks WHERE ISBN=@ISBN   INSERT INTO ArchiveBooks (ISBN, Title, PublicationDate)   SELECT * FROM BookList WHERE ISBN LIKE @ISBN   SELECT @verror = @@ERROR, @Result = @@ROWCOUNT   IF @verror <> 0 GOTO on_error   IF @Result > 0   BEGIN   DELETE FROM BookList WHERE ISBN=@ISBN   IF @@ERROR <> 0 GOTO on_error   COMMIT TRANSACTION   END   ELSE   ROLLBACK TRANSACTION   RETURN   on_error:   SELECT @Result = -1   ROLLBACK TRANSACTION   RETURN  

The Transacted Stored Procedure Example

The example page Updating Data with a Transacted Stored Procedure ( transacted-storedproc.aspx ) uses the stored procedure just described. We've arranged for it to use the same ISBN code as the previous example that inserts and deletes a book in the BookList table, so that you can see the results of this example by running it after inserting the new book and after deleting it. As shown in Figure 10-5, the stored procedure in this example will succeed providing that you have run the previous example to insert the new book row:

Figure 10-5:

If you then run the page again, as in Figure 10-6, it will show that the stored procedure failed to find the book in the BookList table (because, of course, it's just been moved to the ArchiveBooks table):

Figure 10-6:

The Code for the Transacted Stored Procedure Example

As in the earlier examples, let's start by specifying the name of the stored procedure and displaying it in the page, and then create the Connection and Command objects you'll need to execute it. Also set the CommandType of the Command object to indicate that you'll be executing a stored procedure. Then you can create the parameters for the command. This time there are only two “an input parameter to hold the ISBN of the book you want to archive and an output parameter to hold the result.

  'specify the stored procedure name   Dim strSQL As String = "DoBookArchive"   outSQL.InnerText = strSQL 'and display it     'create connection and command   Dim objConnect As New OleDbConnection(strConnect)   Dim objCommand As New OleDbCommand(strSQL, objConnect)   objCommand.CommandType = CommandType.StoredProcedure     'create an input Parameter object named 'ISBN' with the correct data   'type to match a SQL database 'varchar' field of 12 characters   Dim objParam As OleDbParameter   objParam = objCommand.Parameters.Add("ISBN", OleDbType.VarChar, 12)   objParam.Direction = ParameterDirection.Input   objParam.Value = "199999999"     'create an output Parameter object named 'Result' with the correct   'data type to match a SQL database 'integer' field   'specify that it's an Output parameter so no value required   objParam = objCommand.Parameters.Add("Result", OleDbType.Integer)   objParam.Direction = ParameterDirection.Output     'display the value of the input parameter   outInParams.InnerText = "ISBN='" & objCommand.Parameters("ISBN").Value & "'"  

Executing the Stored Procedure and Displaying the Results

The next step is to open your connection and execute the stored procedure. Then you can collect the result from the output parameter and display it, along with some accompanying explanatory text.

  Try   'execute the stored procedure   objConnect.Open()   objCommand.ExecuteNonQuery()   objConnect.Close()   Catch objError As Exception   outError.InnerHtml = "* Error while updating original data.<br />" _   & objError.Message & "<br />" & objError.Source   Exit Sub 'stop execution   End Try   'collect and display the value of the output parameter   Dim intResult As Integer = objCommand.Parameters("Result").Value   Dim strResult As String = "Result='" & CStr(intResult) & "'<br />"   Select Case intResult   Case -1: strResult &= "Error occurred while attempting archive"   Case 0: strResult &= "Failed to archive book  no matching book found"   Case > 0: strResult &= "Successfully archived the specified book"   End Select   outOutParams.InnerHtml = strResult  

Notice that you didn't have to do anything extra to benefit from the transaction within the stored procedure “you just executed it and checked the result to see what actually happened . This is not the case, however, when you use the other type of transaction, a connection-based transaction. You'll see how different working with this type of transaction is next.

Connection-Based Transactions

The previous example shows how you can use a transaction within a stored procedure (a database transaction) to ensure that operations on your data either all succeed or are all rolled back. A second way of using a transaction is through the capabilities of the Connection object. All Connection objects ( SqlConnection, OelDbConnection , OdbcConnection , and OracleConnection) can be used to perform transacted data updates.

While the way you actually apply a transaction is different from the stored-procedure transaction used in the previous example, the terminology is broadly the same. The three methods of the Connection class concerned with managing transactions are shown in the following table.

Connection.BeginTransaction	Starts a new transaction on this connection and all subsequent changes to the data become part of the transaction until it is committed or rolled back.
Transaction.Commit	Commits all changes made to the data within this transaction since it was started. The changes are made permanent in the target data store.
Transaction.Rollback	Abandons all changes made to the data within this transaction since it was started. The changes are removed from the target data store.

The Transaction Class

In ADO.NET, there are separate classes that implement transactions, one for each of the different types of Connection . To start a transaction, call the BeginTransaction method of the current Connection object. This returns a Transaction object that you must then assign to any Command objects that you want to enroll into that transaction.

To end a transaction and commit all the changes to the database, call the Commit method of the Transaction object (note that it's not a method of the Connection object as you might at first have expected). To abandon all changes to the data, you can call the Transaction object's Rollback method instead.

Notice also that you have to manually enroll any Command objects into the transaction. While this might seem odd, it does allow you to have multiple transactions in progress, and use whichever is appropriate for each command you carry out on the database. You can also create a nested transaction (that is a transaction that executes within another transaction) by creating a new Transaction object and calling the Begin method.

A Connection-Based Transaction Example

To see the transaction methods in action, open the example Transactional Data Updates with a Command Object ( update - with - transaction.aspx ) shown in Figure 10-7. This page creates three SQL statements that are used to update the titles of three books in the BookList table to reflect the current date and time, and then it executes these statements. Afterwards, it reads the rows back from the database and displays the details to confirm that the updates were successful.

Figure 10-7:

You can see in Figure 10-7 that the transaction was committed, and the three rows were updated. However, this is only because the page contains logic that uses the current time in seconds to decide whether to commit or roll back the transaction. While not a real-world scenario, it is done so that you can see the result of rolling back a transaction as well as committing it. After running the page again where the time has an even number of seconds, as shown in Figure 10-8, the transaction is rolled back and so the titles are not updated.

Figure 10-8:

The Code for the Connection-Based Transaction Example

The only real differences in the way that this page works, when compared to the other examples that use SQL statements to update the data source, is that you have to call the transaction methods at the appropriate times “effectively managing the transaction yourself. Instead of a stored procedure within the database itself deciding whether to commit or rollback the changes (usually dependent on the outcome of one of the statements in the stored procedure), you decide within your ASP code if the transaction should be committed or rolled back.

As usual, start by creating the SQL statements you'll be executing against the BookList table to update the book titles, as shown in the following code. Then you can create your Connection and Command objects, and declare a variable to hold the number of rows affected by your updates. The initial value is set to zero here, though this is not actually required (zero is the default value), but it helps to illustrate how the code works, and ensures that you can safely add on the result each time you execute a SQL statement.

  'specify the SQL statements to update the data   Dim strNow, strSQL1, strSQL2, strSQL3 As String   Dim datNow As DateTime = Now()   strNow = datNow.ToString("dd-M-yy \a\t hh:mm:ss")   strSQL1 = "UPDATE BookList SET Title = 'Book One Written on " _   & strNow & "' WHERE ISBN='1100000001'"   outSQL1.InnerText = strSQL1 'and display it   strSQL2 = "UPDATE BookList SET Title = 'Book Two Written on " _   & strNow & "' WHERE ISBN='1100000002'"   outSQL2.InnerText = strSQL2 'and display it   strSQL3 = "UPDATE BookList SET Title = 'Book Three Written on " _   & strNow & "' WHERE ISBN='1100000003'"   outSQL3.InnerText = strSQL3 'and display it     'create connection and command and variable to hold result   Dim objConnect As New OleDbConnection(strConnect)   Dim objCommand As New OleDbCommand()   Dim intRowsAffected As Integer = 0  

Starting a Transaction

You need a variable to hold the Transaction object that will be returned when we start a transaction, and so declare this next, as shown in the following code. Then open your connection, and execute the BeginTransaction method to start a new connection-based transaction. You can assign the Transaction object that is returned to your objTransaction variable.

Now you are ready to execute your three SQL UPDATE statements using the Command object you created earlier on. You created it without providing any values for the constructor parameters, so you have to assign your Connection object to its Connection property. Also set the CommandType to indicate that you're using a SQL statement (though this is the default if not specified). Once your Command object is set up, you also have to enroll it into the current transaction.

Notice that you can only do so after you've set the Connection property, and if you want to change the Connection property afterwards you first have to un-enrol it by setting the Transaction property of the Command object to Nothing .

  'declare a variable to hold a Transaction object   Dim objTransaction As OleDbTransaction     Try     'open connection before starting transaction   objConnect.Open()     'start a transaction for this connection   objTransaction = objConnect.BeginTransaction()     'specify the Connection object and command type for the Command   objCommand.Connection = objConnect   objCommand.CommandType = CommandType.Text     'attach the current transaction to the Command object   'must be done after setting Connection property   objCommand.Transaction = objTransaction  

Executing the Commands and Committing or Rolling Back

The next step is to assign each SQL statement to the CommandText property in turn and execute it. Then the next place where you need to consider how to handle the transaction that you've started is if an error occurs while executing the SQL statements. In an error situation, you would usually call the Rollback method of the Transaction object to cancel any changes that have been applied to the source data as shown.

If there is no error, and all three SQL statements have successfully executed, you would normally call the Commit method of the Transaction object to permanently apply the changes to the data store. However, in this example, check the number of seconds in the current time and only call Commit if this is an odd number. If it's an even number, you can call the Rollback method to abandon all updates.

  'specify the select statement to use for the first update   objCommand.CommandText = strSQL1     'execute the SQL statement against the command to fill the DataReader   'keep track of number of records originally updated   intRowsAffected += objCommand.ExecuteNonQuery()     'repeat using the select statement for the second update   objCommand.CommandText = strSQL2   intRowsAffected += objCommand.ExecuteNonQuery()     'repeat using the select statement for the third update   objCommand.CommandText = strSQL3   intRowsAffected += objCommand.ExecuteNonQuery()     Catch objError As Exception     'error encountered so roll back all the updates   objTransaction.Rollback()   'display error details   outError.InnerHtml = "* Error while updating original data.<br />" _   & objError.Message & "<br />" & objError.Source   Exit Sub ' and stop execution     End Try     'all seems OK so can now commit all the updates. However as an   'illustration of the technique only do so if the current time   'has an odd number of seconds. If not, rollback all changes   Dim strCommit As String   If Second(datNow) Mod 2 = 0   objTransaction.Rollback()   strCommit = "rolled back"   Else   objTransaction.Commit()   strCommit = "committed"   End If  

Afterwards you can read the values of the rows using a DataReader object and display them in the page. This is identical to the way you did it in the first example in this chapter, so the code is not repeated here.

Having looked briefly at how you can use transactions to ensure multiple data updates all succeed, or all fail, we'll move on to a different topic. The DataSet object introduced in previous chapters has the facility to automatically update the source data from which it was created “or in fact any data store for which the structure and contents of the tables within the DataSet are of the appropriate format. This is the focus of the next section.