What is the best way to handle the Connections to MySql from c#

Question

I am working on a c# application which connects to MySql server. There are about 20 functions which will connect to database. This application will be deployed in 200 over machines. I am using the below code to connect to my database which is identical for all the functions. The problem is, i can some connections were not closed and still alive when deployed in 200 over machines.

Connection String :

<add key="Con_Admin" value="server=test-dbserver; database=test_admindb; uid=admin; password=1Password; Use Procedure Bodies=false;" />

Declaration of the connection string Globally in application [Global.cs] :

public static MySqlConnection myConn_Instructor = new MySqlConnection(ConfigurationSettings.AppSettings["Con_Admin"]);

Function to query database :

  public static DataSet CheckLogin_Instructor(string UserName, string Password)
        {
            DataSet dsValue = new DataSet();
            //MySqlConnection myConn = new MySqlConnection(ConfigurationSettings.AppSettings["Con_Admin"]);
            try
            {
                string Query = "SELECT accounts.str_nric AS Nric, accounts.str_password AS `Password`," +
                        " FROM accounts " +
                        " WHERE accounts.str_nric = '" + UserName + "' AND accounts.str_password = '" + Password + "\'";

                MySqlCommand cmd = new MySqlCommand(Query, Global.myConn_Instructor);
                MySqlDataAdapter da = new MySqlDataAdapter();
                if (Global.myConn_Instructor.State == ConnectionState.Closed)
                {
                    Global.myConn_Instructor.Open();
                }

                cmd.ExecuteScalar();
                da.SelectCommand = cmd;
                da.Fill(dsValue);
                Global.myConn_Instructor.Close();
            }
            catch (Exception ex)
            {
                Global.myConn_Instructor.Close();
                ExceptionHandler.writeToLogFile(System.Environment.NewLine + "Target  :  " + ex.TargetSite.ToString() + System.Environment.NewLine + "Message :  " + ex.Message.ToString() + System.Environment.NewLine + "Stack   :  " + ex.StackTrace.ToString());
            }

            return dsValue;
        }

Answer 1

You should be using a unit-of-work approach using disposables. The MySqlConnection type is disposable, so should be cleanly wrapped in using blocks:

using (MySqlConnection connection = CreateConnection("Con_Admin")) {
  // Do work here.
}

What this does, is it ensures that Dispose() is called on the connection object, which closes the connection. This is incredibly important.

I've also noticed you're using appSettings for your connection strings, there is actually a dedicated connectionStrings element in a config file:

<connectionStrings>
  <add name="Con_Admin" connectionString="..." providerName="MySql.Data" />
</connectionStrings>

Which you can use to create instances of connections:

public MySqlConnection CreateConnection(string name)
{
  if (string.IsNullOrEmpty(name))
    throw new ArgumentException("Connection name must be provided", "name");

  string connection = ConfigurationManager.ConnectionStrings[name].ConnectionString;
  return new MySqlConnection(connection);
}

The last point of interest, you should try using parameterised queries, which protect against sql injection attacks:

string query = "SELECT * FROM SomeTable Where SomeField = @field";

using (MySqlCommand command = new MySqlCommand(query))
{
  command.Parameters.AddWithValue("@field", "someFieldValue");
}

Hope that helps!

Answer 2

The root cause of the problem is the global single instance of your connection object. I don't recommend to keep single instance of connection through out the application. Create, Open, Close, Dispose the Connection wherever required within the unit-of-work context/scope. Also try to move your connection string to section in your app.config.