Professional ASP.NET 1.1 [Electronic resources]

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.

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.

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.