Transactions allow you to batch a set of SQL so that all of them either succeed or fail together.

In .NET, it's especially easy to create transactions — using SqlTransaction.Suppose you have a monkey object and a fingers object. When you create a monkey, you would of course want to create the monkey’s fingers, as well. And so, you first create the monkey object, get the ID of that object, and then go create the fingers.

The code might go something like this:

public void CreateMonkey() { Monkey monkey = new Monkey();

monkey.Id = Guid.NewGuid();

//Set other monkey data here

bool result = Insert(monkey);

if (result) { Fingers fingers = new Fingers(10);

monkey.Hand.Fingers = fingers;

result = Insert(monkey.Hand.Fingers);

if (result) Console.WriteLine("Success! Good job!");

else Console.WriteLine("Failure! Dismal, just dismal!"); } }

You’ll run into problems when you use this kind of code, as might be obvious to you already. Suppose you successfully created the monkey, but for some odd reason, creating the fingers failed. What now? You have a monkey without fingers running around, which is sad for the monkey and creepy for us humans.

Yes, this is exactly where transactions come in. Transactions let you batch SQL statements together so that either all of them succeed or all of them fail. So, if you were using transactions, you’d either get “no monkey and no fingers” or “a monkey with fingers.”

.NET 2.0 makes it especially easy to use transactions. ;-) I recently had to work with them, and I was impressed with how intuitive they were.

When you want to use a transaction, you basically need to follow the following steps:

  1. Create a SqlTransaction object
  2. Create a SqlTransaction object
  3. Link the transaction to the SqlConnection before running the first SQL statement of the batch.
  4. Link the transaction to the SqlCommand object of each of the SQL statement of the batch.
  5. If everything succeeds, commit the transaction. If there’s an error, rollback the transaction.

So, the modified code:

public void CreateMonkey() { //Need to initialize it to null because otherwise .NET will // complain that we're using an uninitialized object. SqlTransaction tr = null;

SqlConnection cn = new SqlConnection ("ConnectionString");

try { cn.Open();

tr = cn.BeginTransaction();

string sqlInsertMonkey = "Insert Monkey into database";

SqlCommand cmd = new SqlCommand(sqlInsertMonkey, cn, tr);

if (cmd.ExecuteNonQuery() == 0) throw new Exception("Failed to insert monkey");

string sqlInsertFingers = "Insert Monkey's fingers into database";

cmd = new SqlCommand(sqlInsertFingers, cn, tr);

if (cmd.ExecuteNonQuery() == 0) throw new Exception("Failed to insert fingres");

cmd.ExecuteNonQuery();

//Everything executed successfully, so commit the //transaction tr.Commit();

//Close the SqlConnection cn.Close(); }

catch (Exception ex) { //We need to check this, because the exception might //not be SQL related at all; i.e., the transaction //could have completed successfully and we could //be here because of some other problem. if (tr != null) { tr.Rollback(); cn.Close(); }

//Show the appropriate error message here } }

It’s that easy! :-)