3

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

Improve this question

2 Answers 2

Reset to default
4

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;
        }
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you, that did the trick. And also thank you for the hint about my disposable objects. I will do better.
4

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.

Improve this answer

2 Comments

Thank you for the very useful explanation I will keep this in mind.
@Mike In case it is not clear (combining the info in the two answers), when an async function completes synchronously doing .ConfigureAwait(false) has no affect on the flow of the code and you will still be on the UI thread after the await call even though you set .ConfigureAwait(false).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.