Writing driver class generic for any database support

Question

For the past few days, I was working with various database such as MySQL,oracle,Ibmdb2 etc which connect with dot net through odbc providers.

For example:

1)MySQL:

Driver={MySQL ODBC 5.1 Driver};server=**********;uid=**;database=**;port=***;pwd=***;"

2)oracle:

Driver={Microsoft ODBC for Oracle};server=**********;uid=**;database=**;port=***;pwd=***;"

3)Db2:

Driver={IBM DB2 ODBC DRIVER};server=**********;uid=**;database=**;port=***;pwd=***;"

now my question is

is it possible to write generic class for any database provider as

Driver={My own driver};server=**********;uid=**;database=**;port=***;pwd=***;"

which connects every database just by changing driver name in web.config and placing that dll file in bin folder of my published web application or website project.

Answer 1

To roll one of your own isn't that big a deal. Here is a basic structure of how I would implement it for bare minimum needs (you can of course expand it):

1) First create an interface specifying the basic functionalities.

interface IDb
{
    IEnumerable<T> Get<T>(string query, Action<IDbCommand> parameterizer, 
                          Func<IDataRecord, T> selector);

    int Add(string query, Action<IDbCommand> parameterizer);

    int Save(string query, Action<IDbCommand> parameterizer);

    int SaveSafely(string query, Action<IDbCommand> parameterizer);

}

2) Create the generic helper class which should not only implements the interface but also should be specified by the type IDbConnection. The class should be better (not necessarily) instantiable (not static) so that you can pass the required connection string to instantiate it.

Here is a fully lazy implementation:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;

public class Db<T> : IDb where T : IDbConnection, new()
{
    string connectionString;

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

    IEnumerable<S> Do<R, S>(string query, Action<IDbCommand> parameterizer, 
                            Func<IDbCommand, IEnumerable<R>> actor, Func<R, S> selector)
    {
        using (var conn = new T())
        {
            using (var cmd = conn.CreateCommand())
            {
                if (parameterizer != null)
                    parameterizer(cmd);
                cmd.CommandText = query;
                cmd.Connection.ConnectionString = connectionString;

                cmd.Connection.Open();

                foreach (var item in actor(cmd))
                    yield return selector(item);
            }
        }
    }

    public IEnumerable<S> Get<S>(string query, Action<IDbCommand> parameterizer, Func<IDataRecord, S> selector)
    {
        return Do(query, parameterizer, ExecuteReader, selector);
    }

    static IEnumerable<IDataRecord> ExecuteReader(IDbCommand cmd)
    {
        using (var r = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            while (r.Read())
                yield return r;
    }

    public int Add(string query, Action<IDbCommand> parameterizer)
    {
        return Do(query, parameterizer, ExecuteReader, r => Convert.ToInt32(r[0])).First();
    }

    public int Save(string query, Action<IDbCommand> parameterizer)
    {
        return Do(query, parameterizer, ExecuteNonQuery, noAffected => noAffected).First();
    }

    static IEnumerable<int> ExecuteNonQuery(IDbCommand cmd)
    {
        yield return cmd.ExecuteNonQuery();
    }

    public int SaveSafely(string query, Action<IDbCommand> parameterizer)
    {
        // 'using' clause ensures rollback is called, so no need to explicitly rollback
        return Do(query, parameterizer, cmd => 
        {
            using (cmd.Transaction = cmd.Connection.BeginTransaction())
            {
                var noAffected = ExecuteNonQuery(cmd);
                cmd.Transaction.Commit();
                return noAffected;
            }
        }, noAffected => noAffected).First();
    }
}

This only does the basic ExecuteNonQuery and ExecuteReader like operations, and simple Transactions. No stored procedures. The Add function works for inserting and retrieving the last inserted id and likes. It was crazy of me to have made things lazy and to have used just one core execution function Do (which is called for various db actions), and that is why Do looks complicated, but its very DRY. Ideally its better to be separated. You can rid of Linq too.

3) Lastly provide static wrapper Db with no generic constraints around the instantiable Db class so that you don't have to keep passing the T parameter every time to do a db query. For instance like this:

public static class Db
{
    static IDb db = GetDbInstance();

    static IDb GetDbInstance()
    {
        // get these two from config file or somewhere
        var connectionString = GetConnectionString();
        var driver = GetDbType();   // your logic to decide which db is being used

        // some sort of estimation of your db
        if (driver == SQLite)
            return new Db<SQLiteConnection>(connectionString);
        else if (driver == MySQL)
            return new Db<MySqlConnection>(connectionString);
        else if (driver == JET)
            return new Db<OleDbConnection>(connectionString);
        //etc

        return null;
    }

    public static void Parameterize(this IDbCommand command, string name, 
                                    object value)
    {
        var parameter = command.CreateParameter();
        parameter.ParameterName = name;
        parameter.Value = value;
        command.Parameters.Add(parameter);
    }

    public static IEnumerable<T> Get<T>(string query, 
                                        Action<IDbCommand> parameterizer, 
                                        Func<IDataRecord, T> selector)
    {
        return db.Get(query, parameterizer, selector);
    }

    public static int Add(string query, Action<IDbCommand> parameterizer)
    {
        return db.Add(query, parameterizer);
    }

    public static int Save(string query, Action<IDbCommand> parameterizer)
    {
        return db.Save(query, parameterizer);
    }

    public static int SaveSafely(string query, Action<IDbCommand> parameterizer)
    {
        return db.SaveSafely(query, parameterizer);
    }
}

4) Now I would create an additional static function GetDbInstance somewhere so that it infers the right database parameters like connection string, provider type etc. Also have an extension method to ease parameterization of queries. I put both of them in the above static Db class but that's your choice (some people write it in the Db class itself but I prefer it outside because the functionality should be your application's).

5) Take care to have neutral queries that work on the databases you prefer.

Or

You can utilize DbProviderFactory under System.Data.Common to detect the type of DbConnection/provider you have. You can have just one non-generic Db class and do:

public class Db
{
    string connectionString;
    DbProviderFactory factory;

    public Db(string driver, string connectionString)
    {
        this.factory = DbProviderFactories.GetFactory(driver);
        this.connectionString = connectionString;
    }

    //and your core function would look like
    IEnumerable<S> Do<R, S>(string query, Action<IDbCommand> parameterizer, 
                            Func<IDbCommand, IEnumerable<R>> actor, 
                            Func<R, S> selector)
    {
        using (var conn = factory.CreateConnection())
        {
            // and all the remaining code..
        }
    }
}

Your GetDbInstance method would look like:

static IDb GetDbInstance()
{
    string connectionString = GetConnectionString();
    string driver = GetDriver();

    return Db(driver, connectionString);
}

Pro: You get rid of the if-else style of programming and the right version of Db class will be instantiated depending on the provider and connection string in the config file.

Con: You need to specify the right provider/driver in the configuration file.

---

A sample query from your C# code would look like:

string query = "SELECT * FROM User WHERE id=@id AND savedStatus=@savedStatus";
var users = Db.Get(sql, cmd =>
{
    cmd.Parameterize("id", 1);
    cmd.Parameterize("savedStatus", true);
}, selector).ToArray();

All you have to do is call Db.Get, Db.Save etc. The function GetDbInstance is the key here which finds the functions in the right dlls to be called, and the helper class manages the resources well while additionally doing its task of various db operations. Such a class would avoid the hassle of opening and closing connections, freeing resources, having to include database dll namespace etc every time. This is what is called DbAL. You can have an additional layer to help DbAL communicate between various strongly typed model classes as well. I simply love the power of polymorphism via interfaces and constraints which is very very OOP! :)