Timeout expired

Question

I am writing C# code and using LINQ and some stored procedures, i am careful about opening and closing the connections but i keep getting this 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.

my code works perfectly except the occurence of this error, what can i do about it?

Thanks for any ideas.

 public static List<int> GetIslemIdleribySPbyCariId(int cariId)
    {
        string connString = System.Configuration.ConfigurationManager.ConnectionStrings["LocalSqlServer1"].ConnectionString;

        SqlConnection sqlConn = new SqlConnection(connString);
        sqlConn.Open();

        List<int> islemidleri = new List<int>();
        islemidleri.Clear();

        SqlCommand cmd;
        cmd = new SqlCommand("GetIslemIdleri", sqlConn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@CARIID", cariId));

        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                islemidleri.Add(reader.GetInt32(0));

            }
            cmd.Parameters.Clear();
        }

        sqlConn.Close();

        return islemidleri;

    }
    /// <summary>
    /// SP kullanarak dovizturlerini döndürür
    /// </summary>
    /// <returns>string listesi döndürür için döviz türleri var TL, USD vs.</returns>
    public static List<string> GetDovizTurleribySP()
    {
        string connString = System.Configuration.ConfigurationManager.ConnectionStrings["LocalSqlServer1"].ConnectionString;

        SqlConnection sqlConn = new SqlConnection(connString);
        sqlConn.Open();

        List<string> dovizTanimlari = new List<string>();

        string commandGetDovizTanimlari = "EXEC GetDovizTanimlari";
        SqlCommand cmd;
        cmd = new SqlCommand(commandGetDovizTanimlari, sqlConn);

        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                dovizTanimlari.Add(reader.GetString(0));
            }

        }
        return dovizTanimlari;

    }

Can you show some code that demonstrates how you are managing the connections? — Chris Taylor, Mar 28 '11 at 14:00

score 3 · Accepted Answer · answered Mar 28 '11 at 14:13

From your code you are not closing the connection in the GetDovizTurleribySP function. I would suggest that you use the using statement to ensure that the connections are closed even if an exception occurs.

It might look something like this

public static List<string> GetDovizTurleribySP()
{
  string connString = System.Configuration.ConfigurationManager.ConnectionStrings["LocalSqlServer1"].ConnectionString;

  using (SqlConnection sqlConn = new SqlConnection(connString))
  {
    sqlConn.Open();

    List<string> dovizTanimlari = new List<string>();

    string commandGetDovizTanimlari = "EXEC GetDovizTanimlari";
    SqlCommand cmd;
    cmd = new SqlCommand(commandGetDovizTanimlari, sqlConn);

    using (var reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        dovizTanimlari.Add(reader.GetString(0));
      }

    }
    return dovizTanimlari;
  }
}

thank you Mr.Taylor, i just saw the missing sqlConn.Close(). And as much as i understand from your code if i use "using" i dont have to write sqlConn.Close(), is that right? — Bastardo, Mar 28 '11 at 14:18
@EmreVeriyaz, that is correct. The using statement actually wraps the code in a try/finally and will call the Dispose method on the object referenced by the using statement. This will ensure that the connection is closed even if the code does not reach the end of the function because of an exception or a early return etc. You have used the same concept for your DataReader, you are ensuring that the reader is closed once you are done with it, which is good. — Chris Taylor, Mar 28 '11 at 14:24

score 1 · Answer 2 · answered Mar 28 '11 at 14:08

1

After seeing your code theres 2 things I want to point out, wrap your SqlCommand, DataReader and sqlConnection in using statements (yes nested using statements) and also when you create List you don't have to call clear() afterwards as the list should be initialized to emtpy already (at least I think so in c#).

answered Mar 28 '11 at 14:08

Jesus Ramos

22,940
10
58
88

i did not use it like this but i will try right now, thanks for the advice. – Bastardo Mar 28 '11 at 14:09
Thanks i will rebuild my code And i found the cause of the error it was just a missing sqlConn.Close() before return dovizTanimlari;. :) – Bastardo Mar 28 '11 at 14:16
1

that is where using statements come in handy, even if an exception is thrown it all gets closed and cleaned up which means you don't have logic errors – Jesus Ramos Mar 28 '11 at 14:19

score 0 · Answer 3 · edited Mar 09 '16 at 20:16

Testing and testing:

Close(); 
Dispose(); 
SqlConnection.ClearPool(connection); 
SqlConnection.ClearAllPools();

Using expression, among others, finally I found out that the problem of "Open Pools" for each OpenConnection not reuses the "Pool" maintained (AWAITING COMMAND) causing saturation in ASP.NET client application (the only way is to restart IIS to release ), I realized that is the call to the connection string in the .config:

*System.Configuration.ConfigurationManager.ConnectionStrings ["ConnectionSQL"] ConnectionString.;*

Apparently he calls the external assembly, causing "difference" in the connection string or hash.

Solution:

I added up the assembly:

System.Configuration"** and replacement implicitly invoking: ConfigurationManager.ConnectionStrings ["ConnectionSQL"] ConnectionString;

This solved the problem, No more "Pools" were executed by each "Open()".

Timeout expired

3 Answers3