Connection Sharing

These scenarios demonstrate how to share a connection. This is especially important when combining two ORMs in the same transaction.

Scenario Prototype

public interface IConnectionSharingScenario<TModel, TConnection, TTransaction, TState>
   where TModel : class, IEmployeeClassification, new()
    where TConnection : DbConnection
    where TTransaction : DbTransaction
{
    /// <summary>
    /// Closes a previous opened connection.
    /// </summary>
    /// <param name="state">ORM specific state such as a context/session</param>
    void CloseConnection(TState state);

    /// <summary>
    /// Closes a previous opened connection and transaction.
    /// </summary>
    /// <param name="state">ORM specific state such as a context/session</param>
    void CloseConnectionAndTransaction(TState state);

    /// <summary>
    /// Open and return a connection that can be used by another ORM.
    /// </summary>
    /// <returns>The open connection and any ORM-specific state such as a context/session.</returns>
    ConnectionResult<TConnection, TState> OpenConnection();

    /// <summary>
    /// Open and return a connection/transaction pair that can be used by another ORM.
    /// </summary>
    /// <returns>The open connection/transaction pair and any ORM-specific state such as a context/session.</returns>
    ConnectionTransactionResult<TConnection, TTransaction, TState> OpenConnectionAndTransaction();

    /// <summary>
    /// Gets an EmployeeClassification row by its primary key, reusing an open connection.
    /// </summary>
    /// <param name="connection">A open database connection</param>
    /// <param name="transaction">An open transaction. May be null.</param>
    TModel? UseOpenConnection(TConnection connection, TTransaction? transaction, int employeeClassificationKey);

    /// <summary>
    /// Returns a connection string.
    /// </summary>
    /// <returns></returns>
    /// <remarks>This should come directly from the config file, not the ORM.</remarks>
    string GetConnectionString();
}

ADO.NET

Not applicable, ADO.NET always works directly on raw connection/transaction objecsts.

Chain

public class ConnectionSharingScenario : IConnectionSharingScenario<EmployeeClassification,
    SqlConnection, SqlTransaction, IOpenDataSource>
{
    readonly SqlServerDataSource m_DataSource;
    readonly string m_ConnectionString;

    public ConnectionSharingScenario(SqlServerDataSource dataSource, string connectionString)
    {
        m_ConnectionString = connectionString;
        m_DataSource = dataSource;
    }

    public void CloseConnection(IOpenDataSource dataSource)
    {
        if (dataSource == null)
            throw new ArgumentNullException(nameof(dataSource), $"{nameof(dataSource)} is null.");

        dataSource.Close();
    }

    public void CloseConnectionAndTransaction(IOpenDataSource dataSource)
    {
        if (dataSource == null)
            throw new ArgumentNullException(nameof(dataSource), $"{nameof(dataSource)} is null.");

        dataSource.TryCommit();
        dataSource.Close();
    }

    public string GetConnectionString()
    {
        return m_ConnectionString;
    }

    public ConnectionResult<SqlConnection, IOpenDataSource> OpenConnection()
    {
        var connection = m_DataSource.CreateConnection();
        var openDataSource = m_DataSource.CreateOpenDataSource(connection);
        return (connection, openDataSource);
    }

    public ConnectionTransactionResult<SqlConnection, SqlTransaction, IOpenDataSource>
        OpenConnectionAndTransaction()
    {
        var connection = m_DataSource.CreateConnection();
        var transaction = connection.BeginTransaction();
        var openDataSource = m_DataSource.CreateOpenDataSource(connection, transaction);
        return (connection, transaction, openDataSource);
    }

    public EmployeeClassification UseOpenConnection(SqlConnection connection, SqlTransaction? transaction,
        int employeeClassificationKey)
    {
        var openDataSource = m_DataSource.CreateOpenDataSource(connection, transaction);
        return openDataSource.GetByKey<EmployeeClassification>(employeeClassificationKey)
            .ToObject<EmployeeClassification>().Execute();
    }
}

Dapper

Not applicable, Dapper always works directly on raw connection/transaction objects.

DbConnector

Not applicable, DbConnector always works directly on either internally created or client provided raw connection/transaction objects.

Entity Framework 6

public class ConnectionSharingScenario : IConnectionSharingScenario<EmployeeClassification,
    SqlConnection, SqlTransaction, OrmCookbookContext>
{
    readonly string m_ConnectionString;
    private Func<OrmCookbookContext> CreateDbContext;

    public ConnectionSharingScenario(Func<OrmCookbookContext> dBContextFactory, string connectionString)
    {
        m_ConnectionString = connectionString;
        CreateDbContext = dBContextFactory;
    }

    public void CloseConnection(OrmCookbookContext context)
    {
        if (context == null)
            throw new ArgumentNullException(nameof(context), $"{nameof(context)} is null.");

        context.Dispose();
    }

    public void CloseConnectionAndTransaction(OrmCookbookContext context)
    {
        if (context == null)
            throw new ArgumentNullException(nameof(context), $"{nameof(context)} is null.");

        var contextTransaction = context.Database.CurrentTransaction;
        contextTransaction.Commit();
        context.Dispose();
    }

    public string GetConnectionString()
    {
        return m_ConnectionString;
    }

    public ConnectionResult<SqlConnection, OrmCookbookContext> OpenConnection()
    {
        var context = CreateDbContext();
        context.Database.Connection.Open(); //Force the connection open since we haven't used it yet.
        var connection = (SqlConnection)context.Database.Connection;
        return (connection, context);
    }

    public ConnectionTransactionResult<SqlConnection, SqlTransaction, OrmCookbookContext>
        OpenConnectionAndTransaction()
    {
        var context = CreateDbContext();
        var connection = (SqlConnection)context.Database.Connection;
        var contextTransaction = context.Database.BeginTransaction();
        var transaction = (SqlTransaction)contextTransaction.UnderlyingTransaction;
        return (connection, transaction, context);
    }

    public EmployeeClassification UseOpenConnection(SqlConnection connection, SqlTransaction? transaction,
        int employeeClassificationKey)
    {
        //Set contextOwnsConnection to false so the underlying connection will not be closed
        using (var context = new OrmCookbookContext(connection, contextOwnsConnection: false))
        {
            if (transaction != null)
                context.Database.UseTransaction(transaction);

            return context.EmployeeClassification.Find(employeeClassificationKey);
        }
    }
}

Entity Framework Core

public class ConnectionSharingScenario : IConnectionSharingScenario<EmployeeClassification,
    SqlConnection, SqlTransaction, OrmCookbookContext>
{
    readonly string m_ConnectionString;
    private Func<OrmCookbookContext> CreateDbContext;

    public ConnectionSharingScenario(Func<OrmCookbookContext> dBContextFactory, string connectionString)
    {
        m_ConnectionString = connectionString;
        CreateDbContext = dBContextFactory;
    }

    public void CloseConnection(OrmCookbookContext context)
    {
        if (context == null)
            throw new ArgumentNullException(nameof(context), $"{nameof(context)} is null.");

        context.Dispose();
    }

    public void CloseConnectionAndTransaction(OrmCookbookContext context)
    {
        if (context == null)
            throw new ArgumentNullException(nameof(context), $"{nameof(context)} is null.");

        context.Database.CommitTransaction();
        context.Dispose();
    }

    public string GetConnectionString()
    {
        return m_ConnectionString;
    }

    public ConnectionResult<SqlConnection, OrmCookbookContext> OpenConnection()
    {
        var context = CreateDbContext();
        context.Database.OpenConnection(); //Force the connection open since we haven't used it yet.
        var connection = (SqlConnection)context.Database.GetDbConnection();
        return (connection, context);
    }

    public ConnectionTransactionResult<SqlConnection, SqlTransaction, OrmCookbookContext>
        OpenConnectionAndTransaction()
    {
        var context = CreateDbContext();
        var connection = (SqlConnection)context.Database.GetDbConnection();
        var contextTransaction = context.Database.BeginTransaction();
        var transaction = (SqlTransaction)contextTransaction.GetDbTransaction();
        return (connection, transaction, context);
    }

    public EmployeeClassification? UseOpenConnection(SqlConnection connection, SqlTransaction? transaction,
                int employeeClassificationKey)
    {
        var options = new DbContextOptionsBuilder<OrmCookbookContext>().UseSqlServer(connection).Options;
        using (var context = new OrmCookbookContext(options))
        {
            if (transaction != null)
                context.Database.UseTransaction(transaction);

            return context.EmployeeClassifications.Find(employeeClassificationKey);
        }
    }
}

LINQ to DB

TODO

LLBLGen Pro

The DataAccessAdapter class needs some small adjustments to support this scenario as the scenario actively exposes objects owned by the adapter and by design this isn't directly supported. To adjust the DataAccessAdapter class we create a small partial class of DataAccessAdapter and add the following code:

/// <summary>
/// Extension of the generated adapter class which allows customization of the active connection / transaction.
/// </summary>
public partial class DataAccessAdapter
{
    private DbConnection _connectionToUse;
    private DbTransaction _transactionToUse;
    
    public void SetConnectionTransaction(DbConnection connectionToUse, DbTransaction transactionToUse=null)
    {
        _connectionToUse = connectionToUse;
        _transactionToUse = transactionToUse;
    }


    protected override void Dispose(bool isDisposing)
    {
        if(_connectionToUse != null || _transactionToUse != null)
        {
            // don't dispose, leave them alive
            return;
        }
        base.Dispose(isDisposing);
    }


    protected override DbConnection CreateNewPhysicalConnection(string connectionString)
    {
        return _connectionToUse ?? base.CreateNewPhysicalConnection(connectionString);
    }


    protected override DbTransaction CreateNewPhysicalTransaction()
    {
        return _transactionToUse ?? base.CreateNewPhysicalTransaction();
    }


    public DbConnection GetConnection()
    {
        return GetActiveConnection();
    }

    
    public DbTransaction GetTransaction()
    {
        return this.PhysicalTransaction;
    }
}

The scenario:

public class ConnectionSharingScenario
    : IConnectionSharingScenario<EmployeeClassificationEntity,
        DbConnection, DbTransaction, DataAccessAdapter>
{
    public void CloseConnection(DataAccessAdapter adapter)
    {
        if(adapter == null)
            throw new ArgumentNullException(nameof(adapter), $"{nameof(adapter)} is null.");

        adapter.CloseConnection();
        adapter.Dispose();
    }


    public void CloseConnectionAndTransaction(DataAccessAdapter adapter)
    {
        if(adapter == null)
            throw new ArgumentNullException(nameof(adapter), $"{nameof(adapter)} is null.");

        adapter.Commit();
        adapter.Dispose();
    }


    public string GetConnectionString()
    {
        using(var adapter = new DataAccessAdapter())
        {
            return adapter.ConnectionString;
        }
    }


    public ConnectionResult<DbConnection, DataAccessAdapter> OpenConnection()
    {
        var adapter = new DataAccessAdapter();
        adapter.OpenConnection();
        var connection = adapter.GetConnection();
        adapter.KeepConnectionOpen = true;
        return (connection, adapter);
    }


    public ConnectionTransactionResult<DbConnection, DbTransaction, DataAccessAdapter>
        OpenConnectionAndTransaction()
    {
        var adapter = new DataAccessAdapter();
        adapter.OpenConnection();
        adapter.StartTransaction(IsolationLevel.ReadCommitted, "OpenConTrans");
        adapter.KeepConnectionOpen = true;
        return (adapter.GetConnection(), adapter.GetTransaction(), adapter);
    }


    public EmployeeClassificationEntity UseOpenConnection(DbConnection connection, DbTransaction? transaction,
                                                          int employeeClassificationKey)
    {
        using(var adapter = new DataAccessAdapter())
        {
            adapter.SetConnectionTransaction(connection, transaction);
            adapter.StartTransaction(IsolationLevel.ReadCommitted, "OpenConnection");
            adapter.KeepConnectionOpen = true;
            return adapter.FetchNewEntity<EmployeeClassificationEntity>(
                                new RelationPredicateBucket(EmployeeClassificationFields.EmployeeClassificationKey
                                            .Equal(employeeClassificationKey)));
        }
    }
}

NHibernate

TODO

RepoDb

TODO

ServiceStack

TODO