Proper way of using BeginTransaction with Dapper.IDbConnection

Question

Which is the proper way of using BeginTransaction() with IDbConnection in Dapper ?

I have created a method in which i have to use BeginTransaction(). Here is the code.

using (IDbConnection cn = DBConnection) {     var oTransaction = cn.BeginTransaction();      try     {         // SAVE BASIC CONSULT DETAIL         var oPara = new DynamicParameters();         oPara.Add("@PatientID", iPatientID, dbType: DbType.Int32);         ..........blah......blah............     }     catch (Exception ex)     {         oTransaction.Rollback();         return new SaveResponse { Success = false, ResponseString = ex.Message };     } } 

When i executed above method - i got an exception -

Invalid operation. The connection is closed.

This is because you can't begin a transaction before the connection is opened. So when i add this line: cn.Open();, the error gets resolved. But i have read somewhere that manually opening the connection is bad practice!! Dapper opens a connection only when it needs to.

In Entity framework you can handle a transaction using a TransactionScope.

So my question is what is a good practice to handle transaction without adding the line cn.Open()... in Dapper ? I guess there should be some proper way for this.

Answer 1

Manually opening a connection is not "bad practice"; dapper works with open or closed connections as a convenience, nothing more. A common gotcha is people having connections that are left open, unused, for too long without ever releasing them to the pool - however, this isn't a problem in most cases, and you can certainly do:

using(var cn = CreateConnection()) {     cn.Open();     using(var tran = cn.BeginTransaction()) {         try {             // multiple operations involving cn and tran here              tran.Commit();         } catch {             tran.Rollback();             throw;         }     } } 

Note that dapper has an optional parameter to pass in the transaction, for example:

cn.Execute(sql, args, transaction: tran); 

I am actually tempted to make extension methods on IDbTransaction that work similarly, since a transaction always exposes .Connection; this would allow:

tran.Execute(sql, args); 

But this does not exist today.

TransactionScope is another option, but has different semantics: this could involve the LTM or DTC, depending on ... well, luck, mainly. It is also tempting to create a wrapper around IDbTransaction that doesn't need the try/catch - more like how TransactionScope works; something like (this also does not exist):

using(var cn = CreateConnection()) using(var tran = cn.SimpleTransaction()) {     tran.Execute(...);     tran.Execute(...);      tran.Complete(); } 

Answer 2

You should not call

cn.Close(); 

because the using block will try to close too. For the transaction part, yes you can use TransactionScope as well, since it is not an Entity Framework related technique. Have a look at this SO answer: https://stackoverflow.com/a/6874617/566608 It explain how to enlist your connection in the transaction scope. The important aspect is: connection are automatically enlisted in the transaction IIF you open the connection inside the scope.