C# Mysql executenonqueryasync is not asynchronous

Question

I want to get the list of the teachers who are teaching this specific type:

public static async Task<DataTable> getTeacherSHS() 
{
    DataTable dt = new DataTable();
    string query = @"some long query dont mid this";
    using (MySqlConnection conn = new MySqlConnection(cs))
    {
        try
        {
            await conn.OpenAsync();
            using( MySqlCommand cmd = new MySqlCommand(query,conn))
            {
                cmd.Parameters.AddWithValue("@shs", "%SHS%");
                cmd.Parameters.AddWithValue("@term", term);

                await cmd.ExecuteNonQueryAsync();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) 
                {
                    await da.FillAsync(dt);
                    MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
                }
            }
        }
        catch (MySqlException e)
        {
            Console.Write(e.Message);
        }
        if (conn != null)
            await conn.CloseAsync();
    }

    return dt;
}

Now my method for getting this is via a button click and it will return a datatable for my datagridview

private  async void button1_Click_1(object sender, EventArgs e)
{
    dataGridView1.DataSource = await ConnectionAsync.getTeacherSHS(); 
    dataGridView1.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
    dataGridView1.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
}

My problem is that after clicking the button the call is blocking the other I/O i cant seem to type in the other textbox because it is waiting for the task to be done. I thought it was asynchronous, can somebody explain?

Answer 1

This happens because the Async methods in the MySql.Data connector aren't actually asynchronous. They block on network I/O and only return when the DB operation is complete. (For a much more detailed description, see this question and its top answer.) MySQL bug #70111 reports this problem in the MySQL connector.

To get truly asynchronous DB operations, you'll have to wait until that bug is fixed, or switch to a different connector.

I've been developing a new, fully async connector (MySqlConnector on NuGet; source on GitHub). It supports MySqlDataAdapter since version 0.33.0.