Website down time and pooled connections were in use .net core MVC Apps

Question

While running a .net Core MVC application against Azure Mysql database, frequently we are getting the sites are running down / going offline issue.

After checking server error logs , we can see many instances of the following error close to website down time.

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

This error is occured at line connection.Open();

Following is the code we used with all db related functions and all db calls are properly inside

using()
{
  
} // so we assume the connections should be closed without explicitly writing it 

    private MySqlConnection GetConnection { get { return new MySqlConnection(_ConnectionString); } }
     
    public void siteDbFunctions()
    {
        using (MySqlConnection connection = GetConnection)
        {
            if (connection.State != System.Data.ConnectionState.Open)
            {
                connection.Open();
            }
            string query = "...";
            MySqlCommand command = new MySqlCommand(query, connection);
            command.CommandTimeout = 600;
            command.Parameters.AddWithValue("@Val1", _EditorialCategoryID);
            command.Prepare();
            using (MySqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                  .....................;
                }
            }
        }
    }
    
    

Only work around at the moment is restart App Pool / Website . Is there a better approach to read / write from db to avoid this

Answer 1

You used a new connection instance return new MySqlConnection(_ConnectionString);
"If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. " doc You could try close the connection. Maybe like following:

if (connection.State != System.Data.ConnectionState.Open)
{
    connection.Open();

    string query = "...";
    MySqlCommand command = new MySqlCommand(query, connection);
    ......


    connection.Close();
}

Another thing I consider is even connection is disposed , but GetConnection is not in the "using" clause. You could try make it simply

using (MySqlConnection connection = new MySqlConnection(_ConnectionString))