Is using for IDbConnection / IDbTransaction safe to use?

Question

Although my assumption may seem subjective, after some research, I have found that it is not uncommon to find developers who prefer the Try/Catch dummy instead of using the Use operator to handle IDbConnection/IDbTransaction (Close / Commit / Rollback).

This is true even for some of the most experienced developers and some new ones. I intentionally do not intend to link to a single question on StackOverflow or the forum links as an example, so people are not offended. From what I found , Using the instructions is safe to use (no pun intended).

Is there something wrong with this? Consider the following code:

 Public Sub Commit() Dim cn As IDbConnection = {CREATE_CONNECTION} Dim tran As IDbTransaction = Nothing cn.Open() Try tran = cn.BeginTransaction 'run some queries here tran.Commit() Catch ex As Exception If Not tran Is Nothing Then tran.Rollback() Throw Finally cn.Close() End Try End Function 

Assume that {CREATE_CONNECTION} is the owner of the Sub space, which creates a connection, depending on the database provider, written in accordance with all possible best practices and not requiring much improvement.

Is there a reason why the above code cannot be rewritten as such:

 Using cn As IDbConnection = {CREATE_CONNECTION} cn.Open() Using tran As IDbTransaction = cn.BeginTransaction 'run some queries here tran.Commit() End Using End Using 

Obviously, version number 2 is more intuitive for what it does. But maybe I am missing something important here? Things like vendor-specific data access library implementations that don't call Transaction.Commit and / or Connection.Close on Dispose internally? Is this approach decommissioned in the near future or is it not considered clear enough in the current programming scheme / best practice? Mono / mobile dev applications do not have debugging support for the Using keyword

I am looking for any answer to support or deny this. Preferably, one who has quotes for the original documentation, something like Do not use Using with IDbTransaction when ... Links to blogs or personal experiences are fine too.

Answer 1

I am fully in touch with you; which should be using , and there is no need for explicit Close() . The deal is a little more complicated; The code shown is certainly full at the moment, but it’s not entirely clear that Dispose() should roll back. In fact, this is what tends to occur in every implementation that I looked at, but it is a little annoying that even DbTransaction (which most providers use) does not actually do this. Contrast to TransactionScope , where it is explicitly defined that a Dispose() without a commit is considered a rollback. Because of this, I tend to use (sorry C #):

 using(var conn = GetOpenConnection()) using(var tran = conn.BeginTransaction()) { try { // TODO: do work tran.Commit(); } catch { tran.Rollback(); throw; } } 

which is somewhere between the two in terms of complexity. It doesn't mess with null -check at least.

Answer 2

What you see is coding developers in accordance with the documentation ("Good thing"). The base class DbTransaction (used to implement transactions of most data providers) clearly points to its documentation :

Dispose should cancel the transaction. However, the Dispose behavior is provider specific and should not replace the Rollback call.