Visual Basic World

Recipe 13.6. Using Transactions

Problem

You need to issue multiple database updates in the context of a single, atomic transaction.

Solution

Use an ADO.NET transaction to envelop the various SQL statements that need to be processed as a unit.

Discussion

The following block of code connects to a database via ADO.NET and makes several database updates within a single transaction:

	' ----- Connect to the database.
	Dim connectionString As String = _
	   "Data Source=MySystemSQLEXPRESS;" & _
	   "Initial Catalog=MyDatabase;Integrated Security=true"
	Dim theDatabase As New SqlClient.SqlConnection(connectionString)
	theDatabase.Open( )

	' ----- Create a command object that will hold each
	'       processed SQL statement.
	Dim sqlStatement As New SqlClient.SqlCommand
	sqlStatement.Connection = theDatabase

	' ----- Start the transaction.
	Dim theTransaction As System.Data.SqlClient.SqlTransaction
	theTransaction = theDatabase.BeginTransaction( )
	sqlStatement.Transaction = theTransaction

	' ----- Issue the first statement.
	sqlStatement.CommandText = _
	   "UPDATE Table1 SET Column2 = 25 WHERE Column1 = 0"
	sqlStatement.ExecuteNonQuery( )

	' ----- Issue the second statement.
	sqlStatement.CommandText = _
	   "UPDATE Table1 SET Column2 = 50 WHERE Column1 = 1"
	sqlStatement.ExecuteNonQuery( )

	' ----- Finish the transaction.
	theTransaction.
Commit( )

	‘ —– Clean up.
	theTransaction = Nothing
	sqlStatement = Nothing
	theDatabase.Close( )
	theDatabase.Dispose( )

Transactions allow multiple SQL statements to exhibit all-or-nothing behavior. The ADO.NET transaction object is provider-specific and communicates with the target database to manage the atomic nature of the multi-statement transaction.

The SqlTransaction object establishes a transaction for a set of statements in SQL Server. Instead of creating the object directly, use the connection’s BeginTransaction() method to create it. This establishes the new transaction at the database level:

	Dim theTransaction As System.Data.SqlClient.SqlTransaction
	theTransaction = theDatabase.BeginTransaction()

All commands issued while the transaction is in effect need to include the transaction object. Assign the object to each command’s transaction property:

	sqlStatement.Transaction = theTransaction

When you have issued all the commands needed for this transaction, use the transaction object’s Commit() method to permanently write all updates to the database:

	theTransaction.Commit()

If for any reason you need to cancel the changes in the middle of the transaction, use the Rollback() method instead:

	theTransaction.Rollback()