CODEWITHSUNDEEP
c#ado.net
user366312
user366312

Reputation: 17026

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

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?

Upvotes: 3

Views: 18256

Answers (3)

nawfal
nawfal

Reputation: 73311

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.

Upvotes: 0

Ricardo
Ricardo

Reputation: 1023

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.

Upvotes: 0

Jimmy Chandra
Jimmy Chandra

Reputation: 6580

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

Upvotes: 5

Related Questions