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