Why to use using statement on DbTransaction in ADO.NET / C#?

Question

I understood the implicit rollback (that usually happens when exception happens and Dispose is called) is not guaranteed for all providers. However many examples uses:

using (DbTransactio txn = cnctn.BeginTransaction())

is there a reason for that?

Answer 1

The most plain answer there would be "because it implements IDisposable". Any type that implements IDisposable: it is your job to dispose it appropriately. In this case, with procedural code, the simplest way to do that would be via a using statement.

In this specific case, the answer would be: because in the event of error, you want the transaction rolled back rather than left to the GC. Personally I would probably tend to use catch to Rollback, but one should hope that the Dispose() already does that. I wouldn't rely on it myself, though, unless it was documented. For example:

using(var tran = conn.BeginTransaction()) {
    try {
        // do stuff...
        tran.Commit();
    } catch {
        tran.Rollback();
        throw;
    }
}

Note that in the related case of TransactionScope, the "Dispose() without marking it completed" is the expected way of signalling a rollback:

using(var tran = new TransactionScope()) {
    // do stuff...
    tran.Complete();
}

Answer 2

If you have code like this:

DbTransactio txn = cnctn.BeginTransaction();

// do something which throws an exception

txn.Commit();

the transaction wont be rolled back until the garbage collector decides to collect the transaction object (do note that it will only work if the class that implements IDisposable follows the dispose pattern).

You could of course do this:

DbTransaction txn = cnctn.BeginTransaction();
try
{
    // do something which throws an exception

    txn.Commit();
}
finally  
{
    txn.Dispose();
}

but that's not as readable as

using (var txn = cnctn.BeginTransaction())
{
    // do something which throws an exception

    txn.Commit();
}

Edit:

I understood the implicit rollback (that usually happens when exception happens and Dispose is called) is not guaranteed for all providers.

I haven't seen a single provider which do not support it. Since IDbTransaction do inherit IDisposable all implementations should use Dispose() to rollback the transaction.