C# async/await not working with mysql

Question

0.00/5 (No votes)

See more:

I'm trying to get my brain around this async / await thing, and thought I'd try it with a little database app. Below is the code, which works fine, but the GetData() method is not running asynchronously because the UI still freezes when it's connecting/pulling the data. I have the random numbers filling listBox2 as a visual cue that the UI is freezing. The database is just a list of website addresses I made to test this code.

C#

using System;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace MySQLasync
{
    public partial class Form1 : Form
    {
        // public variables and constants
        string strconnect = "Server = localhost; Port=3306; Database=mydbase; Uid=myusername; Pwd=mypasswd;";
        Random r = new Random();
        int c = 0;

        public Form1()
        {
            InitializeComponent();
        }

        private void Button1_Click(object sender, EventArgs e)
        {
            timer1.Enabled = true;
            GetData();
        }

        private async void GetData()
        {
            listBox1.Items.Clear();
            string sql = "SELECT * FROM websites";
            MySqlConnection con1 = new MySqlConnection(strconnect);
            {
                con1.Open();
                MySqlCommand cmd = new MySqlCommand(sql, con1);
                MySqlDataReader reader = (MySqlDataReader)await cmd.ExecuteReaderAsync();
                while (reader.Read())
                {
                    listBox1.Items.Add(reader["title"].ToString());
                }
                con1.Close();
            }
        }

        private void Timer1_Tick(object sender, EventArgs e)
        {
            c++;
            if (c > 20)
            {
                c = 0;
                listBox2.Items.Clear();
            }
            int num = r.Next(1, 999999);
            listBox2.Items.Add(num);
        }
    }
}

What I have tried:

Whether I use async / await or not, the code runs exactly the same. That is, the UI still freezes for the same amount of time as the data is accessed and added to listBox1.

Can someone please explain what I'm doing wrong here? I want the UI to remain responsive while the data is accessed and loaded. Not sure I have the whole concept of async/await yet. Thank you.

Posted 26-Apr-19 5:55am

dragnscalearmor

Updated 26-Apr-19 6:07am

v2

Add a Solution

Comments

Richard Deeming 26-Apr-19 12:00pm

How about:

while (await reader.ReadAsync())

Also, check that you're using MySQL Connector/NET 6.9 or later.
MySQL :: MySQL Connector/NET Developer Guide :: 5.10 Asynchronous Methods[^]

dragnscalearmor 26-Apr-19 12:09pm

Richard - thank you. I have made this change, but the same result: the UI freezes for a bit. Also, I'm using 8.0.15 of the MySQL Connector.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gerry Schmitz · Accepted Answer · 2019-04-26T06:07:00

Solution 1

You should use one of the patterns in this link (e.g. Using the Base Provider Model and the New Asynchronous Feature).

Asynchronous Programming | Microsoft Docs[^]

Posted 26-Apr-19 6:07am

Gerry Schmitz

Comments

dragnscalearmor 26-Apr-19 12:42pm

Thank you for the link. I've followed the docs and formatted my code according to that pattern, and it still freezes the UI while it loads. Not sure what's causing it, as the output when running that method is: "The thread 0x3158 has exited with code 0 (0x0)." .... and then it lists the websites from the query. Can't seem to get around this UI freezing bit.

[no name] 26-Apr-19 12:53pm

Well, "show the code". Everything else is hearsay.

dragnscalearmor 26-Apr-19 12:54pm

This is my newly structured code, but it also freezes the UI briefly:

public async void GetMoreData()
{
using (MySqlConnection conn = new MySqlConnection(strconnect)) // my connection string variable
{
MySqlCommand command = new MySqlCommand("select * from websites", conn);

conn.Open();
MySqlDataReader reader = (MySqlDataReader)await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
listBox1.Items.Add(reader["title"].ToString());
}
}
}

Richard Deeming 26-Apr-19 13:04pm

Is it actually the query that's causing it to freeze? Or is it adding the items to the list?

You'll probably need to profile your code to see where the bottleneck is. The only other obvious one I can see is:

await conn.OpenAsync();

dragnscalearmor 26-Apr-19 13:09pm

Modified the code to test this, and the UI still freezes even without adding to the listbox. Below is the data from the output window with the new code:

'MySQLasync.exe' (CLR v4.0.30319: MySQLasync.exe): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'MySQLasync.exe' (CLR v4.0.30319: MySQLasync.exe): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\MySql.Data\v4.0_8.0.15.0__c5687fc88969c44d\MySql.Data.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'MySQLasync.exe' (CLR v4.0.30319: MySQLasync.exe): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089\System.Transactions.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'MySQLasync.exe' (CLR v4.0.30319: MySQLasync.exe): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.EnterpriseServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'MySQLasync.exe' (CLR v4.0.30319: MySQLasync.exe): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.EnterpriseServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.Wrapper.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'MySQLasync.exe' (CLR v4.0.30319: MySQLasync.exe): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\System.Management\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Management.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
The thread 0x2d0c has exited with code 0 (0x0).

The new code is:

await conn.OpenAsync();
MySqlDataReader reader = (MySqlDataReader)await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
//listBox1.Items.Add(reader["title"].ToString());
}

Very strange problem. I'm not a professional programmer, mostly just an enjoyable hobby, but this has stumped me.

Richard Deeming 26-Apr-19 13:55pm

If you use something like CodeTrack[^] to profile your application, you should be able to see which calls are taking the most time.

dragnscalearmor 26-Apr-19 13:17pm

Marking this as answered, using Gerry's answer above. MS docs show this as the correct way to do it, so I'm going to continue working with my code to get it more in line with their recommendations. Thanks, everyone.