Asynchronous Database call to MySql Database gives error

Question

I am using Microsoft.Enterprise Library with MySql database. I am trying to call a database method asynchronously.

My method is like below..

public static async Task<DataTable> ExecuteDataSetWithParameterAsSourceAsync(this Database database, CommandDetail cmdDetail, params Object[] parameterSource)
    {
        List<int> outParamIndex = new List<int>();

        AsyncCallback cb = new AsyncCallback(EndExecuteReaderCallBack);

        DataTable dt = new DataTable();
        DbAsyncState state = BeginExecuteReader(database, cmdDetail.CommandText, cb, parameterSource);
        IDataReader reader = (IDataReader)state.State;
        dt.Load(reader);
        reader.Close();
      ...
        return await Task.FromResult(dt);
    }

I am getting below Error

{"The database type "MySqlDatabase" does not support asynchronous operations."}

Below is the complete stack image of the error..

My connection string is

 <add name="VirtualCloudDB" providerName="EntLibContrib.Data.MySql" connectionString="database=test;uid=xxx;password=xxx;Data Source=test-instance; maximumpoolsize=3"/>

Answer 1

About the error

Oracle's Connector/.NET library didn't even allow asynchronous operations before v8.0. Even now, there are several quirks. It's better to use the independent, open-source MySqlConnector library.

If you absolutely must use Connector/.NET, upgrade to the latest version.

About the code (no history lesson)

Forget EntLib, especially DAAB. Even the docs say:

The Database class leverages the provider factory model from ADO.NET. A database instance holds a reference to a concrete DbProviderFactory object to which it forwards the creation of ADO.NET objects.

What you use isn't the real thing anyway, it's a community-supported clone of the official code that used to be stored in Codeplex. The only thing that is still in development is the Unity DI container.

Real async operations are available in ADO.NET and implemented by most providers. The database-agnostic, factory-based model of EntLib 1 was incorporated into ADO.NET 2 back in 2006. Entlib 2.0 DAAB is essentially a thin layer of convenience methods over ADO.NET 2.

ADO.NET 2 "Raw"

In ADO.NET 2.0 alone, the entire method can be replaced with :

async Task<DataTable> LoadProducts(string category)
{
    var sql="select * from Products where category=@category";
    using(var connection=new MySqlConnection(_connStrFromConfig))
    using(var cmd=new MySqlCommand(sql,connection))
    {
        cmd.Parameters.AddWithValue("@category",category);

        await connection.OpenAsync();

        using(var reader=await cmd.ExecuteReaderAsync())
        {
            DataTable dt = new DataTable();
            dt.Load(reader);
            return dt;
        }
    }
}

Especially for MySQL, it's better to use the open-source MySqlConnector library than Oracle's official Connector/.NET.

ADO.NET 2 Factory model

ADO.NET 2 added abstract base classes and a factory model (based on DAAB 1, but easier) that allows using database-agnostic code as much as possible.

The previous code, without using the provider factory, can be rewritten as :

string _providerName="MySqlConnector"


DbCommand CreateConnection()
{
    DbProviderFactory _factory =DbProviderFactories.GetFactory(_providerName);
    connection = _factory.CreateConnection();
    connection.ConnectionString = connectionString;
    return connection;
}

async Task<DataTable> LoadProducts(string category)
{
    var sql="select * from Products where category=@category";
    using(DbConnection connection=CreateConnection())
    using(DbCommand cmd= connection.CreateCommand())
    {
        cmd.CommandText=sql;
        var param=cmd.CreateParameter();
        param.Name="@category";
        //The default is String, so we don't have to set it
        //param.DbType=DbType.String;
        param.Value=category;
        
        cmd.Parameters.Add("@category",category);

        await connection.OpenAsync();

        using(var reader=await cmd.ExecuteReaderAsync())
        {
            DataTable dt = new DataTable();
            dt.Load(reader);
            return dt;
        }
    }
}

All that's needed to target eg SQL Server or Oracle is registering and using a different provider name.

The code can be simplified. For example, DbParameterCollection.AddRange can be used to add multiple parameters at once. That's still too much code by modern standards though.

Entlib 2 DAAB - it's the same classes

Entlib 2 DAAB uses the same abstract classes. In fact, the Database class does little more than add convenience methods on top of the abstract classes, eg methods to create a DbCommand, or execute a query and return a reader or a Dataset.

If you didn't need parameters, you could write just :

DataTable LoadProducts(Database database)
{
    var sql="select * from Products";
    var set=database.ExecuteDataSet(CommandType.Text,sql);
    return set.Tables[0];
}

Unfortunately, there's no way to combine a raw query and parameters. Back when EntLib 1 was created it was thought that complex code should always be stored in a stored procedure. So while there's a ExecuteDataSet(string storedProcedureName,params object[] parameterValues), there's no equivalent for raw SQL.

And no Task-based async methods either. By 2010 EntLib was in support mode already.

Unfortunately again there's no way to directly create a DbCommand from Database. Again, the assumption was that people would either execute raw SQL or called a stored procedure. There's a GetSqlStringCommand that accepts no parameters. There's also Database.ProviderFactory that can be used to do everything manually, and end up with the same code as raw ADO.NET.

Another possible option is to cheat, and use Database.GetStoredProcCommand with positional parameters and change the CommandType

async Task<DataTable> LoadProducts(Database database,string category)
{
    var sql="select * from Products where category=@category";
    using(var cmd=database.GetStoredProcCommand(sql,category))
    {
        cmd.CommandType=CommandType.Text;
        using(var reader=await cmd.ExecuteReaderAsync())
        {
            DataTable dt = new DataTable();
            dt.Load(reader);
            return dt;
        }
    }

    return set.Tables[0];
}

Dapper

With microORM libraries like Dapper the code can be reduced to :

async Task<IEnumerable<Product>> LoadProducts(string category)
{
    var sql="select * from Products where category=@category";
    using(var connection=CreateConnection())
    {

        var products=await connection.Query<Product>(sql,new {category=category});

        return products;
    }
}

Dapper will open the connection if it's closed, execute the query asynchronously and map the results to the target object, in a single line of code. Parameters will be mapped by name from the parameter object.

When called without a type parameter, Query returns a list of dynamic objects

async Task<IEnumerable<dynamic>> LoadProducts(string category)
{
    var sql="select * from Products where category=@category";
    using(var connection=CreateConnection())
    {

        var products=await connection.Query(sql,new {category=category});

        return products;
    }
}