locked
all pooled connections were in use and max pool size was reached RRS feed

  • Question

  • User-158363518 posted

    Error :  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.

    Is there a problem with the following command ?

    public SqlDataReader returnDataReader(SqlCommand DRcmd)
    {
    try
    {
    DRcmd.Connection = Openconn();
    sqldatareader = DRcmd.ExecuteReader();
    return sqldatareader;
    }
    catch (Exception Ex)
    {
    throw Ex;
    }
    finally
    {
    DRcmd.Connection = null;
    }
    }
    public SqlConnection Openconn()
    {
    if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
    {
    conn.Open();
    }
    return conn;
    }


    public SqlConnection Closeconn()
    {
    if (conn.State == ConnectionState.Open)
    {
    conn.Close();
    }
    return conn;
    }

    I often use the above command.

    sample ASPX  , Code Behind : 

    MyClassName mcn=new MyClassName ();

    SqlCommand cmd=new SqlCommand();

    cmd.CommandText="Commandtext";

    cmd.CommandType=CommandType.StoredProcedure;

    SqlDataReader reader=null;

    reader=mcn.returnDataReader(cmd);

    if(reader.HasRows)

    {

    //DoCommand

    }

    cmd.close();

    reader.close();

    reader.Dispose();

    Tuesday, July 24, 2018 6:23 AM

Answers

  • User-369506445 posted

    hi

     are you using SQL DataReader ?? if yes check are you closing the data reader?

    I think in your code your  DataReader is opening

    here I put a sample for <g class="gr_ gr_76 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="76" data-gr-id="76">solve</g> it 

    string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;";
    
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(queryString, connection))
                    {
                        connection.Open();
    
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            // Call Read before accessing data.
                            while (reader.Read())
                            {
                                Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1]));
                            }
                        }
                    }
                }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 24, 2018 6:48 AM
  • User753101303 posted

    It would be :

    sqldatareader = DRcmd.ExecuteReader(CommandBehavior.CloseConnection);
    DRcmd.Dispose(); // Disposing the command should be ok. I used to keep a separate connection variable though.

    Then the client code should make sure to close the reader once done and it will automatically close the underlying connection.

    If new you could also consider to use https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/overview rather than low level ADO.NET.

    Edit: you are assigning the data reader to a DataSource before calling DataBind ? It should read the reader until the end and close the reader which will then close the connection that doesn't need to be exposed any more.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 24, 2018 11:14 AM
  • User-369506445 posted

    in your <g class="gr_ gr_20 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="20" data-gr-id="20">aspx</g> page try below 

               MyClassName mcn = new MyClassName();
                SqlCommand cmd = new SqlCommand();
    cmd.CommandText="Commandtext";
    cmd.CommandType=CommandType.StoredProcedure;

    using (SqlDataReader reader=mcn.returnDataReader(cmd)) { if (reader.HasRows) { //DoCommand } }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 24, 2018 11:26 AM
  • User475983607 posted

    I recommend a different design approach.   One approach is wrapping the DataReader member in a class and implement IDisposable.  Another approach that I prefer is a crafting a generic class/method that populates and returns a type.  The later requires reflection and plenty of example on the Internet.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 24, 2018 11:31 AM
  • User753101303 posted

    For a start open, close the connection as needed (with the ExecuteReader exception where it will be closed when the reader closes). The "using" statement is your friend and allows to handle that more easily. Based on that enhance as needed.

    If you end up at exposing data using strongly typed objects you may look at existing ORMs (whose purpose is to expose db data as "raw"  .NET objects).

    See https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement

    Not directly related but turning an exception into an error code is considered bad (you can forget to test, if an error happens the caller doesn't have any info about the exception).Use a general handler to log and report exceptions and catch locally if you can really do something about an exception.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 26, 2018 7:47 AM

All replies

  • User-369506445 posted

    hi

     are you using SQL DataReader ?? if yes check are you closing the data reader?

    I think in your code your  DataReader is opening

    here I put a sample for <g class="gr_ gr_76 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="76" data-gr-id="76">solve</g> it 

    string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;";
    
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand command = new SqlCommand(queryString, connection))
                    {
                        connection.Open();
    
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            // Call Read before accessing data.
                            while (reader.Read())
                            {
                                Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1]));
                            }
                        }
                    }
                }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 24, 2018 6:48 AM
  • User753101303 posted

    Hi,

    You set the connection variable to null rather than really closing it (it is closed only when you try to use a connection again, does it really happen ?)

    You could also have a look at the C# using statement (which is under the hood a try/finally block whose purpose is to dispose an object when an exception happens)

    Edit: and when executing a datareader you have an option so that the underlying connection can be closed when the data reader is closed avoiding to have to expose the connection (so that it can be closed once done) to the calling code.

    Tuesday, July 24, 2018 7:00 AM
  • User-158363518 posted

    I have a class that includes the following code and its name is MyClassName . 

    public SqlDataReader returnDataReader(SqlCommand DRcmd)
    {
    try
    {
    DRcmd.Connection = Openconn();
    sqldatareader = DRcmd.ExecuteReader();
    return sqldatareader;
    }
    catch (Exception Ex)
    {
    throw Ex;
    }
    finally
    {
    DRcmd.Connection = null;
    }
    }
    public SqlConnection Openconn()
    {
    if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
    {
    conn.Open();
    }
    return conn;
    }


    public SqlConnection Closeconn()
    {
    if (conn.State == ConnectionState.Open)
    {
    conn.Close();
    }
    return conn;
    }

    I send a Sql Command for it and get a Sql Data Reader . 

    I Save it (Sql Data Reader) in a SqlDatareader in Webform .

    When i Close the Connection in MyClassname => returnDataReader(SqlCommand DRcmd) . SqlDatareader Not Worked.

    So, I Set it to null;

    Tuesday, July 24, 2018 11:02 AM
  • User753101303 posted

    It would be :

    sqldatareader = DRcmd.ExecuteReader(CommandBehavior.CloseConnection);
    DRcmd.Dispose(); // Disposing the command should be ok. I used to keep a separate connection variable though.

    Then the client code should make sure to close the reader once done and it will automatically close the underlying connection.

    If new you could also consider to use https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/overview rather than low level ADO.NET.

    Edit: you are assigning the data reader to a DataSource before calling DataBind ? It should read the reader until the end and close the reader which will then close the connection that doesn't need to be exposed any more.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 24, 2018 11:14 AM
  • User-369506445 posted

    in your <g class="gr_ gr_20 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="20" data-gr-id="20">aspx</g> page try below 

               MyClassName mcn = new MyClassName();
                SqlCommand cmd = new SqlCommand();
    cmd.CommandText="Commandtext";
    cmd.CommandType=CommandType.StoredProcedure;

    using (SqlDataReader reader=mcn.returnDataReader(cmd)) { if (reader.HasRows) { //DoCommand } }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 24, 2018 11:26 AM
  • User475983607 posted

    I recommend a different design approach.   One approach is wrapping the DataReader member in a class and implement IDisposable.  Another approach that I prefer is a crafting a generic class/method that populates and returns a type.  The later requires reflection and plenty of example on the Internet.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 24, 2018 11:31 AM
  • User-158363518 posted

    You can refer to me a good resource ? 

    Thursday, July 26, 2018 4:18 AM
  • User-158363518 posted

    My class structure is as follows:

    What is your suggestion for optimizing the following class?

    Thanks.

    public class MyClassName
    {
    private string connectionString;
    private SqlConnection conn;
    private DataTable sqldatatable = new DataTable();
    private SqlDataReader sqldatareader;
    private SqlCommand cmd = new SqlCommand();
    string databaseString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    public MyClassName()
    {
    connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    conn = new SqlConnection(databaseString);
    }
    public string getConnectionString()
    {
    return connectionString;
    }

    public MyClassName(string connectionString)
    {
    this.connectionString = connectionString;
    }
    public SqlConnection Openconn()
    {
    if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
    {
    conn.Open();
    }
    return conn;
    }
    public SqlConnection Closeconn()
    {
    if (conn.State == ConnectionState.Open)
    {
    conn.Close();
    }
    return conn;
    }
    public int ExecuteQuery(SqlCommand Execcmd)
    {
    try
    {
    Execcmd.Connection = Openconn();
    Execcmd.ExecuteNonQuery();
    return (1);
    }
    catch (Exception)
    {

    // Save Error In DB or TXT
    return (-1);
    }
    finally
    {
    Execcmd.Connection = Closeconn();
    }
    }
    public DataTable returnDataTable(SqlCommand DTcmd)
    {
    SqlDataAdapter DTadapter;
    try
    {
    sqldatatable.Clear();
    DTcmd.Connection = Openconn();
    DTadapter = new SqlDataAdapter(DTcmd);
    DTadapter.Fill(sqldatatable);
    }
    catch (Exception Ex)
    {

    // Save Error In DB or TXT
    Ex.Message.ToString();
    }
    finally
    {
    DTcmd.Connection = Closeconn();
    }
    return sqldatatable;
    }
    public SqlDataReader returnDataReader(SqlCommand DRcmd)
    {
    try
    {
    DRcmd.Connection = Openconn();
    sqldatareader = DRcmd.ExecuteReader(CommandBehavior.CloseConnection);
    return sqldatareader;
    }
    catch (Exception Ex)
    {

    // Save Error In DB or TXT
    throw Ex;
    }
    finally
    {
    DRcmd.Dispose();
    }
    }
    }

    Thursday, July 26, 2018 4:33 AM
  • User753101303 posted

    For a start open, close the connection as needed (with the ExecuteReader exception where it will be closed when the reader closes). The "using" statement is your friend and allows to handle that more easily. Based on that enhance as needed.

    If you end up at exposing data using strongly typed objects you may look at existing ORMs (whose purpose is to expose db data as "raw"  .NET objects).

    See https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement

    Not directly related but turning an exception into an error code is considered bad (you can forget to test, if an error happens the caller doesn't have any info about the exception).Use a general handler to log and report exceptions and catch locally if you can really do something about an exception.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 26, 2018 7:47 AM