Does MySQL connector class slowdown windows forms app?

MadhawaBamini 1 Reputation point
2021-08-31T04:58:51.22+00:00

I'm using MySQL.Data NuGet package to connect a windows forms app to a remote Azure Database for MySQL server. I've used the following class to connect to the database.


private void Initialize()
{
//string connectionString = "xxxxxxxxxxxxx";
connection = new MySqlConnection(connectionString);
}

    private bool OpenConnection()
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (MySqlException ex)
        {
            //When handling errors, you can your application's response based 
            //on the error number.
            //The two most common error numbers when connecting are as follows:
            //0: Cannot connect to server.
            //1045: Invalid user name and/or password.
            switch (ex.Number)
            {
                case 1042:
                    MessageBox.Show("Cannot connect to the database server. Please check your internet connection. If the issue persist contact administrator!");
                    Application.Exit();
                    break;

                case 1045:
                    MessageBox.Show("Invalid username/password, please try again");
                    break;

                default:
                    MessageBox.Show("Error: " + ex.Number.ToString());
                    break;
            }
            return false;
        }
    }

    //Close connection
    private bool CloseConnection()
    {
        try
        {
            connection.Close();
            return true;
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }

    public Object Execute(string query_string)
    {
        string query = query_string;

        Object returnValue = new Object();

        //open connection
        if (this.OpenConnection() == true)
        {
            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, connection);

            //Execute command
            cmd.ExecuteNonQuery();

            MySqlCommand cmd2 = new MySqlCommand("SELECT LAST_INSERT_ID()", connection);

            //Execute command
            returnValue = cmd2.ExecuteScalar();

            //close connection
            this.CloseConnection();
        }

        return returnValue;
    }

    public DataTable ExecuteGetRows(string query_string)
    {
        string query = query_string;

        //Create a list to store the result
        System.Data.DataTable dataTable = new DataTable();

        //Open connection
        if (this.OpenConnection() == true)
        {
            //Create Command
            MySqlCommand cmd = new MySqlCommand(query, connection);
            //Create a data reader and Execute the command
            MySqlDataReader dataReader = cmd.ExecuteReader();

            dataTable.Load(dataReader);

            //close Data Reader
            dataReader.Close();

            //close Connection
            this.CloseConnection();

            //return list to be displayed
            return dataTable;
        }
        else
        {
            return dataTable;
        }
    }

    public Object ExecuteGetScalar(string query_string)
    {
        string query = query_string;

        Object returnValue = new Object();

        //open connection
        if (this.OpenConnection() == true)
        {
            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, connection);

            //Execute command
            returnValue = cmd.ExecuteScalar();

            //close connection
            this.CloseConnection();

            return returnValue;
        }
        else
        {
            return returnValue;
        }
    }

I create an object of the above class under any method in the app when I need to query the db and pass the sql query.

However the app is extremely slow when connected to the remote db where as works fine when connected to the localhost.

Is there anything wrong with my approach?

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
992 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.