Cannot convert dbconnection to sqlconnection

I have created a generic method which will work with all database like sql server,mysql and oracle and execute my sql query and return datatable.

private Datatable GetData(string connectionString,string query
{
    if (model.RdbmsType =="Mysql")
    {
         return ExecuteSqlQuery<MySqlConnection>(connectionString,query);
    }
    else if(model.RdbmsType == "SqlServer")
    {
       return ExecuteSqlQuery<SqlConnection>(connectionString,query);
    }
    else // for oracle
    {
       // code for oracle
    }

}

private Datatable ExecuteSqlQuery<T>(string connectionString, string sqlQuery) where T : DbConnection, new()
{
    using (DbConnection connection = new T())
    {
        var dt = new DataTable();
        connection.ConnectionString = connectionString;
        connection.Open();
        SqlCommand cmd = new SqlCommand(sqlQuery, connection); //Error : Cannot convert dbconnection to sqlconnection
        var queryData.Load(cmd.ExecuteReader());
    }
}

Error on this below line :

SqlCommand cmd = new SqlCommand(sqlQuery, connection); //Error : Cannot convert dbconnection to sqlconnection

I even tried like below :

DbCommand cmd1 = new DbCommand(sqlQuery, connection);

But as Dbcommand is abstract class i cant do above.

So how do i make my ExecuteSqlquery method to work with all sql server,mysql and oracle??

Jon Skeet
people
quotationmark

Basically, when you call new SqlCommand(), that will only work with a SqlConnection. It's not going to work for MySqlConnection, or for anything from Oracle etc. SqlCommand is tightly coupled to SqlConnection.

So either you need a Func<string, T, DbCommand> as well (that would call the SqlCommand constructor, or similar) or just separate out your methods instead of it being generic - have separate ExecuteSqlQuery, ExecuteOracleQuery etc methods.

Or you could just make the command type another generic type parameter, and have:

private Datatable ExecuteSqlQuery<TConnection, TCommand>(string connectionString, string sqlQuery)
    where TConnection : DbConnection, new()
    where TCommand : DbCommand, new() 
{
    using (DbConnection connection = new TConnection())
    {
        connection.ConnectionString = connectionString;
        connection.Open();
        TCommand cmd = new TCommand();
        cmd.CommandText = sqlQuery;
        cmd.Connection = connection;
        var queryData.Load(cmd.ExecuteReader());
    }
}

Then you'd call it with:

return ExecuteSqlQuery<SqlConnection, SqlCommand>(connectionString, query);

(etc).

Alternatively, look at DbProviderFactory, which is designed for this sort of scenario as far as I can tell.

people

See more on this question at Stackoverflow