Transactions in C# with Dapper, Oracle vs MS

Question

I have this code which I used for some time:

using Dapper.Contrib;
using Dapper.Contrib.Extensions;
...
        async Task DBStuff()
        {
            OracleConnection conn = new OracleConnection();
            //SqlConnection conn = new SqlConnection();
            await conn.OpenAsync();
            using (var tran = await conn.BeginTransactionAsync())
            {
                var sql = "insert stuff...";
                await conn.ExecuteAsync(sql);
            }
        }

It works flawlessly. However when I switch the connection from OracleConnection to SqlConnection suddenly I get this error at conn.ExecuteAsync(sql): "BeginExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction... "

I can get rid of error if I pass the transaction to every conn.ExecuteXXX() like this:

conn.ExecuteAsync(sql, transaction: tran)

Is there a way to make it work like with OracleConnection, i.e without having to pass the transaction every time?

According to this post (Performing an Oracle Transaction using C# and ODP.NET) Oracle doesn't need or use additional transaction settings:

The OracleCommand automatically "reuses" the transaction that is currently active on the command's OracleConnection

Note. See [Using async disposable](https://learn.microsoft.com/en-us/dotnet/standard/garbage-collection/implementing-disposeasync#using-async-disposable). You should write `await using SqlConnection conn = ...`, `await using (var tran = ...`. — Alexander Petrov, May 21 '22 at 08:37
See https://stackoverflow.com/a/67474832/5045688. Third example: Dapper Transaction. — Alexander Petrov, May 21 '22 at 08:38

score 0 · Answer 1 · answered May 25 '22 at 20:16

In the end I decided to drop Dapper transactions and use TransactionScope. I'm writing this so maybe will help someone and to correct some comments that I found about this subject. The code will look like this:

using System.Transactions;
...

        using (var transactionScope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))            
        {
            SqlConnection conn = new SqlConnection();
            var sql = "insert stuff...";
            await conn.ExecuteAsync(sql);

            transactionScope.Complete();
        }

Note that in order to work with Async methods TransactionScopeAsyncFlowOption.Enabled option must be used. The code has several advantages over Dapper code:

it's simpler
doesn't require to explicitly open the connection
doesn't need a transaction argument passed to conn.ExecuteXXX() methods
doesn't require explicit rollback
works with distributed transactions (a big plus for me but maybe not for everybody because it requires MSDTC)
works with multiple connections

Transactions in C# with Dapper, Oracle vs MS

1 Answers1