Use Dapper transaction between multiple methods in C#

Question

I have a class with many methods with different inserts and updates to a database that uses Dapper for its operations.

I call some of them in one method and want to have one transaction for all of them.

Something like this :

public bool OrderItemChange(int OrderItem)
{
    using (var con = await Connection.GetOpenConnection())
    {
            using (var trans = con.BeginTransaction())
            {
                 try 
                 {
                    // It's better if I don't send 'con' and 'trans' to all these methods
                    Find(OrderItem);
                    Remove(OrderItem);
                    Save(OrderItem);

                    trans.commit();
                 }
                 catch(exception ex)
                 {
                     trans.Rollback();
                 }       
            }
    }
}

I've seen some answers like this one.

but I want to know if there is a better and simpler way than passing SqlConnection and SqlTransaction arguments to each method.

The transaction is associated with the connection. You can't follow the normal "create a connection on every database operation" pattern. Each method will need to get passed that information, or you'll need to make them all be members of the same class and have that class be transaction aware — Flydog57, Jul 16 '23 at 05:50
"if there is a better and simpler way than passing SqlConnection And SqlTransaction" not really, except that you could make it a Unit of Work class, instead of just a method. Then you can keep the conenction and transaction around, and dispose it in a `Dispose` method — Charlieface, Jul 16 '23 at 06:00
Your example doesn't compile. It's a method returning `bool`, but you're never returning `bool`. Please give a [mcve] when asking a question. — JHBonarius, Jul 16 '23 at 08:25

score 2 · Accepted Answer · answered Jul 16 '23 at 06:44

You will need to pass in the tras in order to make each new connection aware of the transaction.

Find(OrderItem, trans);
Remove(OrderItem, trans);
Save(OrderItem, trans);

Then, when you open a new connection:

using conn = new SqlConnection(ConnectionString);
var count = conn.Execute(sql, transaction: trans);

score 2 · Answer 2 · answered Jul 16 '23 at 07:03

You can use the TransactionScope. But you will need to pass the con to methods.

using System.Data;
using System.Transactions;
using Dapper;

public class YourClass
{
    public bool OrderItemChange(int orderItem)
    {
        using (var scope = new TransactionScope())
        {
            try
            {
                using (var con = Connection.GetOpenConnection())
                {
                    Find(OrderItem, con);
                    Remove(OrderItem, con);
                    Save(OrderItem, con);

                    scope.Complete(); // Commit transaction
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        return true;
    }
}

Reusing the connection for multiple queries can cause a deadlock in a concurrent system. — beautifulcoder, Jul 16 '23 at 13:55

Kenan BAŞDEMİR · Answer 3 · 2023-07-16T09:22:18.820

0

You can perform transaction and error management via a separate static method. So you can separate Connection and BeginTransaction transactions. Example usage is given below:

public static class Transaction 
{
    public static async Task Execute(Action<SqlConnection> action) 
    {
        using(var trans = new TransactionScope()) 
        {
            using(var con = await Connection.GetOpenConnection()) 
            {
                try 
                {
                    action(con);
                    trans.Complete();
                } 
                catch (Exception ex) 
                {
                    throw;
                }
            }
        }
    }
}

You can use the example as follows.

public async Task OrderItemChange(int OrderItem) 
{
    await Transaction.Execute(con => 
    {
        Find(con, OrderItem);
        Remove(con, OrderItem);
        Save(con, OrderItem);
    });
}

edited Jul 16 '23 at 09:22

answered Jul 16 '23 at 06:54

Kenan BAŞDEMİR

57
9

Although this works, it's reinventing the existing [`TransactionScope`](https://learn.microsoft.com/en-us/dotnet/api/system.transactions.transactionscope). – JHBonarius Jul 16 '23 at 08:05
1

Thank you for your returns, I've edited by adding the use of TransactionScope. @JHBonarius – Kenan BAŞDEMİR Jul 16 '23 at 08:18
1

Ok, well... that was not what I'm saying. And Vivek Nuna already showed how to use it. Anyhow, second thing is that `catch (Exception ex) { throw; }` doesn't really do anything. So why would you need it? – JHBonarius Jul 16 '23 at 08:23
Might want to check for method error, that's why I added it. @JHBonarius – Kenan BAŞDEMİR Jul 16 '23 at 09:19

Use Dapper transaction between multiple methods in C#

3 Answers3