Creating a generic / abstract "DBContext" Class for shared functionality among different DBs

Question

I am working on a C# project that is pulling data from SQL Server, ODBC data-sources and Oracle Databases at different times.

At this point, I have created 3 different classes - Once for each type of DB access. However, I am seeing that 95% of the code is identical between each of the libraries - The main difference being in creating the Connection and Command objects.

As a VERY simple example of what's in the classes, here could be an example:

public class Oracle
{
    static DataTable exec_DT(string query, string conn_str)
    {
        DataTable retVal = new DataTable();

        using (OracleConnection conn = new OracleConnection(conn_str))
            using (OracleCommand cmd = new OracleCommand(query, conn))
            {
                conn.Open();
                using (OracleDataReader rdr = cmd.ExecuteReader())
                {
                    retVal.Load(rdr);
                    rdr.Close();
                }
            }

        return retVal;
    }
}

and

public class SQLServer
{
    static DataTable exec_DT(string query, string conn_str)
    {
        DataTable retVal = new DataTable();

        using (SqlConnection conn = new SqlConnection(conn_str))
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            conn.Open();
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                retVal.Load(rdr);
                rdr.Close();
            }
        }

        return retVal;
    }
}

Basically, I need to get the same kind of return data frmo each of my databases and the methods to do so are near-identical, the difference being the Connection and Command-type objects.

Now, when I look at both connection objects, I see that they both inherit from DbConnection:

public sealed class SqlConnection : DbConnection, ICloneable

public sealed class OracleConnection : DbConnection, ICloneable

But I still can't think of a good way to create a generic / abstract parent class that can stop the need for this kind of code duplication as the DBConnection / DBCommand classes are abstract and, when I try something along the following lines:

using (DbConnection conn = new DbConnection(conn_str))
using (DbCommand cmd = new DbCommand(query, conn))

I get errors such as Cannot create an instance of the abstract class or interface 'System.Data.Common.DbCommand'

I'm very much a newbie and any help / sample code / links to ways this has been done well in the past would really be greatly appreciated!

THANKS!!!

Answer 1

The key here is to step back and think about the problem from another angle. You are duplicating lots of code because you are creating instances of the database and command classes within the method. So inject them instead:

public class SomeDBClass
{
    static DataTable exec_DT(DBConnection conn, DBCommand cmd)
    {
        DataTable retVal = new DataTable();
        conn.Open();
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            retVal.Load(rdr);
            rdr.Close();
        }
        return retVal;
    }
}

This approach has the added value that you can then test exec_DT without the need for either SQLServer or Oracle, by creating mock DBConnection and DBCommand that you then inject into your test cases.

Answer 2

I like David's answer. In the alternative, if you do still want to be creating connections and commands from within your shared code, what you could do is pass a DbProviderFactory instance to the constructor of your "DB Context" class.

You can then call e.g. CreateCommand, CreateConnection or whatever else you need to create, within each method.

Answer 3

The DbProviderFactory class does exactly what you're looking for.

static DataTable exec_DT(string query, string conn_str)
{
    DataTable retVal = new DataTable();

    using (DbConnection conn = MyDbProvider.CreateConnection())
    {
        conn.ConnectionString = conn_str;

        using (DbCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = query;
            conn.Open();

            using (DbDataReader rdr = cmd.ExecuteReader())
            {
                retVal.Load(rdr);
                rdr.Close();
            }
        }
    }

    return retVal;
}

You can even use the DbProviderFactories class to create a factory instance from the connection string in the config file.

DbProviderFactories.GetFactory(WebConfigurationManager
    .ConnectionStrings[name].ProviderName);