Getting Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

Question

I have been facing this timed out Problem more frequently. I have been using MYSQL Database and working on windows application. I have even tried on using having clause but faced the same situation

   public bool VerifyStock(string serialnumber)
    {
        con = new MySqlConnection(connstring);

        string readData = "select * from Fn_Inventory where ModelNumber = '" + serialnumber + "'";

        cmd = new MySqlCommand(readData, con);
        cmd.Parameters.AddWithValue("@ModelNumber", serialnumber);


        con.Open();

        dr = cmd.ExecuteReader();

        if (dr.HasRows)
        {
            //while (dr.Read())
            if (dr.Read())
            {

                decimal invquntity = Convert.ToDecimal(dr["AvailableQuantity"].ToString());

                decimal quantity = Convert.ToDecimal(txtQuantity.Text);

                decimal sinvquntity = invquntity - quantity;
                if (sinvquntity >= 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }

            }
            else
            {
                return false;
            }
        }
        else
        {
            return false;
        }
        con.Close();
    }

Answer 1

Increasing connection pool size and time out will be a quick fix. You can refer here for more information

And properly close opened connections and the end outside conditions. Use a try catch finally block and add this code in finally block so that it is always executed.

if (con.State == ConnectionState.Open)
{
     con.Close();
}

Answer 2

You are opening connection but not closing it based on your conditions and returning from code using return statement either close connections where ever you are using return statement or use using() clause.When connections remain open there will be no connection free to use so pool will be full. Always close connections when you are done reading values from datareader.

using (SqlConnection con = new SqlConnection()) { //your code here}