ASP.Net designing data access layer for lesser known databases

Question

We are in the process of refactoring entire code base of an ASP.Net web application. Almost the entire application is using sql statements from c# code to query database and the similiar sql statements are scattered across the entire codebase. The application is using Mimer database which is not a commonly used database.

What could be a good refactoring plan for such an application, specially for the data access layer so as to remove duplicate code ensuring DRY?

Answer 1

Implement a layer of abstraction over the database implementation, such that your application calls methods like GetUser() and UpdateWidget() instead of IDbCommand.ExecuteReader() and friends.

Those methods will exist on interfaces, one for each entity type.

IUserRepository
IWidgetRepository

You should have one implementer of each interface for each database platform.

MySqlUserRepository
MySqlWidgetRepository

MimerUserRepository
MimerWidgetRepository

Then create a factory class which dishes out instances of the correct implementing class of the requested entity type for the relevant database platform.

The factory class should be instantiated with knowledge of what database platform the application is currently configured to use. It should use this information to choose which class to instantiate and return when asked.

class RepoFactory
{
    string _platform;
    public RepoFactory(string platform) { this._platform = platform; }

    public IUserRepository GetUserRepository()
    {
        if (_platform == "mysql") return new MySqlUserRepository();
        if (_platform == "mimer") return new MimerUserRepository();
        throw new NotSupportedException(_platform);
    }

    public IWidgetRepository GetWidgetRepository() // ...
}

Keep a reference to a single instance of your RepoFactory so that you only have to set it up once from configuration.

Stick all the database platform-specific stuff in the implementer classes, job done. (You have all the queries you'll need for Mimer already written - you just need to transplant them from your app into this new data layer.)