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?

Wagers

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();
}

Musset

thanks for your answer. my point is that does Dispose do anything else than call Rollback (in most/all cases)? Is there a reason to call Dispose if one wants to have Rollback in catch clause? I think calling Rollback excplicitly is far better coding. So is Dispose necessary for already committed/rollbacked transaction? – Wagers

@matti Dispose() is strongly advised simply because it is IDisposable. I would agree that in this case I'd prefer explicit Rollback(), as per my example. But that is in addition to Dispose(), not instead of. – Musset

thanks. i think that's good enough reason for me :D but as I said I prefer explictit rollback. that's just little tricky cause if something goes wrong, the transaction is not active any more. So your code example throws an exception about rollback and original cause is missed. For example MSDN had an example where Rollback in catch clause was inside an additional catch-clause... Which is absolutely ridiculous. – Wagers

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.

Dealfish

thanks for your answer. i understand what using means, but why to call Dispose at all if all it does is that it calls rollback? – Wagers

there is no need to explicitly call Rollback if you are using using. imho the code is cleaner to have a using block with a Commit() in it. – Dealfish