C# 已达到最大池大小错误？

Error max pool size was reached?

提问by CsharpBeginner

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?

我认为这是因为我没有关闭与我的数据库的连接。我在下面发布了用于我的 Datalayer 的代码。我需要关闭连接吗？我也要怎么做？这是导致问题的代码吗？

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.

异常详细信息：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;

    }

采纳答案by Pongsathon.keng

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() 到数据适配器的情况下，在每个 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();
    }

回答by AJP

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 中回收池。但最好的做法是一旦使用就关闭连接。

回答by findcaiyzh

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

using 确保 Displose 将被调用。我认为发布的代码很好

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 对象是否在其他地方没有dispose。

回答by Jon Crowell

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