title: Handle connection to MySql lost
body: <p>I would like to detect connection state to MySql database. My database is deployed in different server than my app and there is good chances to lose connection to it via network. So I have to take this scenario into consideration. </p>
  
  <p>Here is what I tried so far (a simplified test example):</p>
  
  <pre><code>static string connectionString = "***";
  public static MySqlConnection Connection;
  static System.Timers.Timer _timer; 
  
  static void _timer_Elapsed(object sender, ElapsedEventArgs e)
  {
      try
      {
          if (Connection.State != System.Data.ConnectionState.Open)
              Connection.Open();
          // Call method to invoke MySqlCommand.ExecuteNonQuery
          mysqlCommand.ExecuteNonQuery();
      }
      catch (MySqlException ex)
      {
          Console.WriteLine("SQL EXCEPTION: " + ex);
          // Handle all type of database exceptions
          switch(ex.Number)
          {...}
      }
      catch (Exception ex)
      {
          Console.WriteLine("OTHER EXCEPTION: " + ex);
      }
  }
  
  static void Main(string[] args)
  {
      Connection = new MySqlConnection(connectionString);
  
      _timer = new System.Timers.Timer(3000); 
      _timer.Elapsed += new ElapsedEventHandler(_timer_Elapsed);
      _timer.Enabled = true; 
  
      Console.ReadKey();
  }
  </code></pre>
  
  <p>If the connection to MySql is lost, I got a general exception: </p>
  
  <blockquote>
    <p>IOException : Unable to write data to the transport connection: An
    established connection was aborted by the software in your host
    machine.</p>
  </blockquote>
  
  <p>I was expecting  <code>MySqlException</code> to be fired but that was not the case.
  Also, if the connection to MySql is restored, I still get the <code>IOException</code> instead of executing the query. Seems like, <code>MySqlConnection</code> object has not been updated and it doesn't care about new connection state.</p>
  
  <ol>
  <li>What's the best way to handle connection lost exception? </li>
  <li>How can I refresh <code>MySqlConnection</code> when connection is restored?</li>
  </ol>
  
  <p><strong>Note:</strong> that I can't instantiate a new <code>MySqlConnection</code> object for each new query, because the program I'm trying to change has a <strong>Singleton</strong> of type <code>MySqlConnection</code> which is initialized only once. I know that's a bad design but I don't want to change this design now. I just want to catch connection lost exception and try to refresh <code>MySqlConnection</code> to continue to work correctly.  </p>