Data Provider Abstraction

Question

I have a scenario where I need to abstract common class members from several disparate data providers into a common class. The current Data objects all derive from the various Db* classes in the System.Data.Common namespace.

Below is my current base implementation:

public abstract class DataProvider<TConn, TCmd, TParam> : IDisposable
    where TConn : DbConnection, new()
    where TCmd : DbCommand, new() {

    public DataProvider ( string connstr ) {
        ConnectionString = connstr;
    }

    public TConn Connection { get { return new TConn(); } }
    public TCmd Command { get { return new TCmd(); } }
    public string ConnectionString { get; private set; }

    protected DbDataReader ExecuteReader ( string text , CommandType type , params TParam [] parms ) {
        try {
            using ( var conn = Connection ) {
                conn.Open();

                using ( var cmd = Command ) {
                    cmd.CommandText = text;
                    cmd.CommandType = type;
                    cmd.Parameters.AddRange( parms );

                    return cmd.ExecuteReader();
                }
            }
        } catch ( Exception ex ) {
            Log.Exception( ex , GetType().Name );
            throw ex;
        }
    }
}

Two of my current implementations:

public class SqlDataProvider : DataProvider<SqlConnection, SqlCommand, SqlParameter> {
    public SqlDataProvider ( string connstr ) : base( connstr ) { }

    public DataTable ExecuteStoredProcedure(string text, params SqlParameter[] parms ) {
        var dt = new DataTable();
        dt.Load( ExecuteReader( text , CommandType.StoredProcedure , parms ) );
        return dt;
    }

    public DataTable ExecuteStatement(string text, params SqlParameter[] parms ) {
        var dt = new DataTable();
        dt.Load( ExecuteReader( text , CommandType.Text , parms ) );
        return dt;
    }
}

public class OleDataProvider : DataProvider<OleDbConnection, OleDbCommand, OleDbParameter> {
    public OleDataProvider ( string connstr ) : base( connstr ) { }

    public DataTable ExecuteStoredProcedure(string text, params OleDbParameter[] parms ) {
        var dt = new DataTable();
        dt.Load( ExecuteReader( text , CommandType.StoredProcedure , parms ) );
        return dt;
    }

    public DataTable ExecuteStatement(string text, params OleDbParameter[] parms ) {
        var dt = new DataTable();
        dt.Load( ExecuteReader( text , CommandType.Text , parms ) );
        return dt;
    }
}

Ultimately, I am trying to setup a framework for future expansion into other database objects that derive from the Db* family of objects.

The current product spans connections to MS-SQL, Sybase, Informatica and Oracle. I understand that OleDb can perform the operations for all 4 system types but would like to provide the ability for the specialized object constructs if available.

Question

1. Is there a better approach, outside of Entity Framework?
2. Am i duplicating already existing effort (outside of EF and/or ADO)

Answer 1

The only type you need is the connection type. The connection offers all methods to create it's subsequent objects.

Example:

using(var connection = new TConnection())
{
    using(var command = connection.CreateCommand())
    {
        var parameter = command.CreateParameter();
        parameter.ParameterName = "@answer";
        parameter.Value = 42;

        command.Parameters.Add(parameter);

        /// and so forth
    }
}

You could even move that from the actual generics... this is runtime behavior. All you need is your methods to take an IDbConnection and none of your underlying methods needs to know who created that connection and whether it's a SqlConnection, OracleConnection or any other.

void MyAgnosticDatabaseMethod(IDbConnection connection)
{
    using(var command = connection.CreateCommand())
    {
        var parameter = command.CreateParameter();
        parameter.ParameterName = "@answer";
        parameter.Value = 42;

        command.Parameters.Add(parameter);

        /// and so forth
    }
}

And then:

using(var connection = new OracleConnection())
{
    MyAgnosticDatabaseMethod(connection);
}

using(var connection = new SqlConnection())
{
    MyAgnosticDatabaseMethod(connection);
}