Click here to Skip to main content
14,384,492 members
Rate this:
Please Sign up or sign in to vote.
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.

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
Updated 26-Apr-19 7:07am
v2
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

Rate this:
Please Sign up or sign in to vote.

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[^]
   
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.
Gerry Schmitz 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.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100