C# - A class for Generic database connection, command, reader

Question

Suppose I am designing a class that can handle any database technology to create a connection, execute command and retrieve data, etc.

If I need to create a generic database handling class for existing RDBMSs (like SQL Server, Oracle, FireBird, et.), which .net abstract-class/Interface should I use {DbConnection, DbCommand, DbParameter,...} or {IDbConnection, IDbCommand, IDbParameter,...}?

Should I use the code like

public bool CreateConnection(DatabaseTypeEnum type)
{
    DbConnection conn ;

    if(type==DatabaseTye.Oracle)
    {
        //....
    }    
}

public DbDataReader GetData()
{

    DbCommand comm;
    //...
}

or,

public bool CreateConnection(DatabaseTypeEnum type)
{
    IDbConnection conn ;

    if(type==DatabaseTye.Oracle)
    {
        //....
    } 
}

public IDbDataReader GetData()
{

    IDbCommand comm;
    //...
}

And, Why?

Answer 1

Ermm... totally different question :)

OK, neither...

You are going to violate Open Close Principle when you do that... The switch / if statement in that particular place is making me uncomfortable :).

I'd leave the actual creation to a Factory class and your code should not care if it is talking to a SQL Server or DB2 or Oracle or whatever.

Ideally, your code should only talk to IDbConnection, IDbCommand, etc. or the abstract base class (DbConnection, DbCommand, etc.). Sometimes I do find that you need to upcast to a specific provider tho (like SqlDataReader for using specific methods), but it is quite rare.

The Factory will encapsulate this switch / if statement to a single place so it's easily maintainable. You can further abstract the actual creation in a app.config. So in app.config you choose what type of DB backend you are supporting and the Factory will pick it up from there and create the necessary DB stuffs for you.

See: this. Read about Creating DbProviderFactory and Connection part...