Data Access Layer design : Different providers same tables and singleton

Question

I need to build a Data Access Library to be used from many small applications afterwards.

It will heavily use the DataReader objects. The tables may exist with same structure either in SQL Servers or in DB2/400. This means that a method for example

GetItemsByWarehouse()

Must be able to run either against SQL Server DB or DB2. Where it will run depends on the server availability and user selection.

What i plan to do (and need advice on it) is :

1. Implement the DAL based on Singleton design Pattern to ensure that i will have only one instance of my Library.
2. Have a property that will set the connection string.
3. Have a property that will set if the target server is AS400 or SQL.

I dont know if this course of action is correct. Should i implement point #3 or i could get the type from the connection string?

Also How i should implement such a method as above? check the property and decide inside the method if i will use Sqlconnection or OleDbConnection e.t.c?

Answer 1

I paste this code from my micro Orm . There are multiple overloads for the constructor to specify what Db you want used.

 public class DbAccess : IDisposable
{
    public DbAccess()
    {
        var cnx=ConfigurationManager.ConnectionStrings[0];
        if (cnx==null) throw new InvalidOperationException("I need a connection!!!");

        Init(cnx.ConnectionString,ProviderFactory.GetProviderByName(cnx.ProviderName));
    }

    public DbAccess(string connectionStringName)
    {
        var cnx = ConfigurationManager.ConnectionStrings[connectionStringName];
        if (cnx == null) throw new InvalidOperationException("I need a connection!!!");

        Init(cnx.ConnectionString, ProviderFactory.GetProviderByName(cnx.ProviderName));
    }

    public DbAccess(string cnxString,string provider)
    {
        Init(cnxString,ProviderFactory.GetProviderByName(provider));
    }

    public DbAccess(string cnxString,DBType provider)
    {
      Init(cnxString,ProviderFactory.GetProvider(provider));
    }

    public DbAccess(string cnxString,IHaveDbProvider provider)
    {
        Init(cnxString, provider);
    } //other stuff
   }

Note that the DAO (DbAccess) doesn't care about the concrete provider. Here's how the ProviderFactory looks. Here you can add a method to detect the db and to return a provider.

   internal static class ProviderFactory
{
    public static IHaveDbProvider GetProviderByName(string providerName)
    {
        switch (providerName)
        {
            case SqlServerProvider.ProviderName:return new SqlServerProvider();
            case MySqlProvider.ProviderName:return new MySqlProvider();
            case PostgresProvider.ProviderName:return new PostgresProvider();
            case OracleProvider.ProviderName:return new OracleProvider();
            case SqlServerCEProvider.ProviderName:return new SqlServerCEProvider();
            case SqliteProvider.ProviderName:return new SqliteProvider();
        }
        throw new Exception("Unkown provider");
    }

    public static IHaveDbProvider GetProvider(DBType type)
    {
        switch (type)
        {
            case DBType.SqlServer: return new SqlServerProvider();
            case DBType.SqlServerCE: return new SqlServerCEProvider();
            case DBType.MySql: return new MySqlProvider();
            case DBType.PostgreSQL:return new PostgresProvider();
            case DBType.Oracle:return new OracleProvider();
            case DBType.SQLite:return new SqliteProvider();
        }
        throw new Exception("Unkown provider");
    }
}

For more code snippets and inspiration you can check the Github repo

I would advice against the Singleton pattern, it's much better to let a DI container to manage the instance life. Also, the app should use the interface of the DAO not the concrete instance (this will help you in the future).