Multiple Databases

These scenarios demonstrate how to support multiple databases with the same code. For demonstration purposes, SQL Server and PostgreSQL will be used.

Scenario Prototype

public interface IMultipleDBScenario<TModel>
   where TModel : class, IEmployeeClassification, new()
{
    /// <summary>
    /// Create a new EmployeeClassification row, returning the new primary key.
    /// </summary>
    int Create(TModel classification);

    /// <summary>
    /// Delete a EmployeeClassification row using an object.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined.</remarks>
    void Delete(TModel classification);

    /// <summary>
    /// Delete a EmployeeClassification row using its primary key.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined.</remarks>
    void DeleteByKey(int employeeClassificationKey);

    /// <summary>
    /// Gets an EmployeeClassification row by its name. Assume the name is unique.
    /// </summary>
    TModel? FindByName(string employeeClassificationName);

    /// <summary>
    /// Gets all EmployeeClassification rows.
    /// </summary>
    IList<TModel> GetAll();

    /// <summary>
    /// Gets an EmployeeClassification row by its primary key.
    /// </summary>
    TModel? GetByKey(int employeeClassificationKey);

    /// <summary>
    /// Update a EmployeeClassification row.
    /// </summary>
    /// <remarks>Behavior when row doesn't exist is not defined.</remarks>
    void Update(TModel classification);
}

ADO.NET

In order to support multiple databases with the same code, ADO.NET provides a DbProviderFactory implmentation for each database. This can be used to create the connection, command, and parameter objects.

public class MultipleDBScenario_DbProviderFactory : IMultipleDBScenario<EmployeeClassification>
{
    readonly DbProviderFactory m_ProviderFactory;
    readonly string m_ConnectionString;
    readonly DatabaseType m_DatabaseType;

    public MultipleDBScenario_DbProviderFactory(string connectionString, DatabaseType databaseType)
    {
        m_ConnectionString = connectionString;
        m_DatabaseType = databaseType;

        m_ProviderFactory = databaseType switch
        {
            DatabaseType.SqlServer => Microsoft.Data.SqlClient.SqlClientFactory.Instance,
            DatabaseType.PostgreSql => Npgsql.NpgsqlFactory.Instance,
            _ => throw new NotImplementedException()
        };
    }

Alternately, commands can be created from connections and parameters from commands.

public class MultipleDBScenario_Chained : IMultipleDBScenario<EmployeeClassification>
{
    readonly string m_ConnectionString;
    readonly DatabaseType m_DatabaseType;

    public MultipleDBScenario_Chained(string connectionString, DatabaseType databaseType)
    {
        m_ConnectionString = connectionString;
        m_DatabaseType = databaseType;
    }

    DbConnection OpenConnection()
    {
        DbConnection con = m_DatabaseType switch
        {
            DatabaseType.SqlServer => new Microsoft.Data.SqlClient.SqlConnection(m_ConnectionString),
            DatabaseType.PostgreSql => new Npgsql.NpgsqlConnection(m_ConnectionString),
            _ => throw new NotImplementedException()
        };

        con.Open();
        return con;
    }

Chain

In Chain, each named DataSource exposes database-specific functionality. For functionality that's common across multiple databases, a set of interfaces are offered.

• IClass0DataSource: Raw SQL only.
• IClass1DataSource: CRUD operations. Database reflection.
• IClass2DataSource: Functions and Stored procedures

public class MultipleDBScenario : IMultipleDBScenario<EmployeeClassification>
{
    const string TableName = "HR.EmployeeClassification";
    readonly ICrudDataSource m_DataSource;

    public MultipleDBScenario(ICrudDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        return m_DataSource.Insert(classification).ToInt32().Execute();
    }

    public void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        m_DataSource.Delete(classification).Execute();
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        m_DataSource.DeleteByKey(TableName, employeeClassificationKey).Execute();
    }

    public EmployeeClassification FindByName(string employeeClassificationName)
    {
        return m_DataSource.From<EmployeeClassification>(new { employeeClassificationName })
            .ToObject().Execute();
    }

    public IList<EmployeeClassification> GetAll()
    {
        return m_DataSource.From<EmployeeClassification>().ToCollection().Execute();
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        return m_DataSource.GetByKey(TableName, employeeClassificationKey)
            .ToObjectOrNull<EmployeeClassification>().Execute();
    }

    public void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        m_DataSource.Update(classification).Execute();
    }
}

Dapper

TODO

DbConnector

public class MultipleDBScenario : IMultipleDBScenario<EmployeeClassification>
{
    IDbConnector DbConnector { get; }
    readonly DatabaseType m_DatabaseType;

    public MultipleDBScenario(string connectionString, DatabaseType databaseType)
    {
        if (databaseType == DatabaseType.SqlServer)
        {
            DbConnector = new DbConnector<SqlConnection>(connectionString);
        }
        else
        {
            DbConnector = new DbConnector<NpgsqlConnection>(connectionString);
        }

        //DbConnector.ConnectionType could also be used...
        m_DatabaseType = databaseType;
    }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        string sql = m_DatabaseType switch
        {
            DatabaseType.SqlServer =>
                @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )",
            DatabaseType.PostgreSql =>
                @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    VALUES(@EmployeeClassificationName )
                    RETURNING EmployeeClassificationKey",
            _ => throw new NotImplementedException()
        };

        return DbConnector.Scalar<int>(sql, classification).Execute();
    }

Entity Framework 6

TODO

Entity Framework Core

Conceptually, you just replace .UseSqlServer(SqlServerConnectionString) with .UseNpgsql(PostgreSqlConnectionString) to change databases.

Due to differences in naming conventions between the two, you may find that a naming converter is needed.

/// <summary>
/// CaseConventionConverter allows one DBContext to connect to multiple databases that using
/// differ only in naming conventions.
/// </summary>
public abstract class CaseConventionConverter : IDatabaseConventionConverter
{
    public void SetConvention(ModelBuilder builder)
    {
        if (builder == null)
            throw new ArgumentNullException(nameof(builder), $"{nameof(builder)} is null.");

        foreach (var entity in builder.Model.GetEntityTypes())
        {

            // Replace table names
            entity.SetTableName(ConvertName(entity.GetTableName()));
            entity.SetSchema(ConvertName(entity.GetSchema()));

            var soi = StoreObjectIdentifier.Table(entity.GetTableName(), entity.GetSchema());
            System.Diagnostics.Debug.WriteLine($"Remapping table {entity.GetSchema()}.{entity.GetTableName()}");
            System.Diagnostics.Debug.IndentLevel += 1;

            // Replace column names
            foreach (var property in entity.GetProperties())
                property.SetColumnName(ConvertName(property.GetColumnName(soi)), soi);

            foreach (var key in entity.GetKeys())
                key.SetName(ConvertName(key.GetName()));

            foreach (var key in entity.GetForeignKeys())
                key.SetConstraintName(ConvertName(key.GetConstraintName()));

            foreach (var key in entity.GetIndexes())
                key.SetDatabaseName(ConvertName(key.GetDatabaseName()));
        }
    }

    protected abstract string? ConvertName(string? input);
}

The two most common conventions for PostgreSQL are snake_case and lowercase.

/// <summary>
/// SnakeCaseConverter is used in database where table/columns use the "table_name" convention.
/// </summary>
public sealed class SnakeCaseConverter : CaseConventionConverter
{
    //Based on: https://github.com/avi1989/DbContextForMultipleDatabases, Used with permission.
    //Reference: https://andrewlock.net/customising-asp-net-core-identity-ef-core-naming-conventions-for-postgresql/

    [SuppressMessage("Globalization", "CA1308")]
    [return: NotNullIfNotNull("input")]
    protected override string? ConvertName(string? input)
    {
        if (string.IsNullOrEmpty(input))
            return input;

        var startUnderscores = Regex.Match(input, @"^_+");
        return startUnderscores + Regex.Replace(input, @"([a-z0-9])([A-Z])", "$1_$2").ToLowerInvariant();
    }
}

/// <summary>
/// LowerCaseConverter is used in database where table/columns use the "tablename" convention.
/// </summary>

public sealed class LowerCaseConverter : CaseConventionConverter
{
    [SuppressMessage("Globalization", "CA1308")]
    [return: NotNullIfNotNull("input")]
    protected override string? ConvertName(string? input)
    {
        if (string.IsNullOrEmpty(input))
            return input;

        return input.ToLowerInvariant();
    }
}

No changes were needed to the actual DB access code.

public class MultipleDBScenario : IMultipleDBScenario<EmployeeClassification>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public MultipleDBScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var context = CreateDbContext())
        {
            context.EmployeeClassifications.Add(classification);
            context.SaveChanges();
            return classification.EmployeeClassificationKey;
        }
    }

    public void Delete(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var context = CreateDbContext())
        {
            context.Entry(classification).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            var temp = new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey };
            context.Entry(temp).State = EntityState.Deleted;
            context.SaveChanges();
        }
    }

    public EmployeeClassification? FindByName(string employeeClassificationName)
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassifications.Where(ec => ec.EmployeeClassificationName == employeeClassificationName).SingleOrDefault();
        }
    }

    public IList<EmployeeClassification> GetAll()
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassifications.ToList();
        }
    }

    public EmployeeClassification GetByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
        {
            return context.EmployeeClassifications.Find(employeeClassificationKey);
        }
    }

    public void Update(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var context = CreateDbContext())
        {
            context.Entry(classification).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}

LINQ to DB

TODO

LLBLGen Pro

TODO

NHibernate

TODO

RepoDb

TODO

ServiceStack

TODO