connecting different database using petapoco

Question

we are using petapoco mini ORM which have inline queries use to connect to PostgreSQL database.

public async Task<IEnumerable<P>> FetchExistP(CQ c)
    {
        var query = "select * from \"P\"where \"Pr\"= " + c.M;
        var database =  BuildDatabase(c.DatabaseId.ToString(CultureInfo.InvariantCulture));
        return await database.FetchAsync<P>(query);
    }

Now we want to support SQLserver database as well.Since queries in PostgreSQl uses double quote hence we need to modify syntax for every query what should we do for this.

1. should we use factory design pattern or some other design pattern and create duplicate repository class files for SQL version ?

2. do we have any utility which takes query and based on database converts syntax accordingly ?

3. Any other option ?

Answer 1

First, you should have your app setup as DataAccess layer uses Repository layer for all CRUD actions. You BusinessLogic only uses the DataAccess layer. So, your app is passing around POCO objects that relate to your database objects.

Now, you implement dependency injection so that your DataAccess layer is not instantiating a Repository object directly, but requesting a typeof Repository. Your current Repository is implemented with PostgreSQL; your new Repository will be implemented with SQL Server. Both Repositories will implement the same interface so that the DataAccess layer does not need to know anything about the database. When you are ready to switch databases, you change your dependency injection configuration to return the SQL Server Repository objects.

Here is an easy framework for dependency injection: Ninject