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 a program tries to subtract money from one account and 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 transaction. If the program gets halfway through the transaction and then crashes, the database engine unwinds the transaction when the database restarts, 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.

Example program Transactions, which is available for download on the book’s website, uses the following code to perform two operations within a single transaction. This code removes an amount of money from one account and adds the same amount to another account.

' Make a transfer.
Private Sub btnUpdate_Click() Handles btnUpdate.Click
    ' Open the connection.
    Using conn_accounts As New OleDbConnection(MakeConnectString())
        conn_accounts.Open()
 
        ' Make the transaction.
        Dim trans As OleDbTransaction =
            conn_accounts.BeginTransaction(IsolationLevel.ReadCommitted)
 
        ' Make a Command for this connection.
        ' and this transaction.
        Dim cmd As New OleDbCommand(
            "UPDATE Accounts SET Balance=Balance + ? WHERE AccountName=?",
            conn_accounts,
            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(conn_accounts)
 
        ' Close the connection.
        conn_accounts.Close()
    End Using
End Sub

The code first creates a connection. It uses the MakeConnectString function to build an appropriate connection string.

Next the code uses the connection’s BeginTransaction method to make the transaction object trans.

The code then defines an OleDbCommand object named cmd, setting its command text to the following text:

UPDATE Accounts 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. If you click Yes, the program calls the transaction’s Commit method and both of the update operations occur. If 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.

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/2k2hy99x.aspx gives an overview of using transactions.