Does MySQL connector class slowdown windows forms app?
MadhawaBamini
1
Reputation point
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
Sign in to answer