title: Using MySQLConnection in C# does not close properly
body: <p>I try to write a class to make MySql Connections easier. My problem is, after I open a connection and close it. It is still open in the Database and gets aborted.</p>
  <p>I'm using the 'using' statement' of course, but the connection is still open and gets aborted after I exit the program.</p>
  <p>Here's what my code looks like:</p>
  <pre><code>using (DatabaseManager db = new DatabaseManager())
  {
  using (MySqlDataReader result = db.DataReader(&quot;SELECT * FROM module WHERE Active=1 ORDER BY Sequence ASC&quot;))
  {
      foreach (MySqlDataReader result in db.DataReader(&quot;SELECT * FROM module WHERE Active=1 ORDER BY Sequence ASC&quot;))
      {
          //Do stuff here
      }
  }
  }
  </code></pre>
  <p>The class Database manager opens the connection and closes it when disposed:</p>
  <pre><code>public DatabaseManager()
  {
      this.connectionString = new MySqlConnectionStringBuilder(&quot;Server=localhost;Database=businessplan;Uid=root;&quot;);
      connect();
  }
  private bool connect()
  {
      bool returnValue = true;
      connection = new MySqlConnection(connectionString.GetConnectionString(false));
      connection.Open();
  }
  
  public void Dispose()
  {
      Dispose(true);
  }
  
  public void Dispose(bool disposing)
  {
      if (disposing)
      {
          if (connection.State == System.Data.ConnectionState.Open)
          {
              connection.Close();
              connection.Dispose();
          }
      }
      //GC.SuppressFinalize(this);//Updated
  }
  //Updated
  //~DatabaseManager()
  //{
  //  Dispose(false);
  //}
  </code></pre>
  <p>So, I checked it in the debugger and the Dispose()-method is called and executes correctly.
  What am I missing? Is there something I did wrong or misunderstood?</p>
  <p>Just in case, the DataReader()-method (Updated version):</p>
  <pre><code>public IEnumerable&lt;IDataReader&gt; DataReader(String query)
      {
          using (MySqlCommand com = new MySqlCommand())
          {
              com.Connection = connection;
              com.CommandText = query;
              using (MySqlDataReader result = com.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
              {
                  while (result.Read())
                  {
                      yield return (IDataReader)result;
                  }
              }
          }
      }
  </code></pre>
  <hr />
  <p>Ok, I tried to use the yield return:</p>
  <pre><code>foreach (MySqlDataReader result in db.DataReader(&quot;SELECT * FROM module WHERE Active=1 ORDER BY Sequence ASC&quot;))
  {
      //...
  }
  </code></pre>
  <p>And I changed the DataReader-method:</p>
  <pre><code>public IEnumerable&lt;IDataReader&gt; DataReader(String query)
      {
          using (MySqlCommand com = new MySqlCommand())
          {
              com.Connection = connection;
              com.CommandText = query;
              using (MySqlDataReader result = com.ExecuteReader())
              {
                  while (result.Read())
                  {
                      yield return (IDataReader)result;
                  }
              }
          }
      }
  </code></pre>
  <p>It works in the way that I can retrieve the data, yet I still have the same problem: The connection isn't closed properly.</p>