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.

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

Solution 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.

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

Comments

[Krishnraj Rana]

Yes, you are right, sorry i forgot to remove it. So the link you provided said that you can use TransactionScope with Dapper but you have to write this code - con.Open(). So is it a good practice ??

[Felice Pollano]

of course you have to open the connection before using it

[Vincent Sels]

FFR: this was suggested but rejected as a PR :( github.com/StackExchange/dapper-dot-net/pull/429 Marc also participated in the discussion. It was rejected mainly because there is already duplication between sync/async - adding extension methods for transactions would result in all methods being duplicated 4 times.

[MaYaN]

@marc-gravell - In the case of rollback, do you have to explicitly call tran.RollBack? is the transaction not rolled back automatically on dispose?

[Alex Gurskiy]

Its nice. Have several questions about the solution. What if dont wanna use transactions lets say for usual select queries? So, as I understood, sql will generate code for transactions after commit() or what? Why do I need do BeginTransaction() if I will not use it in query? Can it affect perfomance for queries where I dont need transactions? Please, dont understand me wrong. I just wanna clarify all things before I start use this in production.

[Alex Gurskiy]

So, I think that better is to add flag (useTransaction = false). In that case, creating instance of unitOfWork we can chose strategy that we need. Am I right?

[vaheeds]

You don't need to commit() when your query is just SELECT. So don't worry about performance!. your idea about adding a flag is nice but in fact, it is not necessary. I use it this way and it works like a charm.

[Efe Zaladin]

Could you explain why _transaction is disposed in the finally block although _transaction.RollBack() has been called in the catch block?

[vaheeds]

@EfeZaladin finally block will run for sure, so disposing of the object is neccessary either way. In case of successful try, the _transaction should dispose and if something goes wrong, _transaction should be rollbacked and in both scenarios, it will be disposed of finally.

[Efe Zaladin]

@vaheeds Thanks! It's obvious but I think I saw your answer at a late hour :D I also have an idea and I would like to know it makes sense. Could we open the connection and begin the transaction when a repository's get method is called for the first time, in order not to hog the DB connections when not necessary?

[vaheeds]

@EfeZaladin Generally, you don't need transactions for read-only queries like get. When you're going to perform some single or batch write queries like insert, update or delete, in a row, you will need to begin a transaction to have the ability to roll back in case of any error occurs.

[Efe Zaladin]

@vaheeds No I mean the getter-setter methods. Eg: private ICatRepository _catRepository