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