Dapper async and transaction

Question

I'm trying to figure out how to properly use the Dapper with the transaction. But I still think I'm doing something wrong. All the examples I found are without using async.

Could somebody advise me how to do it right?

class DapperAsyncTransaction
{
    private readonly IDbConnection _dbConnection;
    private IDbTransaction _dbTransaction;

    private IDbConnection Connection => _dbTransaction.Connection;

    /// <summary>
    /// Constructor
    /// </summary>
    /// <param name="dbConnection"></param>     
    public DapperAsyncTransaction(
        IDbConnection dbConnection)
    {
        _dbConnection = dbConnection;
        _dbConnection.Open();
        _dbTransaction = _dbConnection.BeginTransaction();
    }

    public async Task Execute()
    {
        try
        {
            await Connection.ExecuteAsync(
            @"insert into Persons(Name, Surname) values" +
            "(@Name, @Surname)",
            param: new { Name = "John", Surname = "Doe" },
            transaction: _dbTransaction);


            _dbTransaction.Commit();
        }
        catch (Exception)
        {
            _dbTransaction.Rollback();
        }
        finally
        {
            _dbTransaction.Dispose();
            _dbTransaction = _dbConnection.BeginTransaction();
        }
    }
}

Explain why you think it is wrong. This example is pretty pointless unless triggers on `Persons` is involved because you are only doing a single operation which will already operate in its own implicit transaction (assuming MSSQL) — Crowcoder, Aug 24 '17 at 12:00
You are disposing the transaction in the finally clause but creating it in the constructor. I would say you implement IDisposable in your class and move the dispose call into proper dispose method. Then you could use you class wrapped nicely around a using block too. — Ε Г И І И О, May 22 '21 at 07:56

score 6 · Accepted Answer · answered Aug 24 '17 at 12:25

6

Just wrap your db access in a Transaction scope with TransactionScopeAsyncFlowOption enabled.

public async Task AddPerson(string name, string surname)
{
   const string sql = "insert into Persons(Name, Surname) values(@Name, @Surname)";  

   using (var tran = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
   using (var connection = await _connectionProvider.OpenAsync()) //Or however you get the connection
   {
     await connection.ExecuteAsync(sql, new{name, surname});
     tran.Complete();
   }
}

answered Aug 24 '17 at 12:25

Magnus

45,362
8
80
118

I'm confused by this example. Surely there's a communication with DB on `.Complete()` or perhaps on `tran` or `connection` disposal (the "COMMIT" part). But these operations are synchronous, right? Isn't this blocking? Aren't we basically losing the async advantage? – freakish Oct 31 '18 at 12:18
@freakish I suppose creating/commit/rollback transaction is not a heavy call and does not need to be async. The heavy part is the actual query. At least there is no `CompleteAsync` function. The only thing `TransactionScopeAsyncFlowOption.Enabled` does it to ensure transaction flow across threads is supported. – Magnus Nov 01 '18 at 14:08
@freakish There is a answer about the issue here: https://stackoverflow.com/a/31154519/468973 – Magnus Nov 01 '18 at 14:17

Dapper async and transaction

1 Answers1