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.

Answer 3

Take a look at Tim Schreiber solution which is simple yet powerful and implemented using repository pattern and has Dapper Transactions in mind.

The Commit() in the code below shows it.

public class UnitOfWork : IUnitOfWork
{
    private IDbConnection _connection;
    private IDbTransaction _transaction;
    private IBreedRepository _breedRepository;
    private ICatRepository _catRepository;
    private bool _disposed;

    public UnitOfWork(string connectionString)
    {
        _connection = new SqlConnection(connectionString);
        _connection.Open();
        _transaction = _connection.BeginTransaction();
    }

    public IBreedRepository BreedRepository
    {
        get { return _breedRepository ?? (_breedRepository = new BreedRepository(_transaction)); }
    }

    public ICatRepository CatRepository
    {
        get { return _catRepository ?? (_catRepository = new CatRepository(_transaction)); }
    }

    public void Commit()
    {
        try
        {
            _transaction.Commit();
        }
        catch
        {
            _transaction.Rollback();
            throw;
        }
        finally
        {
            _transaction.Dispose();
            _transaction = _connection.BeginTransaction();
            resetRepositories();
        }
    }

    private void resetRepositories()
    {
        _breedRepository = null;
        _catRepository = null;
    }

    public void Dispose()
    {
        dispose(true);
        GC.SuppressFinalize(this);
    }

    private void dispose(bool disposing)
    {
        if (!_disposed)
        {
            if(disposing)
            {
                if (_transaction != null)
                {
                    _transaction.Dispose();
                    _transaction = null;
                }
                if(_connection != null)
                {
                    _connection.Dispose();
                    _connection = null;
                }
            }
            _disposed = true;
        }
    }

    ~UnitOfWork()
    {
        dispose(false);
    }
}

Answer 4

There are two intended ways to use transactions with Dapper.

1. Pass your IDbTranasction to your normal Dapper call.

   Before:

   var affectedRows = connection.Execute(sql, new {CustomerName = "Mark"});
   

   After:

   var affectedRows = connection.Execute(sql, new {CustomerName = "Mark"}, transaction=tx);
   

2. Use the new .Execute extension method that Dapper adds to IDbTransaction itself:

   tx.Execute(sql, new {CustomerName = "Mark"});
   

Note: the variable tx comes from IDbTransaction tx = connection.BeginTransaction();

This is how you're supposed to use transactions with Dapper; neither of them are TranasctionScope.

Bonus Reading

• https://stackoverflow.com/a/67474832/12597