Abstractionlayer for Database-Access

Question

I would like to write custom SQL code for mysql and mssql (perhaps more later).

var dbSystem = "mssql";
if ("mssql" == dbSystem) {
    var mssqlConn = new SqlConnection(mssqlConnString);
    new SqlCommand("CREATE TABLE t1 (c1 in NOT NULL PRIMARY KEY IDENTITY(1,1), c2 int)", mssqlConn).ExecuteNonQuery();
} else {
    var mysqlConn = new MySqlConnection(mysqlConnString);
    new SqlCommand("CREATE TABLE t1 (c1 in NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 int)", mssqlConn).ExecuteNonQuery();
}

The Problem starts here. I would like to declare the connection via an interface or abstract-class before the if-statement. But it seems not possible, because the command-classes demand explicitly the specific SqlConnection or MySqlConnection.

The problem get's worse if you would like to execute a simple command like SELECT c1 form t1 where c2 = @value. Because you have to do the same as above.

Is there an API which serves a workflow like this:

IDbConnection conn; // initialized by SqlConnection or MySqlConnection, etc.
IDbCommand cmd = new DbCommand("SELECT c1 form t1 where c2 = @value", conn);
cmd.Parameters.Add("@value", Type.Int);

Problem:

• System.Data.Common.DbCommand is an abstract class
• SqlDbCommand (MySqlDbCommand, etc. ) demand there specific connection
• DbCommand.Properties does not support "Add" with two Parameters ("@placeholer", value) like the Properties of SqlDbCommand
• i don't need/want a full blown ORM; just an interface/abstraction-layer over the existing heterogeneous API

Answer 1

The way to go would be using DbProviderFactories.GetFactory(String) to retrieve a DbProviderFactory object.

Using DbProviderFactory.CreateConnection() would give you a DbConnection object.

Setting the ConnectionString property of this DbConnection object and calling Open() method will result in a opened connection to the desired database.

Using DbConnection.CreateCommand() will give you a DbCommand object to use as you wish.

For using a parameterized query you can use DbCommand.CreateParameter() which will return a DbParameter object.

Sample:

// MySQL
DbProviderFactory factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient"); 

// MS-SQL
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");  

private void Query(String query,Dictionary<String,object> namesAndValues, System.Data.CommandType commandType)
{
    String connectionString = ConfigurationManager.ConnectionStrings["yourConnectionString"];

    using (DbConnection connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionString;
        connection.Open();
        using (DbCommand command = connection.CreateCommand())
        {
            command.CommandText = query;
            command.CommandType = commandType;
            AddParameters(command, namesAndValues);
            using (DbDataReader reader=command.ExecuteReader())
            {
                while (reader.Read())
                {
                 //
                }
            }
         }
    }

}
private void AddParameters(DbCommand command, Dictionary<String, object> namesAndValues)
{
    String factoryName = factory.GetType().Name;
    DbParameter para;
    foreach (String key in namesAndValues.Keys)
    {
        para = command.CreateParameter();
        switch (factoryName)
        {
            case "SqlClientFactory":
                para.ParameterName = "@" + key;
                break;
             case "OracleClientFactory":
                para.ParameterName = ":" + key;
                break;
            case "OleDbFactory":
                para.ParameterName = "?";
                break;
            case "MySqlClientFactory":
                para.ParameterName = "?" + key;
                break;
        }
        para.Value = namesAndValues[key];
        command.Parameters.Add(para);

    }
}