C# work with database which type is not known in advance

Question

I have an app that collects data and writes it to a database. The database type is not known in advance, it's defined via an .ini file. So I have a method like this, if the database is Firebird SQL:

public bool writeToDB()
{
    FbConnection dbConn = new FbConnection(connString);
    dbConn.Open();
    FbTransaction dbTrans = dbConn.BeginTransaction();
    FbCommand writeCmd = new FbCommand(cmdText, dbConn, dbTrans);

    /* some stuff */

    writeCmd.ExecuteNonQuery();
    dbTrans.Commit();
    writeCmd.Dispose();
    dbConn.Close();
    return true;
}

To make the same work for e.g. MS Access database, I only have to replace FbConnection, FbTransaction and FbCommand with OleDbConnection, OleDbTransaction and OleDbCommand respectively.

But I don't want to have a separate identical method for each type of database. Is it possible to define the database connection / transaction / command type at runtime, after the database type is known?

Thanks

Answer 1

When you're writing code at this level - opening and closing connections, creating and executing commands - there's probably no benefit in trying to make this method or class database-agnostic. This code is about implementation details so it makes sense that it would be specific to an implementation like a particular database.

But I don't want to have a separate identical method for each type of database.

You're almost certainly better off having separate code for separate implementations. If you try to write code that accommodates multiple implementations it will be complicated. Then another implementation (database) comes along which almost but doesn't quite fit the pattern you've created and you have to make it even more complicated to fit that one in.

I don't know the specifics of what you're building, but "what if I need a different database" is usually a "what if" that never happens. When we try to write one piece of code that satisfies requirements we don't actually have, it becomes complex and brittle. Then real requirements come along and they're harder to meet because our code is tied in knots to do things it doesn't really need to do.

That doesn't mean that all of our code should be coupled to a specific implementation, like a database. We just have to find a level of abstraction that's good enough. Does our application need to interact with a database to save and retrieve data? A common abstraction for that is a repository. In C# we could define an interface like this:

public interface IFooRepository
{
    Task<Foo> GetFoo(Guid fooId);
    Task Save(Foo foo);
}

Then we can create separate implementations for different databases if and when we need them. Code that depends on IFooRepository won't be coupled to any of those implementations, and those implementations won't be coupled to each other.

Answer 2

First (and Second and Third). STOP REINVENTING THE WHEEL.

https://learn.microsoft.com/en-us/ef/core/providers/?tabs=dotnet-core-cli

Alot of code and an alot of better testing has already been done.

Guess what is in that larger list:

FirebirdSql.EntityFrameworkCore.Firebird Firebird 3.0 onwards

EntityFrameworkCore.Jet Microsoft Access files

......

So I'm gonna suggest something in lines with everyone else. BUT also... allows for some reuse.

I am basing this .. on the fact the Entity Framework Core...provides functionality to several RDBMS.

See:

https://learn.microsoft.com/en-us/ef/core/providers/?tabs=dotnet-core-cli

public interface IEmployeeDomainDataLayer
{
    Task<Employee> GetSingle(Guid empKey);
    Task Save(Employee emp);
}


public abstract class EmployeeEntityFrameworkDomainDataLayerBase : IEmployeeDomainDataLayer
{
     /* you'll inject a MyDbContext into this class */

    //implement Task<Employee> GetSingle(Guid empKey); /* but also allow this to be overrideable */
    //implement Task Save(Employee emp); /* but also allow this to be overrideable */
}

public class EmployeeJetEntityFrameworkDomainDataLayer : EmployeeEntityFrameworkDomainDataLayerBase, IEmployeeDomainDataLayer
{

    /* do not do any overriding OR override if you get into a jam */
}


public class EmployeeSqlServerEntityFrameworkDomainDataLayer : EmployeeEntityFrameworkDomainDataLayerBase, IEmployeeDomainDataLayer
{

    /* do not do any overriding OR override if you get into a jam */
}

You "code to an interface, not an implementation". Aka, your business layer codes to IEmployeeDomainDataLayer.

This gives you most code in EmployeeEntityFrameworkDomainDataLayerBase. BUT if any of the concretes give you trouble, you have a way to code something up ONLY FOR THAT CONCRETE.

If you want DESIGN TIME "picking of the RDBMS", then you do this:

You inject one of the concretes ( EmployeeJetEntityFrameworkDomainDataLayer OR EmployeeSqlServerEntityFrameworkDomainDataLayer ) into your IOC, based on which backend you want to wire to.

If you want RUN-TIME "picking of the RDMBS", you can define a "factory".

public static class HardCodedEmployeeDomainDataLayerFactory
{
    public static IEmployeeDomainDataLayer getAnIEmployeeDomainDataLayer(string key)
        {
              return new EmployeeJetEntityFrameworkDomainDataLayer();
              // OR (based on key)
              return new  EmployeeSqlServerEntityFrameworkDomainDataLayer();
        }

}

The factory above suffers from IOC anemia. Aka, if your concretes need items for their constructors..you have to fudge them.

A better idea of the above is the kissing cousin of "Factory" pattern, called the Strategy Design.

It is a "kinda factory", BUT you inject the possible results of the "factory" in via a constructor. Aka, the "factory" is NOT hard coded...and does NOT suffer from IOC anemia.

See my answer here:

Using a Strategy and Factory Pattern with Dependency Injection