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

Have you considered to use ORM (Object Relational Mapper) like NHibernate?

https://www.hibernate.org/343.html

Answer 2

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...

Answer 3

It's recommended to program using Interface and not the exact implementation. So in your codes, the 2nd option is preferred.

Similar discussion has been discussed in Stack Overflow :

http://stackoverflow.com/questions/383947/what-does-it-mean-to-program-to-an-interface http://stackoverflow.com/questions/570302/when-are-interfaces-needed

Answer 4

We have to remember one thing always. What you ask today, could be pondered or faced by any other yesterday.

Take a look at this, http://www.codeproject.com/KB/aspnet/DbHelper.aspx

Answer 5

Why don't you use generics?

You can define your class like this for example:

public class DBHelper<T, Y, W> where T: DbConnection, new() where Y : DbCommand, new()
{
        private T conn_ = new T();
        private Y comm_ = new Y();            
}

That's what I do to and its really easy to maintain.