I think this is because im not closing conections to my DB. I posted code im using below for my Datalayer. Do i need to close my conection? How would i do it too? Is this the code causing problems?
Heres the error code:
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.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: 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.
public DataTable getPictures()
{
//get database connection string from config file
string strConectionString = ConfigurationManager.AppSettings["DataBaseConnection"];
//set up sql
string StrSql = "SELECT MEMBERS.MemberName, Picture.PicLoc, Picture.PicID, Picture.PicRating FROM Picture INNER JOIN MEMBERS ON Picture.MemberID = MEMBERS.MemberID WHERE (Picture.PicID = @n) AND (Picture.PicAproval = 1) AND (Picture.PicArchive = 0)AND (MEMBERS.MemberSex = 'F')";
DataTable dt = new DataTable();
using (SqlDataAdapter daObj = new SqlDataAdapter(StrSql, strConectionString))
{
daObj.SelectCommand.Parameters.Add("@n", SqlDbType.Int);
daObj.SelectCommand.Parameters["@n"].Value = GetItemFromArray();
//fill data table
daObj.Fill(dt);
}
return dt;
}
public int GetItemFromArray()
{
int myRandomPictureID;
int[] pictureIDs = new int[GetTotalNumberOfAprovedPictureIds()];
Random r = new Random();
int MYrandom = r.Next(0, pictureIDs.Length);
DLPicture GetPictureIds = new DLPicture();
DataTable DAallAprovedPictureIds = GetPictureIds.GetPictureIdsIntoArray();
//Assign Location and Rating to variables
int i = 0;
foreach (DataRow row in DAallAprovedPictureIds.Rows)
{
pictureIDs[i] = (int)row["PicID"];
i++;
}
myRandomPictureID = pictureIDs[MYrandom];
return myRandomPictureID;
}
public DataTable GetPictureIdsIntoArray()
{
string strConectionString = ConfigurationManager.AppSettings["DataBaseConnection"];
//set up sql
string StrSql = " SELECT Picture.PicID FROM MEMBERS INNER JOIN Picture ON MEMBERS.MemberID = Picture.MemberID WHERE (Picture.PicAproval = 1) AND (Picture.PicArchive = 0) AND (MEMBERS.MemberSex ='F')";
DataTable dt = new DataTable();
using (SqlDataAdapter daObj = new SqlDataAdapter(StrSql, strConectionString))
{
//fill data table
daObj.Fill(dt);
}
return dt;
}
A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size specified (100 is the default).
You can increase this amount by opening the IIS manager (start->run->inetmgr), expanding the server node on the left pane, clicking on "Application Pools", right clicking on the application pool running your application, selecting "Advanced Settings", and changing the Private Memory Limit and Virtual Memory Limit in ...
Maximum Pool Size: Maximum number of connections that can be created to satisfy client requests (default is 32)
I believe that SqlDataAdapter handle connection by itself. However, in the case of multiple back-to-back fill() to data adapter, that is more performance to open the connection in each fill() request. The result is that database connection being opened and close several times.
I think that you can control the connection by yourself.
using (SqlConnection cnn= new SqlConnection (strConectionString))
using (SqlDataAdapter daObj = new SqlDataAdapter(StrSql, cnn))
{
daObj.SelectCommand.Parameters.Add("@n", SqlDbType.Int);
daObj.SelectCommand.Parameters["@n"].Value = GetItemFromArray();
cnn.Open();
//fill data table
daObj.Fill(dt);
cnn.Close();
}
add this line after fill.
daObj.Dispose();
EDIT: Also you can recycle pool in IIS. but its best practice to close connection once used.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With