DbFactory Common Implementation Ideas

I am trying to create a common data layer to connecting to all common database. So I started with creating a DbFactory class.

What are all the things should I consider to proceed in creating this Factory like scalability, performance, security, connection pooling and re-usability. Please advise.

Here is what I started:

public class DbFactory
{
    private DbConnection _dbConnection;

    public DbConnection GetDbConnection(DbType type)
    {
        switch (type)
        {
            case (DbType.Oracle):
                _dbConnection = new OracleConnection();
                break;
            case (DbType.SqlServer):
                _dbConnection = new SqlConnection();
                break;
            case (DbType.Excel):
                _dbConnection = new OleDbConnection();
                break;
            case (DbType.Other):
                _dbConnection = new OdbcConnection();
                break;
            default:
                _dbConnection = new OdbcConnection();
                break;
        }

        return _dbConnection;
    }

    public enum DbType
    {
        SqlServer,
        Oracle,
        Excel,
        Other
    }
}

Solution

I would advise not to do it like this. Using this way violates SRP and OCP from the SOLID principles at minimum. It also looks like YAGNI to me.

A better way of doing this would be to create a simple interface like:

public interface IConnectionFactory
{
    DbConnection GetNewConnection();
}

You could than create an implementation per type of database that you need. For SQL this would look like:

public class SqlConnectionFactory : IConnectionFactory
{
    private readonly string connectionString;

    public SqlConnectionFactory(string connectionString)
    {
         this.connectionString = connectionString;
    }

    public DbConnection GetNewConnection()
    {
         return new SqlConnection(this.connectionString);
    }
}

Depending on the current application you're working on you can inject the correct implementation needed at that moment. For example:

public class SomeServiceNeedingDatabaseConnection
{
     private readonly IConnectionFactory connectionFactory;

     public SomeServiceNeedingDatabaseConnection(IConnectionFactory connectionFactory)
     {
          this.connectionFactory = connectionFactory;
     }

     public void SomeMethodNeedingDatabase()
     {
          using (var connection = this.connectionFactory.GetNewConnection())
          {
                 // Do something with connection
          }
     }
}

Somewhere in your application, most preferable in the composition root of your application you can create the needed implementation of your ConnectionFactory.