none
Data Access Block DataSet all pooled connections were in use and max pool size was reached

    Question

  •  

    Hi

    The website has runtime error. I checked IIS log.

    Exception information:
        Exception type: InvalidOperationException
        Exception message: 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.

    Thread information:
        Thread ID: 1
        Thread account name: NT AUTHORITY\NETWORK SERVICE
        Is impersonating: False
        Stack trace:    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Modules.Systems.SqlHelper.PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:\Website\SQLHelper.cs:line 111
       at Modules.Systems.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:\Website\SQLHelper.cs:line 551
       at Modules.Systems.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText) in C:\Website\SQLHelper.cs:line 531
       at ProductModel.Product.Page_Load(Object sender, EventArgs e) in C:\Website\product1.ascx.cs:line 63
       at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
       at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.Web.UI.Control.OnLoad(EventArgs e)
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Control.LoadRecursive()
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)


    I think the error happened due to the Dataset connect not close.

    The following is the code.

    How can I fix it?



    DataSet objDS;  
    string str = "SELECT * FROM productdesc where pid = 1 order by id asc";  
     
    objDS = SqlHelper.ExecuteDataset(DBConnection.GetConnection(), CommandType.Text, str);  
                    this.grid1.Visible = false;  
                    this.grid2.Visible = true;  
                    this.GridView1.DataSource = objDS;  
                    this.GridView1.DataBind();  
                    objDS.Clear();  
     
     
    //SQLHelper.cs  
    public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)  
            {  
                return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);  
            }  
    </param> 
            public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)  
            {  
                SqlCommand cmd = new SqlCommand();  
                PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);  
                SqlDataAdapter da = new SqlDataAdapter(cmd);  
                DataSet ds = new DataSet();  
                da.Fill(ds);  
                cmd.Parameters.Clear();  
                return ds;  
            }  
     
     
    Monday, December 22, 2008 2:35 AM

Answers

  • You need to dispose your connection. In this way your connection pool will be fully used after a while. You can do this in the following way:

    Instead of the following code

    objDS = SqlHelper.ExecuteDataset(DBConnection.GetConnection(), CommandType.Text, str);     
                    this.grid1.Visible = false;     
                    this.grid2.Visible = true;     
                    this.GridView1.DataSource = objDS;     
                    this.GridView1.DataBind();     
                    objDS.Clear();    
     

    Use the following

    using (var conn = DBConnection.GetConnection())  
    {  
    objDS = SqlHelper.ExecuteDataset(conn, CommandType.Text, str);     
                    this.grid1.Visible = false;     
                    this.grid2.Visible = true;     
                    this.GridView1.DataSource = objDS;     
                    this.GridView1.DataBind();     
                    objDS.Clear();    
    }  
     

    see http://msdn.microsoft.com/en-us/library/yh598w02.aspx for more info on the using statement
    Ewald
    • Marked as answer by Harry Zhu Monday, December 29, 2008 8:57 AM
    Monday, December 22, 2008 6:49 AM