How to use transaction across multiple method calls with Dapper?

Question

I need to wrap some calls to methods that are doing async updates and inserts into my database. All of the methods use this pattern for running the code:

 using (IDbConnection conn = Connection)
            {
                conn.Open();
                //TODO:  Table item quantity for the QTYALLOC field
                var sql = //sql statement;
                int x = await conn.ExecuteAsync(sql);
                return x > 0;
            }

Right now all of the methods return a boolean. I want to wrap the calls in a transaction and either commit or rollback

                    await [InsertRecord];
                    //add the audit record
                    var addAudit = await [Insert Audit Record];
                    var updateOrd = await [Update Record]
                    var changePickStatus = await [Update Record]
                    if (locs.First().QTYTOTAL - ord.QTYPICKED <= 0)
                    {
                        await [Delete Record]; //delete the record

                    }
                    else
                    {
                        //decrement the quantity for the location and update.
                        locs.First().QTYTOTAL -= ord.QTYPICKED;
                        await [Update Record]

                    }

I put the calls to the methods in the square [] brackets. Right now each call returns a boolean to indicate it was successful or failed, I would like to wrap all these calls in one transaction to commit or rollback based on the outcome of each call. I don't want to put all the SQL statements into one large call if I can help it, they are long and complex. Can I pass in a transaction to each method call and apply it to each ExecuteAsync operation? If so what would I pass back from the method to indicate success or failure?

Answer 1

You can inject the connection/transaction in each method call as parameter.

Following is a sort of pseudo code (syntax may not be accurate):

using (IDbConnection conn = Connection)
{
    using(var transaction = conn.BeginTransaction())//Begin here
    {
        var addAudit = await [YourMethod(conn)];//Inject as parameter
        if(addAudit == false)
            transaction.Rollback();//Rollback if method call failed
        ...
        ...
        //Repeat same pattern for all method calls
        ...
        transaction.Commit();//Commit when all methods returned success
    }
}

The better solution is to use Unit Of Work. But, this is only worth if you are implementing it on much broader level.