Windows forms GUI stuck when calling Dapper ExecuteAsync

Question

I'm trying to use Dapper async features to keep my windows forms client application responsive. I call the asycn method but the GUI gets stuck as it was synchronous code.

I am using an access mdb database and I have my data access code in a class library but here I have a simpler case to demonstrate the issue.

In the form, I have a button click event handler like:

private async void button1_Click(object sender, EventArgs e)
{
    await DBTest();

    MessageBox.Show("Done");
}

and the async method is something like

private static async Task DBTest()
{
    OleDbConnection connection = new OleDbConnection();

    //Connection string is stored somewhere
    connection.ConnectionString = connectionString;

    await connection.OpenAsync().ConfigureAwait(false);

    //GUI stuck here until ExecuteAsync is done!
    await connection.ExecuteAsync("Delete from table1"); 

    connection.Close();
}

The call to the ExecuteAsync method freezes the UI.

What am I missing?

Answer 1

It turns out that Dapper just wraps ADO.NET asynchronous methods, OLEDB default implementation for methods like OpenAsync and ExecuteNonQueryAsync is just to run them synchronously! unlike SQL Server which has proper implementation for asynchronous methods.

Answer 2

I have tried to reproduce what you're seeing by creating a new Windows Form Application and communicating with a SQL Server database (the classic NORTHWND database). I have a form with a single label and three buttons - the first button just increments a value shown in the label to make it easy to test when the form is or isn't responsive. The second button performs a blocking query and the third button performs an async query. All of the code is below..

When I click "button2", the GUI is frozen as you describe - clicking on button1 does not increment the number displayed in the label. In fact, the form doesn't respond to any user interaction - trying to drag that form around will not work until the query has completed.

If click "button3" then the GUI is not locked (clicking "button1" works, dragging and resizing the form still work).

using System;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Windows.Forms;
using Dapper;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        private int _counter = 0;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            _counter++;
            label1.Text = _counter.ToString();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (var conn = new SqlConnection("Server=.;Database=NORTHWND;Trusted_Connection=True;"))
            {
                var timer = new Stopwatch();
                timer.Start();
                label1.Text = "Starting..";
                conn.Execute(@"
                    SELECT *
                    FROM Categories
                    INNER JOIN Customers
                    ON 1=1
                    INNER JOIN Employees
                    ON 1=1
                ");
                timer.Stop();
                label1.Text = "Done: " + timer.ElapsedMilliseconds + "ms";
            }
        }

        private async void button3_Click(object sender, EventArgs e)
        {
            using (var conn = new SqlConnection("Server=.;Database=NORTHWND;Trusted_Connection=True;"))
            {
                var timer = new Stopwatch();
                timer.Start();
                label1.Text = "Starting (async)..";
                await conn.ExecuteAsync(@"
                    SELECT *
                    FROM Categories
                    INNER JOIN Customers
                    ON 1=1
                    INNER JOIN Employees
                    ON 1=1
                ");
                timer.Stop();
                label1.Text = "Done (async): " + timer.ElapsedMilliseconds + "ms";
            }
        }
    }
}

The only differences that I can see between my code and yours are that I leave the opening of the connection to the ExecuteAsync call (so I don't need to use OpenAsync) and that I'm using SQL Server and not Access (I don't have Access available to test against on my computer here).

I tried changing my code to be more similar to yours by removing the "using" around the conn reference, calling OpenAsync, calling ConfigureAwait and explicitly closing it after use but I could not reproduce the issue. I tried it with and without ConfigureAwait (since passing false to this caused problems after the work was completed because the label1.Text update was attempted from a non-GUI thread which is not allowed - passing true to ConfigureAwait avoids this problem).. but I could not.

I would suggest that you try running my code against a SQL Server instance if you have one available, just to reassure yourself that you are using Dapper / async correctly. Then try changing the connection string (and the SQL query) so that it queries your Access database and see whether that is the cause of the problem (I would be surprised if Access is able to break async in this way but I don't know what else it could be).