Proper way of using BeginTransaction with Dapper.IDbConnection

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