When should I close the connection? RSS

• • Reply
  

  vahid.ch

  Member

  294 Points

  331 Posts

  When should I close the connection?

  May 23, 2012 05:44 AM|LINK

  Hi there,I have a web site which issues errors sometimes,I think it's due to not closing connection.

  error issue:

  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.

  As I close my Con,it works fine.

  and I Open my connection as below:

  public class BasePage:System.Web.UI.Page
  {
      public static Commands db = new Commands();
      public static SqlConnection Con;
      private string ConString;
  	public BasePage()
  	{
          ConString = System.Configuration.ConfigurationManager.ConnectionStrings["SPConnection"].ToString();
          Con = new SqlConnection(ConString);
          Con.Open();
         
  	}
  ...
  ...
  ...

  
  
  
  

  • Edited by vahid.ch on May 23, 2012 05:45 AM
• • Reply
  

  urenjoy

  Star

  12181 Points

  1824 Posts

  Re: When should I close the connection?

  May 23, 2012 05:50 AM|LINK

  You should use using statement for connection.

  using (SqlConnection cn = new SqlConnection(connectionString))
  {
      SqlCommand cm = new SqlCommand(commandString, cn)
      cn.Open();
      cm.ExecuteNonQuery();
  }

  Check following thread:

  C# - closing Sql objects best practice
  
  

  http://techbrij.com
  • Edited by urenjoy on May 23, 2012 05:51 AM
  • Edited by urenjoy on May 23, 2012 05:52 AM
  • Edited by urenjoy on May 23, 2012 05:53 AM
• • Reply
  

  gopalanmani

  Star

  7826 Points

  1320 Posts

  Re: When should I close the connection?

  May 23, 2012 06:01 AM|LINK

  Hi,

  The general recommendation is to Open/Execute/Close as soon as possible.

  and check this url,

  http://blogs.msdn.com/b/spike/archive/2008/08/25/timeout-expired-the-timeout-period-elapsed-prior-to-obtaining-a-connection-from-the-pool.aspx

  Hope  its help you

  
  

  Regards,
  Gopalan Mani
  My Tech blog
  • Marked as answer by mamba dai - msft on May 30, 2012 06:10 AM