title: What is the best way to handle the Connections to MySql from c#
body: <p>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.</p>
  
  <p>Connection String :</p>
  
  <pre><code>&lt;add key="Con_Admin" value="server=test-dbserver; database=test_admindb; uid=admin; password=1Password; Use Procedure Bodies=false;" /&gt;
  </code></pre>
  
  <p>Declaration of the connection string Globally in application [Global.cs] :</p>
  
  <pre><code>public static MySqlConnection myConn_Instructor = new MySqlConnection(ConfigurationSettings.AppSettings["Con_Admin"]);
  </code></pre>
  
  <p>Function to query database :</p>
  
  <pre><code>  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;
          }
  </code></pre>