Error max pool size was reached?

Question

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;

    }

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

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

Solution 2

add this line after fill.

daObj.Dispose();

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

Solution 3

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

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

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

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

View more solutions

Comments

[Pongsathon.keng]

The daObj object already in using{} block. Do we need to call Dispose()? I think don't need to do that,

[findcaiyzh]

Why I can't add comment to other's post?