Implementing transactions over multiple databases

.net, database, sql-server, transactions

Solution

Use the TransactionScope class like this:

using (TransactionScope ts = new TransactionScope())
{
    //all db code here

    // if an error occurs jump out of the using block and it will dispose and rollback

    ts.Complete();
}

The TransactionScope class will automatically convert to a distributed transaction if necessary.

Problem

I am performing data changes on multiple databases, and I want to implement a transaction that will cover all of the changes. This is what I currently have: ``` try { db[1].begintransaction(); db[1].ExecuteNonQuery(); db[2].begintransaction(); db[2].ExecuteNonQuery(); ... db[N].begintransaction(); db[N].ExecuteNonQuery(); // will execute only if no exception raised during the process for (int a = 0; a < N; a++) { db[a].Commit();// what if there is an error/exception here } } catch { for (int a = 0; a < N; a++) { db[a].RollBack(); } } ``` The problem is that the above would fail horribly if an exception happens during a `Commit()` (see the comment). Is there any better way of accomplishing this?

Original source