Transaction Objects

A transaction defines a set of database actions that should be executed “atomically” as a single unit. Either all of them should occur or none of them should occur, but no action should execute without all of the others.

The classic example is a transfer of money from one account to another. Suppose that the program tries to subtract money from one account and then add it to another. After it subtracts the money from the first account, however, the program crashes. The database has lost money - a bad situation for the owners of the accounts.

On the other hand, suppose that the program performs the operations in the reverse order: first it adds money to the second account and then subtracts it from the first. This time if the program gets halfway through the operation before crashing, the database has created new money - a bad situation for the bank.

The solution is to wrap these two operations in a database transaction. If the program gets halfway through the transaction and then crashes, the database engine unwinds the transaction, so the data looks as if nothing had happened. This isn’t as good as performing the whole transaction flawlessly, but at least the database is consistent and the money has been conserved.

To use transactions in Visual Basic, the program uses a connection object’s BeginTransaction method to open a transaction. It then creates command objects associated with the connection and the transaction, and it executes them. When it has finished, the program can call the transaction object’s Commit method to make all the actions occur, or it can call Rollback to cancel them all.

The following code shows an example. The program begins by defining a connection string. When it starts, the program uses the string to open a database connection. It calls the subroutine ShowValues and closes the connection.

The subroutine ShowValues creates a command object that selects records from the Accounts table. It executes the command and gets an OleDbDataReader to process the results. It then loops through the returned records building a string holding the records’ AccountName and Balance fields, and displays the result in the program’s txtValues text box.

When the user clicks the Update button, the program reopens the connection and uses its BeginTransaction method to make the transaction object trans. Next, the code defines an OleDbCommand object named cmd, setting its command text to “UPDATE People SET Balance= Balance + ? WHERE AccountName=?” Note that it passes the transaction object into the command object’s constructor to make the command part of the transaction.

The question marks in the command text represent parameters to the command. The program defines the parameters’ values by adding two parameter objects to the command object. It then calls the command’s ExecuteNonQuery method to perform the query.

The code clears the command’s parameters, adds two parameters with different values and calls the command’s ExecuteNonQuery method again.

Now the program displays a message box asking whether you want to commit the transaction. When you click Yes, then the program calls the transaction’s Commit method and both of the update operations occur. When you click No, the program calls the transaction’s Rollback method and both of the update operations are canceled.

The program finishes by calling ShowValues to display the updated data and by closing the connection.

  Imports System.Data Imports System.Data.OleDb Public Class Form1     Private Const CONNECT_STRING As String = _         "Provider=Microsoft.Jet.OLEDB.4.0;" & _         "Data Source=C:\VB Prog Ref\" & _             "CeSrc\Ch11\Transactions\CustomerAccounts.mdb;" & _         "Persist Security Info=False"     Private Sub Form1_Load(ByVal sender As System.Object, _      ByVal e As System.EventArgs) Handles MyBase.Load         ' Open the connection.         Dim connAccounts As New OleDbConnection(CONNECT_STRING)         connAccounts.Open()         ' Display the current balances.         ShowValues(connAccounts)         ' Close the connection.         connAccounts.Close()     End Sub     ' Display the account values.     Private Sub ShowValues(ByVal conn As OleDbConnection)         Dim txt As String = ""         Dim select_cmd As New OleDbCommand( _             "SELECT * FROM Accounts", _             conn)         Dim reader As OleDbDataReader = select_cmd.ExecuteReader()         Do While reader.Read()             txt &= _                reader.Item("AccountName").ToString & ": " & _                reader.Item("Balance").ToString & vbCrLf         Loop         txtValues.Text = txt     End Sub     Private Sub btnUpdate_Click(ByVal sender As System.Object, _      ByVal e As System.EventArgs) Handles btnUpdate.Click         ' Open the connection.         Dim connAccounts As New OleDbConnection(CONNECT_STRING)         connAccounts.Open()         ' Make the transaction.         Dim trans As OleDbTransaction = _             connAccounts.BeginTransaction(IsolationLevel.ReadCommitted)         ' Make a Command for this connection.         ' and this transaction.         Dim cmd As New OleDbCommand( _             "UPDATE Accounts SET Balance=Balance + ? WHERE AccountName=?", _             connAccounts, _             trans)         ' Create parameters for the first command.         cmd.Parameters.Add(New OleDbParameter("Balance", _             Decimal.Parse(txtAmount.Text)))         cmd.Parameters.Add(New OleDbParameter("AccountName", _             "Alice's Software Emporium"))         ' Execute the second command.         cmd.ExecuteNonQuery()         ' Create parameters for the second command.         cmd.Parameters.Clear()         cmd.Parameters.Add(New OleDbParameter("Balance", _             -Decimal.Parse(txtAmount.Text)))         cmd.Parameters.Add(New OleDbParameter("AccountName", _             "Bob's Consulting"))         ' Execute the second command.         cmd.ExecuteNonQuery()         ' Commit the transaction.         If MessageBox.Show( _             "Commit transaction?", _             "Commit?", _             MessageBoxButtons.YesNo, _             MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes _         Then             ' Commit the transaction.             trans.Commit()         Else             ' Rollback the transaction.             trans.Rollback()         End If         ' Display the current balances.         ShowValues(connAccounts)         ' Close the connection.         connAccounts.Close()     End Sub End Class  

Instead of clicking Yes or No when the program asks if it should commit the transaction, you can use the IDE to stop the program. When you then restart the program, you will see that neither update was processed.

In addition to the Commit and Rollback methods, transaction objects may provide other methods for performing more complex transactions. For example, the OleDbTransaction class has a Begin method that enables you to create a nested transaction. Similarly, the SqlTransaction class has a Save method that creates a “savepoint” that you can use to roll back part of the transaction. See the online help for the type of transaction object you are using to learn about these methods. The web page http://msdn.microsoft.com/library/en-us/cpguide/html/cpconperformingtransactionusingadonet.asp gives an overview or using transactions. Links at the bottom lead to information about the OleDbTransaction, SqlTransaction, and OdbcTransaction classes.