Proper way of using BeginTransaction with Dapper.IDbConnection

.net, c#, dapper, idbconnection, orm

Solution

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

Problem

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.

Original source

Related problems