我想这是因为我没有关闭我的数据库的连接.我在下面为我的Datalayer发布了代码.我需要关闭我的连接吗？我怎么也这样做？这是造成问题的代码吗？
超时已过期.从池中获取连接之前经过的超时时间.这可能是因为所有池连接都在使用中并且达到了最大池大小.
描述：执行当前Web请求期间发生未处理的异常.请查看堆栈跟踪以获取有关错误及其源自代码的位置的更多信息.
异常详细信息：System.InvalidOperationException：超时已过期.从池中获取连接之前经过的超时时间.这可能是因为所有池连接都在使用中并且达到了最大池大小.
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;
}
解决方法
我相信sqlDataAdapter自己处理连接.但是,在多个背靠背fill()到数据适配器的情况下,在每个fill()请求中打开连接的性能更高.结果是数据库连接被打开和关闭了好几次.
我认为您可以自己控制连接.
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(); }