Introducing Transactions

In Chapter 3, you saw how you can group SQL statements together into transactions. The transaction is then committed or rolled back as one unit. For example, in the case of a banking transaction, you might want to withdraw money from one account and deposit it into another. You would then commit both of these changes as one unit, or if there's a problem, roll back both changes. You'll be introduced to using transactions in ADO.NET in this section.

There are three Transaction classes: SqlTransaction, OleDbTransaction, and OdbcTransaction, and you use an object of one of these classes to represent a transaction in ADO.NET. I'll show you how to use an object of the SqlTransaction class in this section.

Let's consider an example transaction that consists of two INSERT statements. The first INSERT statement will add a row to the Customers table, and the second one will add a row to the Orders table. The new row in the Orders table will reference the new row in the Customers table, and the two INSERT statements are as follows:

 INSERT INTO Customers   CustomerID, CompanyName ) VALUES   'J3COM', 'Jason Price Corporation' ) INSERT INTO Orders (   CustomerID ) VALUES (   'J3COM' ) 

You can use the following steps to perform these two INSERT statements using a SqlTransaction object:

1. Create a SqlTransaction object and start the transaction by calling the BeginTransaction() method of the SqlConnection object.

2. Create a SqlCommand object to hold the SQL statement.

3. Set the Transaction property for the SqlCommand object to the SqlTransaction object created in step 1.

4. Set the CommandText property of the SqlCommand object to the first INSERT statement. This INSERT statement adds a row to the Customers table.

5. Run the first INSERT statement using the ExecuteNonQuery() method of the SqlCommand object. This method is used because an INSERT statement doesn't return a result set.

6. Set the CommandText property of the SqlCommand object to the second INSERT statement. This statement adds a row to the Orders table.

7. Run the second INSERT statement using the ExecuteNonQuery() method of the SqlCommand object.

8. Commit the transaction using the Commit() method of the SqlTransaction object. This makes the two new rows added by the INSERT statements permanent in the database.

Listing 8.9 illustrates these steps.

Listing 8.9: EXECUTETRANSACTION.CS

 /*   ExecuteTransaction.cs illustrates the use of a transaction */ using System; using System.Data; using System.Data.SqlClient; class ExecuteTransaction {   public static void Main()   {     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     mySqlConnection.Open();     // step 1: create a SqlTransaction object and start the transaction     // by calling the BeginTransaction() method of the SqlConnection     // object     SqlTransaction mySqlTransaction =       mySqlConnection.BeginTransaction();     // step 2: create a SqlCommand object to hold a SQL statement     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();     // step 3: set the Transaction property for the SqlCommand object     mySqlCommand.Transaction = mySqlTransaction;     // step 4: set the CommandText property of the SqlCommand object to     // the first INSERT statement     mySqlCommand.CommandText =       "INSERT INTO Customers (" +       "  CustomerID, CompanyName" +       ") VALUES (" +       "  'J3COM', 'Jason Price Corporation'" +       ")";     // step 5: run the first INSERT statement     Console.WriteLine("Running first INSERT statement");     mySqlCommand.ExecuteNonQuery();     // step 6: set the CommandText property of the SqlCommand object to     // the second INSERT statement     mySqlCommand.CommandText =       "INSERT INTO Orders (" +       "  CustomerID" +       ") VALUES (" +       "  'J3COM'" +       ")";     // step 7: run the second INSERT statement     Console.WriteLine("Running second INSERT statement");     mySqlCommand.ExecuteNonQuery();     // step 8: commit the transaction using the Commit() method     // of the SqlTransaction object     Console.WriteLine("Committing transaction");     mySqlTransaction.Commit();     mySqlConnection.Close();   } } 

Note 

If you wanted to undo the SQL statements that make up the transaction, you can use the Rollback() method instead of the Commit() method. By default, transactions are rolled back. Always use the Commit() or Rollback() methods to explicitly indicate whether you want to commit or roll back your transactions.

The output from this program is as follows:

 Running first INSERT statement Running second INSERT statement Committing transaction 

If you want to run the program more than once, you'll need to remove the row added to the Customers and Orders table using the following DELETE statements (you can do this using the Query Analyzer tool):

 DELETE FROM Orders WHERE CustomerID = 'J3COM' DELETE FROM Customers WHERE CustomerID = 'J3COM'