How to properly handle database queries without freezing the UI?-CodePudding

FIRST! I know very similar questions have been asked before, however, the answers have been so case specific that I seriously don't understand how to apply them. Please don't roast me.

All I want is to build a rather generic application that has CRUD modules and reporting (c# and MySQL), nothing special. However, everytime my app runs a line where there is a call to a method that queries the server the UI freezes a few ms, the freeze-like-stuttering of the UI feels incredibly annoying.

As I understood, every DB call needs to be done in a different thread with a background worker or a task so the main thread doesn't get busy and makes the UI unresponsive BUT whenever I try to do this I have issues because I can't access the UI components.

Can you guys help me with this simple login form as an example, please?

I have a simple form, 2 textboxes, txtUser and txtPassword and a button with the following code:


    MySqlDataReader? user = MdlUsers.GetUserByUsername(txtUser.Text);
    if (user != null && user.Read())
    {
        string? pwd = user["clave"].ToString();
        if (pwd != null && pwd.Equals(txtPassword.Text))
        {
            MessageBox.Show("Acceso correcto.");
            logged = true;
        }
        else
        {
            MessageBox.Show("Acceso denegado.");
            logged = false;
        }
    }
    else
    {
        MessageBox.Show("No regresó nada.");
        logged = true;
    }

This is the code to the MdlUser class:

public class MdlUsers
    {
        public static MySqlDataReader? GetUserByUsername(string username)
        {
            MySqlDataReader? result = null;
            try
            {
                string qry = "SELECT * FROM usuarios WHERE login = @username";
                MySqlCommand cmd = new MySqlCommand(qry, MdlConnection.Connect());
                cmd.Parameters.AddWithValue("@username", username);

                result = cmd.ExecuteReader();
                return result;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
    }

Whenever I click the button the UI stutters. So how can I handle this? Thank you very much.

CodePudding user response：

The way to handle any situation of that sort is to perform your long blocking operation on a separate task, and when it finishes, post a call to the GUI thread.

You have not told us what kind of GUI you are using, (that's what tags are for on stackoverflow,) I suspect it is WinForms, so the way to do this in WinForms is with Control.BeginInvoke().

When you specify an Action to be invoked with Control.BeginInvoke(), that Action will run in the GUI thread, so it will be able to safely "access the UI components."

CodePudding user response：

You are correct that database calls should be performed on a separate thread in order to prevent the UI from freezing. In your case, you could use the Task class to perform the database query in a separate thread. This would allow you to access the UI components from the main thread while the database query is being performed in the background.

Here is an example of how you could modify your code to use a Task to perform the database query in a separate thread:

private async void btnLogin_Click(object sender, EventArgs e)
{
// Use a Task to perform the database query in a separate thread
MySqlDataReader user = await Task.Run(() => MdlUsers.GetUserByUsername(txtUser.Text));

if (user != null && user.Read())
{
    string pwd = user["clave"].ToString();
    if (pwd != null && pwd.Equals(txtPassword.Text))
    {
        MessageBox.Show("Acceso correcto.");
        logged = true;
    }
    else
    {
        MessageBox.Show("Acceso denegado.");
        logged = false;
    }
}
else
{
    MessageBox.Show("No regresó nada.");
    logged = true;
}
}

Notice that the btnLogin_Click() method is marked as async, and the GetUserByUsername() method is called using the await keyword. This will cause the database query to be performed in a separate thread, and the btnLogin_Click() method will continue to execute on the main thread while the query is being performed.

Additionally, you should make sure to close the MySqlDataReader object when you are done with it, in order to prevent any memory leaks. You can do this by calling the Close() method on the MySqlDataReader object when you are done with it.

Here is an example of how you could modify the GetUserByUsername() method to close the MySqlDataReader object when it is no longer needed:

public static MySqlDataReader GetUserByUsername(string username)
{
MySqlDataReader result = null;
try
{
    string qry = "SELECT * FROM usuarios WHERE login = @username";
    MySqlCommand cmd = new MySqlCommand(qry, MdlConnection.Connect());
    cmd.Parameters.AddWithValue("@username", username);

    result = cmd.ExecuteReader();
    return result;
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
    return null;
}
finally
{
    // Close the MySqlDataReader object when it is no longer needed
    if (result != null)
    {
        result.Close();
    }
}
}

This will ensure that the MySqlDataReader object is properly closed when it is no longer needed, and it will prevent any memory leaks.