C# Resolving database at runtime

Question

I need to be able to support clients on both MySQL and PostGres databases in a legacy application that does not use any ORM. Say, I have the following interface to query a list of users.

public interface IUsers
{
  ...
}

I have two implementations for the above interface. One for MySQL

public class MySQLUsers : IUsers
{
   ...
}

and one for PostGres

public class PostGresUsers: IUsers
{
  ...
}

How can I get the right implementation at runtime? Ideally, I'd like my business layer to be able to call the methods on the interface without having any knowledge of whether the data was coming from MySQL or PostGres. I am able to achieve this currently using the following code but it can quickly get unmaintainable if I have to do this for every single interface that has database code in it.

IUsers GetUsersProvider()
{
  if(something)
  {
    return new MySQLUsers();
  }
  else
  {
    return new PostGresUsers();
  }
}

Are there any recommended patterns I can use to do this?

Answer 1

You can add another layer at the factory level:

public interface IUsers { ... }
public interface IFoo { ... }

public interface Factory {
    IUsers GetUsers();
    IFoo GetFoo();
}

public class MySQLFactory : Factory {
    public IUsers GetUsers() {
        return new MySQLUsers();
    }

    public IFoo GetFoo() {
        return new MySQLFoo();
    }
}

public class PostgresFactory : Factory {
    public IUsers GetUsers() {
        return new PostgresUsers();
    }

    public IFoo GetFoo() {
        return new PostgresFoo();
    }
}

public class DBFactory {
    public static Factory getFactory(bool something) {
        if(something) {
            return new MySQLFactory();
        }
        else {
            return new PostgresFactory();
        }
    }
}

Answer 2

This is where you would use dependency injection. Get a container, check the condition at the start of the program, put the appropriate instances in the container. Then get them back out again at construction time.

This is basically the same as what you have done in GetUsersProvider(), it just happens only once at app startup time in one place instead of scattered everywhere.

There is no solution that doesn't basically boil down to "Do that, but in a nicer way".