async function blocks my wpf application

Question

I try to become acquainted with async/await. So I try to write an C#/WPF program to query a database asynchronously without blocking my GUI.

I created an object implementing the INotifyPropertyChanged interface. This object offers a DataTable property and this should become changed by my async function. My GUI component has a binding to the DataTable property.

My object looks this way:

public class AsyncDataDemo : INotifyPropertyChanged
{
    protected DataTable data = new DataTable();

    public DataTable Data
    {
        get { return data; }
        protected set
        {
            data = value;
            doPropertyChanged("Data");
        }
    }

    protected virtual void doPropertyChanged(string propertyName)
    {
        if (PropertyChanged != null)
        {
            PropertyChangedEventArgs Arguments = new PropertyChangedEventArgs(propertyName);
            PropertyChanged(this, Arguments);
        }
    }

    public event PropertyChangedEventHandler PropertyChanged;

    protected async Task<DataTable> OpenQueryAsync(string ConnectionString, string Query)
    {
        OdbcConnection connection = new OdbcConnection(ConnectionString);
        await connection.OpenAsync().ConfigureAwait(false);

        OdbcCommand command = new OdbcCommand(Query, connection);
        DbDataReader dataReader = await command.ExecuteReaderAsync().ConfigureAwait(false);

        DataTable resultData = new DataTable();
        resultData.Load(dataReader);
        connection.Close();
        return resultData;
    }

    public async void RunQueryAsync(string Query)
    {           
            Data = await OpenQueryAsync("<ConectionString>", (Query as string)).ConfigureAwait(false);          
    }
}

And at a button click event I call:

private void Button_Click(object sender, RoutedEventArgs e)
{
    data.RunQueryAsync("SELECT * FROM BigTable");
}

This works fine with one exception: the button click blocks my GUI until the data is loaded and I don't understand why.

Can somebody please explain my failure to me? I don't understand why the async function won't run asynchronously?

Regards

Answer 1

You really need to step through with a debugger to figure out which step is taking a long time. The most likely candidate is resultData.Load(dataReader);, the way your code could still be on the UI thread is if every function you call returns a task that is already has .IsCompleted == true.

If the task is already completed and you await you stay on the UI thread even though you did ConfigureAwait(false). All that needs to happen is OpenAsync() and ExecuteReaderAsync() need to complete very quickly or complete synchronously (both very possible).

One way you could fix this put the query on a background thread to start instead of waiting on ConfigureAwait(false) to do it for you.

public async void RunQueryAsync(string Query)
{           
        Data = await Task.Run(() => OpenQueryAsync("<ConectionString>", (Query as string)));          
}

I also removed the ConfigureAwait(false) because you want your INotifyPropertyChanged to happen on the UI thread.

Also you REALLY need to be disposing of your disposable objects.

protected async Task<DataTable> OpenQueryAsync(string ConnectionString, string Query)
{
    using(OdbcConnection connection = new OdbcConnection(ConnectionString))
    {    
        await connection.OpenAsync().ConfigureAwait(false);

        using(OdbcCommand command = new OdbcCommand(Query, connection))
        using(DbDataReader dataReader = await command.ExecuteReaderAsync().ConfigureAwait(false))
        {
            DataTable resultData = new DataTable();
            resultData.Load(dataReader);
            return resultData;
        }
    }
}

Answer 2

The reason for the behavior you are experiencing is a flaw in the DbConnection and DbCommand classes which all ADO providers use as base for their specific classes. And the flaw is that by default all the Async methods are synchronous! It's even documented!

For instance, the documentation for DbConnnection.OpenAsync:

The default implementation invokes the synchronous Open call and returns a completed task.

and for DbCommand.ExecuteDbDataReaderAsync:

The default implementation invokes the synchronous ExecuteReader method and returns a completed task, blocking the calling thread.

From what I have seen, only the SqlServer provider overrides async methods with a real asynchronous implementation. But since you are using OleDb provider, you are out of luck.