This may have occurred because all pooled connections were in use and max pool size was reached-CodePudding

I have an issue with my ASP.NET WebApp. I store and retrieve my data from MySQL, but the thing is, my data is very huge, resulting in getting this error message:

MySql.Data.MySqlClient.MySqlException: 'error connecting: 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.'

after I run my program and shuffle between the different tabs which opens a new connection and executes an SQL query.

I have searched online to find a way to clear the connection, but the issue is I have to return a MySqlDataReader, which requires me to keep the connection pool open, not allowing me to close the connection pool after retrieving the data, a sample code is shown below:

        public MySqlDataReader GetAllMDH2(string div, string classs, string div2, string queryStr, string selectedmonth, string selectedyear)
        {
            MySqlConnection conn = new MySqlConnection(RBconnstr);
            MySqlCommand cmd = new MySqlCommand(queryStr, conn);
            cmd.Parameters.AddWithValue("@class", classs);
            cmd.Parameters.AddWithValue("@div", div);
            cmd.Parameters.AddWithValue("@div2", div2);
            if (selectedmonth == DateTime.Now.ToString("MMMM") && selectedyear == DateTime.Now.ToString("yyyy"))
            {
                cmd.Parameters.AddWithValue("@thismonth", "%"   DateTime.Now.ToString("MMMM").Substring(0, 3)   "%");
            }
            else
            {
                cmd.Parameters.AddWithValue("@thismonth", "%"   selectedmonth.Substring(0,3)   "%");
            }
            conn.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            return reader;
            conn.Close();
        }

I have tried some solutions, for example, changing it so that when it opens the connection, it checks for any error and if there is, I close it before opening it again:

        public MySqlDataReader GetAllMDH2(string div, string classs, string div2, string queryStr, string selectedmonth, string selectedyear)
        {
            MySqlConnection conn = new MySqlConnection(RBconnstr);
            MySqlCommand cmd = new MySqlCommand(queryStr, conn);
            cmd.Parameters.AddWithValue("@class", classs);
            cmd.Parameters.AddWithValue("@div", div);
            cmd.Parameters.AddWithValue("@div2", div2);
            if (selectedmonth == DateTime.Now.ToString("MMMM") && selectedyear == DateTime.Now.ToString("yyyy"))
            {
                cmd.Parameters.AddWithValue("@thismonth", "%"   DateTime.Now.ToString("MMMM").Substring(0, 3)   "%");
            }
            else
            {
                cmd.Parameters.AddWithValue("@thismonth", "%"   selectedmonth.Substring(0,3)   "%");
            }
            try
            {
                conn.Open();
            }
            finally
            {
                conn.Close();
                conn.Open();
            }
            MySqlDataReader reader = cmd.ExecuteReader();
            return reader;
            conn.Close();
        }

This does not work, so I try catching the error, and close the connection before opening it again:

        public MySqlDataReader GetAllMDH2(string div, string classs, string div2, string queryStr, string selectedmonth, string selectedyear)
        {
            MySqlConnection conn = new MySqlConnection(RBconnstr);
            MySqlCommand cmd = new MySqlCommand(queryStr, conn);
            cmd.Parameters.AddWithValue("@class", classs);
            cmd.Parameters.AddWithValue("@div", div);
            cmd.Parameters.AddWithValue("@div2", div2);
            if (selectedmonth == DateTime.Now.ToString("MMMM") && selectedyear == DateTime.Now.ToString("yyyy"))
            {
                cmd.Parameters.AddWithValue("@thismonth", "%"   DateTime.Now.ToString("MMMM").Substring(0, 3)   "%");
            }
            else
            {
                cmd.Parameters.AddWithValue("@thismonth", "%"   selectedmonth.Substring(0,3)   "%");
            }
            try
            {
                conn.Open();
            }
            catch (MySqlException)
            {
                conn.Close();
                conn.Open();
            }
            finally
            {
                conn.Close();
                conn.Open();
            }
            MySqlDataReader reader = cmd.ExecuteReader();
            return reader;
            conn.Close();
        }

But this still produces the same error, where it skips the catch block and goes to conn.Open.

To give more detail on what I do with the MySqlDataReader, I take the Data and pass the information and binds it into a GridView, displaying the data, hence I cannot close the connection before returning MySqlDataReader:

gv_meetingrooms.DataSource = meet.GetAllMDH2(div, class, div2, querystr, month, year);
gv_meetingrooms.DataBind();

Any idea on how to solve this issue? Trying to publish this WebApp onto a server, but if you navigate through the tabs too much, which executes multiple SQL queries, it hangs the whole website, causing it to crash.

Any help is appreciated so do let me know if you need anymore information, thank you in advance!

CodePudding user response：

Move the ** MySqlDataReader** into the try block

try
{
    conn.Open();
    MySqlDataReader reader = cmd.ExecuteReader();
    return reader;
}
catch (MySqlException ex)
{
    logger(ex);
}
finally
{
    conn.Close();
    conn.Open();
}
return null;

CodePudding user response：

The problem is that the unmanaged resources (the connection) are left open in the method call, and never closed.

// Note that a MySqlConnection is added as a parameter to the function.
public MySqlDataReader GetAllMDH2(string div, string classs, string div2, string queryStr, string selectedmonth, string selectedyear, MySqlConnection conn)
{
    MySqlCommand cmd = new MySqlCommand(queryStr, conn);
    ...
    return reader;
}

And to call the method you should

MySqlConnection conn = new MySqlConnection(RBconnstr);
MySqlDataReader reader = GetAllMDH2(...., conn);
while(reader.Read())
{
    // Use the datareader
}
conn.Close(); // This line will avoid your error message.

Note: If you were using SQL Server, I know it is not the case, you could have taken benefit of the SqlCommand.ExecuteReader(CommandBehavior) and the CommandBehavior.CloseConnection. Sadly it is not the case.