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

Answer 1

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