达到最大池大小的错误?

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.

描述:在当前web请求执行期间发生未处理的异常。请查看堆栈跟踪，以获得关于错误的更多信息，以及错误起源于代码的何处。

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.

异常详细信息:系统。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;

    }

4 个解决方案

#1

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.

我相信SqlDataAdapter自行处理连接。但是，对于数据适配器的多个背对背填充()，在每个fill()请求中打开连接会有更好的性能。结果是数据库连接被打开和关闭了好几次。

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();
    }

#2

If you don't want to guess whether you are handling the connections efficiently, you can run a query to tell how many are open:

如果您不想猜测您是否正在有效地处理连接，您可以运行查询，以确定有多少连接是打开的:

SELECT 
    DB_NAME(dbid) as DatabaseName, 
    COUNT(dbid) as ConnectionCount,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame
order by count(dbid) desc

#3

add this line after fill.

填充后添加这一行。

daObj.Dispose();

EDIT: Also you can recycle pool in IIS. but its best practice to close connection once used.

编辑:还可以在IIS中回收池。但一旦使用，最好的做法是关闭连接。

#4

using makes sure the Displose will be called. I think the posted code is fine

使用时要确保调用方。我认为张贴的代码是可以的

Default Max Pool Size is 100. It is not likely that using Integrated Security and logged users more than 100.

默认最大池大小为100。使用集成安全性和登录用户不可能超过100。

Please check whether some setting in DataBaseConnection in config file conflict with connection pool. refer to :http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.80).aspx

请检查配置文件中的DataBaseConnection中的某些设置是否与连接池发生冲突。查阅:http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v = vs.80). aspx

And please check whether SqlDataAdapter or SqlDataConnection object is not dispose in other places.

请检查SqlDataAdapter或SqlDataConnection对象是否不在其他地方进行处理。

#1