how i can use transactions in dapper C#

Question

how i can use transactions in dapper C#. my problem is i need to lock a table so i use a BeginTransaction, i do others transactions and until i finish these transactions i can do of commit, with the code that i show you. it can do it but when i receive 2 transactions at the same time, one transaction does not close good the conection. Thanks.

I want to do this but with dapper.

public int UpdateStan(PosDTO posParams)
{
    int result = cnn.Execute(new Templates.Queries.upDateStan().TransformText(), new

    {
        id_merchant = posParams.idMerchant
    }, transaction);
    transaction.Commit();
    cnn.Close();
    cnn.Dispose();
    return result;

}

public BatchStanDTO getBatchStan(PosDTO posDto)
{
    cnn = _sodexoSource.Create();
    cnn.Open();
    transaction = cnn.BeginTransaction();
    return cnn.Query<BatchStanDTO>(new Templates.Queries.getBatchStan().TransformText(), new { idMerchant = posDto.idMerchant }, transaction).FirstOrDefault();

}

public void rollbackTran()
{
    try
    {
        transaction.Rollback();
    }
    catch (Exception ex)
    {

    }
}

It's very unclear what you are asking based on your post. This looks like Dapper already. What _specific_ problem are you encountering? — maccettura, Aug 17 '17 at 19:57
I would think `TransactionScope` wrapped around your code would work. — Crowcoder, Aug 17 '17 at 19:57
i want to use dapper Wrapper, but i can´t get objetc of conection like cnn.Open(); , welle i don´t understand where the conectios is open. Thanks — Acquiring Development, Aug 17 '17 at 20:21
seems to be some good advice [here](https://stackoverflow.com/questions/10363933/transaction-with-dapper-dot-net) — Crowcoder, Aug 17 '17 at 20:24
When you use `cnn.Query` or `cnn.Execute` in your code, cnn IS your SQLConnection object. You can do all of the normal SqlConnection stuff in addition to the extra methods that Dapper gives you. — Dave Smash, Aug 17 '17 at 20:26
well, my problem is i need to lock a table so i use a BeginTransaction, i do others transactions and until i finish these transactions i can do of commit, with the code that i show you. it can do it but when i receive 2 transactions at the same time, one transaction does not close good the conection. Thanks. — Acquiring Development, Aug 17 '17 at 21:02
Welcome to Stack Overflow! Please [edit] your post to include any additional information you have to your question. Avoid adding this in the comments, as they are harder to read and can be deleted easier. The edit button for your post is just below the post's tags. — Andrew Myers, Aug 17 '17 at 23:28

score 1 · Answer 1 · edited Jul 09 '21 at 08:57

You can create as many transaction as you need them and join those with the using statement. Get your transaction from the current connection and do something like

using(var tranB= connection.BeginTransaction())
{
    using(var tran = connection.BeginTransaction()) {
    try {
        // multiple operations involving connection and tran here

            tran.Commit();
        } catch {
            tran.Rollback();
            throw;
        }
    }
    tranB.Commit();
}

score 0 · Answer 2 · answered Jul 09 '21 at 09:01

You can also use Dapper Transaction. It is the same as Dapper but extends the transaction which you might prefer in terms of code:

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    connection.Open();
    
    using (var transaction = connection.BeginTransaction())
    {
        // Dapper
        var affectedRows1 = connection.Execute(sql, new {CustomerName = "Mark"}, transaction: transaction);
        
        // Dapper Transaction
        var affectedRows2 = transaction.Execute(sql, new {CustomerName = "Mark"});

        transaction.Commit();
    }
}

Code taken from dapper-tutorial

how i can use transactions in dapper C#

2 Answers2