2
\$\begingroup\$

I wanted to write a custom session to act as proxy for a Dapper connection that could support transactions without having to expose any other DB related entities.

At first, I thought of opening a connection upon creation of the session and keeping it open for the whole life of the instance. As I researched about the subject, I've learned that opening and closing the connections as needed would yield better performance because of the connection pool implemented by ADO.NET. So I tried the following implementation:

public class DBSession : IDisposable
{
    private readonly IConnectionFactory _connectionFactory;

    private IDbConnection _activeConnection;
    private IDbTransaction _activeTransaction;

    public DBSession(IConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }


    private void PreExecuteSingleStatement()
    {
        //Open DB connection if no transaction is open.
        if (_activeTransaction != null)
            return;

        _activeConnection = _connectionFactory.GetOpenConnection();
        if (_activeConnection.State != ConnectionState.Open)
            _activeConnection.Open();
    }

    private void PostExecuteSingleStatement()
    {
        //Close DB connection if no transaction is open.
        if (_activeTransaction != null)
            return;

        if (_activeConnection.State == ConnectionState.Open)
            _activeConnection.Dispose();
        _activeConnection = null;
    }        

    public void BeginTransaction()
    {
        if (_activeTransaction != null)
            throw new InvalidOperationException("Não é possível criar uma nova transação pois já há uma transação aberta.");

        if (_activeConnection == null)
            _activeConnection = _connectionFactory.GetOpenConnection();

        if (_activeConnection.State != ConnectionState.Open)
            _activeConnection.Open();

        _activeTransaction = _activeConnection.BeginTransaction();
    }

    public void CommitTransaction()
    {
        if (_activeTransaction == null)
            throw new InvalidOperationException("Não é possível comitar uma transação pois não há nenhuma transação aberta.");
        _activeTransaction.Commit();

        _activeTransaction.Dispose();
        _activeTransaction = null;
        _activeConnection.Dispose();
        _activeConnection = null;
    }

    public void RollbackTransaction()
    {
        if (_activeTransaction == null)
            throw new InvalidOperationException("Não é possível dar rollback numa transação pois não há nenhuma transação aberta.");
        _activeTransaction.Rollback();

        _activeTransaction.Dispose();
        _activeTransaction = null;
        _activeConnection.Dispose();
        _activeConnection = null;
    }


    public void Insert<T>(T value)
    {
        if (value == null)
            throw new ArgumentNullException(nameof(value));


        PreExecuteSingleStatement();
        _activeConnection.Insert<T>(value);
        PostExecuteSingleStatement();

    }

    public void Dispose()
    {
        if (_activeTransaction != null)
        {
            _activeTransaction.Rollback();
            _activeTransaction.Dispose();
            _activeTransaction = null;
        }

        if (_activeConnection != null)
            _activeConnection.Dispose();
    }
}

IConnectionFactory code:

internal interface IConnectionFactory
{
    IDbConnection GetOpenConnection();
}

For now, I only implemented an Insert method. I plan to write other methods for different operations that will follow almost the same steps as this method.

This code seems somewhat convoluted to me. Lots of checks makes it look very hack-y.

Is there a better way to approach this problem?

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.