Shared connections/transactions with AsyncLocal.

Example.cs

public async Task<Case> StartCase(int caseId, int userId)
{
    using (var connection = new ConScope(_dataService))
    using (var transaction = new TransScope())
    {
        // A transaction is used inside of the settings service as well.
        // It uses the same "ConScope" and "TransScope" types internally,
        // allowing us to share a single IDbConnection and IDbTransaction.
        var caseContext = await _settingsService.GetSetting<CaseContext>();
        if (caseContext.CurrentCaseId == caseId)
        {
            // It's already started.
            return null;
        }
        
        var caseObject = await connection.Connection.SingleByIdAsync<Case>(caseId);
            
        if (caseObject == null)
        {
            return null;
        }

        caseObject.LastStarted = DateTimeOffset.UtcNow;
        caseObject.UserId = userId;

        await connection.Connection.SaveAsync(caseObject);

        caseContext.CurrentCaseId = caseObject.Id;

        // Saving the settings value on the same transaction.
        await _settingsService.SaveSetting(caseContext);
            
        transaction.Commit();
        
        return caseObject;
    }
}

Files.cs

public class ConScope : IDisposable
{
    private static readonly AsyncLocal<ContextData> _dbConnection = new AsyncLocal<ContextData>();
    private readonly bool _ownsScope;
    
    public ConScope(IDataService dataService)
    {
        if (_dbConnection.Value != null)
        {
            // There is already a previous connection.
            _ownsScope = false;
            _dbConnection.Value.IncrementChildCount();
        }
        else
        {
            _dbConnection.Value = new ContextData(dataService.OpenDbConnection());
            _ownsScope = true;
        }
    }
    
    internal static IDbConnection InternalConnection
    {
        get
        {
            if (_dbConnection.Value == null)
            {
                throw new Exception("No connection scope");
            }

            return _dbConnection.Value.Connection;
        }
    }

    internal static IDbTransaction InternalTransaction
    {
        get
        {
            if (_dbConnection.Value == null)
            {
                throw new Exception("No connection scope");
            }

            return _dbConnection.Value.Transaction;
        }
    }
    
    public IDbConnection Connection => InternalConnection;

    internal static bool RefTransaction()
    {
        if (_dbConnection.Value == null)
        {
            throw new Exception("No connection scope");
        }

        var connection = _dbConnection.Value;
        if (connection.Transaction == null)
        {
            connection.Transaction = connection.Connection.OpenTransaction();
            connection.IncrementTransactionCount();
            return true;
        }
        
        connection.IncrementTransactionCount();
        return false;
    }

    internal static void UnrefTransaction()
    {
        var connection = _dbConnection.Value;
        if (connection == null)
        {
            throw new Exception("No connection scope");
        }

        if (connection.TransactionCount == 0)
        {
            throw new Exception("Invalid transaction unref");
        }
        
        connection.DecrementTransactionCount();

        if (connection.TransactionCount == 0)
        {
            if (!connection.TransactionFinished)
            {
                throw new Exception("Disposed of transaction scope without commiting or rolling back");
            }
            connection.Transaction.Dispose();
            connection.Transaction = null;
        }
    }

    internal static void CommitTransaction()
    {
        var connection = _dbConnection.Value;
        if (connection == null)
        {
            throw new Exception("No connection scope");
        }

        if (connection.Transaction == null)
        {
            throw new Exception("No transaction detected");
        }

        if (connection.TransactionFinished)
        {
            throw new Exception("The transaction is finished");
        }
        
        connection.Transaction.Commit();
        connection.Commmited = true;
        connection.TransactionFinished = true;
    }

    internal static void RollbackTransaction()
    {
        var connection = _dbConnection.Value;
        if (connection == null)
        {
            throw new Exception("No connection scope");
        }

        if (connection.Transaction == null)
        {
            throw new Exception("No transaction detected");
        }

        if (connection.TransactionFinished)
        {
            throw new Exception("The transaction is finished");
        }
        
        connection.Transaction.Rollback();
        connection.RolledBack = true;
        connection.TransactionFinished = true;
    }

    public void Dispose()
    {
        var connection = _dbConnection.Value;
        if (_ownsScope)
        {
            if (connection.ChildCount > 0)
            {
                throw new Exception("Child scopes were still detected.");
            }

            if (connection.TransactionCount > 0)
            {
                throw new Exception("Transaction scopes are alive while connection is being disposed");
            }
            
            connection.Connection.Dispose();
            _dbConnection.Value = null;
        }
        else
        {
            connection.DecrementChildCount();
        }
    }

    internal class ContextData
    {
        public IDbConnection Connection;
        public IDbTransaction Transaction;

        public int ChildCount;
        public int TransactionCount;

        public bool TransactionFinished;
        public bool Commmited;
        public bool RolledBack;
        
        public ContextData(IDbConnection connection)
        {
            Connection = connection;
        }

        public void IncrementChildCount()
        {
            ChildCount++;
        }

        public void DecrementChildCount()
        {
            ChildCount--;
        }

        public void IncrementTransactionCount()
        {
            TransactionCount++;
        }

        public void DecrementTransactionCount()
        {
            TransactionCount--;
        }
    }
}

public class TransScope : IDisposable
{
    private readonly bool _ownsTransaction;
    private bool _commited;
    private bool _rolledback;
    private bool _finished;
    private bool _disposed;
    
    public TransScope()
    {
        _ownsTransaction = ConScope.RefTransaction();
    }

    public void Dispose()
    {
        if (_disposed) return;
        _disposed = true;

        if (_ownsTransaction)
        {
            if (!_finished)
            {
                // If we disposed transaction without commit/rollback, do an implicit rollback.
                ConScope.RollbackTransaction();
            }
        }
        
        ConScope.UnrefTransaction();
    }

    public void Commit()
    {
        if (_finished)
        {
            throw new Exception("This transaction is finished");
        }
        if (!_commited)
        {
            if (_ownsTransaction)
            {
                ConScope.CommitTransaction();
            }
            _commited = true;
            _finished = true;
        }
    }

    public void Rollback()
    {
        if (_finished)
        {
            throw new Exception("This transaction is finished");
        }
        if (!_rolledback)
        {
            if (_ownsTransaction)
            {
                ConScope.RollbackTransaction();
            }
            _rolledback = true;
            _finished = true;
        }
    }
}