Timeout expired

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;

    }

---

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

---

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#).

---

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()".