max pool size was reached

Question

   Hi, I always get this error

 

 

"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."

 

 

It seems that I have exhausted the connection pool, even when I am applied the word " using " in all my code.

sample code is below ::

// ALL the code is located in a CRUD.cs . When I create a CRUD object to call the DB.
public int InsertUpdateDelete(string mySql, Dictionary<string, object> myPara)
{
int rtn = 0;
using (SqlCommand cmd = new SqlCommand(mySql, con))
{
cmd.CommandType = CommandType.Text;
foreach (KeyValuePair<string, object> p in myPara)
{
cmd.Parameters.AddWithValue(p.Key, p.Value);
}
using (con)
{
con.Open();
rtn = cmd.ExecuteNonQuery();
con.Close();
}
}
return rtn;
}

public DataSet getDataSetPassSqlDic(string mySql, Dictionary<string, object> myPara)
{
DataSet ds;
using (SqlConnection cn = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(mySql, cn))
{
foreach (KeyValuePair<string, object> p in myPara)
{
cmd.Parameters.AddWithValue(p.Key, p.Value);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
ds = new DataSet();
using (cn)
{
cn.Open();
da.Fill(ds);
return ds;
}
}
}
}

public SqlDataReader getDrPassSql(string mySql, Dictionary<string, object> myPara)
{
using (SqlCommand cmd = new SqlCommand(mySql, con))
{
foreach (KeyValuePair<string, object> p in myPara)
{
// can put validation here to see if the value is empty or not
cmd.Parameters.AddWithValue(p.Key, p.Value);
}
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}
}

//I do not close the datareader in the CRUD class, but in the page that calls getDrPassSql(), I close it after I am done with dr object >>>>
// Here is the test page, where I call the CRUD class and the method return a datareader object,

protected void populateGvInternAttendanceLog()
{
CRUD myCrud = new CRUD();
string mySql = @"select i.internid,fName + ' ' + mi + ' '+ lname as internName , workDate
from intern i inner join internAttendance ia on i.internid=ia.internid
where internstatusid=2
order by internid";
using (SqlDataReader dr = myCrud.getDrPassSql(mySql))
{
gvInternAttendanceLog.DataSource = dr;
gvInternAttendanceLog.DataBind();
}
}

My question, what else do I have to do to avoid the " max pool size was reached. " message
I truly appreciate your help.

Al