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?

Are you meanig to ask: When do I need to create a generic ...? and why? — Jimmy Chandra, Jul 10 '09 at 05:22
Be careful about the usage of the word 'Generic' when talking about C#, as it has a very specific meaning unrelated to this question. — CaptainCasey, Jul 10 '09 at 05:42
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 — hadi teo, Jul 10 '09 at 05:35

score 5 · Accepted Answer · answered Jul 10 '09 at 05:33

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

+1 from me. In the factory I use the providerName in the connection string to map to a providerSettings for provider-specific things like parameter prefixes (e.g. "@" for MSSQL, ":" for Oracle, etc.). — devstuff, Jul 10 '09 at 10:49

Ricardo · Answer 2 · 2009-07-10T12:04:51.287

0

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.

edited Jul 10 '09 at 12:04

answered Jul 10 '09 at 10:31

Ricardo

1,015
4
13
24

You dont need Y type parameter here since DbCommand can be instantiated from connection object, like conn.CreateCommand(). Also what is W type parameter for? – nawfal Jul 28 '15 at 07:42

score 0 · Answer 3 · edited May 23 '17 at 11:44

You should use the IDbConnection and IDbCommand since different database vendors will have different implementation of the interfaces (for their ADO.NET thing), but better not exactly as you have posted. You should instead make the entire class generic to support generic IDbConnection and IDbCommand. May be like this:

public class Db<T> where T : IDbConnection, new()
{
    public bool CreateConnection()
    {
        T conn;

        //now you dont need these lines since you dont have to worry about
        //what type of db you are using here, since they all implement
        //IDbConnection, and in your case its just T.
        //if(type==DatabaseTye.Oracle)
        //{
            //....
        //}
    }

    public DbDataReader GetData()
    {
        //get comm object from S conn
        using(var conn = new S())
            using (var comm = conn.CreateCommand())

        //...
    }

The benefit is that you can pass to this class the types you want to use for DbConnection and DbCommand which will be different for MySQL .net connector and Oracle one. So you have some sort of control from outside the class. You can see to this question and my answer for reference for a basic implementation.

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

3 Answers3