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
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
'J3COM', 'Jason Price Corporation'
INSERT INTO Orders (
) VALUES (
You can use the following steps to perform these two INSERT statements using a
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
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
Listing 8.9 illustrates these steps.
Listing 8.9: EXECUTETRANSACTION.CS
ExecuteTransaction.cs illustrates the use of a transaction
public static void Main()
SqlConnection mySqlConnection =
// step 1: create a SqlTransaction object and start the transaction
// by calling the BeginTransaction() method of the SqlConnection
SqlTransaction mySqlTransaction =
// 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
"INSERT INTO Customers (" +
" CustomerID, CompanyName" +
") VALUES (" +
" 'J3COM', 'Jason Price Corporation'" +
// step 5: run the first INSERT statement
Console.WriteLine("Running first INSERT statement");
// step 6: set the CommandText property of the SqlCommand object to
// the second INSERT statement
"INSERT INTO Orders (" +
" CustomerID" +
") VALUES (" +
" 'J3COM'" +
// step 7: run the second INSERT statement
Console.WriteLine("Running second INSERT statement");
// step 8: commit the transaction using the Commit() method
// of the SqlTransaction object
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
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'