none
How may times can we fire open and close connection in an application. RRS feed

  • Question

  • How many times can we [fire Open() method]Open an already opened connection; to the same database and another database?

    Similarly How many times an we [fire Close() method] close an already closed connection

    Friday, August 17, 2012 10:26 AM

Answers

  • Hi,

    I don't think that there are fixed limits in dotnet. In general you should avoid to many open / close calls. So database providers often support a connection pooling so a connection is only opened once and when you close it, the connection itself remains open and is just given back to the connection pool.

    So when we (the team I am working in) are using any connections, then we simply do not keep an connection open and alive. We simply keep it to the database provider to handle the connections. And if we have a method that has to read from the database, then it will create a SqlConnection and Dispose it at the end. (Normaly these classes implement IDisposable so you should make sure to call Close() or Dispose() at the end!)

    Of course - limitations can be there. The Administrator might limit the number of concurrent connections from one user and one host. And maybe the used database can have limitations, too. Regarding the servers from Microsoft, http://blogs.msdn.com/b/euanga/archive/2006/03/09/545576.aspx and http://msdn.microsoft.com/en-us/library/ms143432.aspx could be of interest but the first more or less only covers some myths of MSDE in the past.

    With kind regards,

    Konrad

    Friday, August 17, 2012 10:47 AM

All replies

  • Hi,

    I don't think that there are fixed limits in dotnet. In general you should avoid to many open / close calls. So database providers often support a connection pooling so a connection is only opened once and when you close it, the connection itself remains open and is just given back to the connection pool.

    So when we (the team I am working in) are using any connections, then we simply do not keep an connection open and alive. We simply keep it to the database provider to handle the connections. And if we have a method that has to read from the database, then it will create a SqlConnection and Dispose it at the end. (Normaly these classes implement IDisposable so you should make sure to call Close() or Dispose() at the end!)

    Of course - limitations can be there. The Administrator might limit the number of concurrent connections from one user and one host. And maybe the used database can have limitations, too. Regarding the servers from Microsoft, http://blogs.msdn.com/b/euanga/archive/2006/03/09/545576.aspx and http://msdn.microsoft.com/en-us/library/ms143432.aspx could be of interest but the first more or less only covers some myths of MSDE in the past.

    With kind regards,

    Konrad

    Friday, August 17, 2012 10:47 AM
  • Hi ankcompy,

    On one of the MSDN article, I found that it varies from product to product. As per the theory, SQL server and MSDE supports the approx 32,767 connections. But in practical, it is not possible. Because if you will open so much connections simultaneously, SQL server might go out of memory. If application is very well designed and planned, then 10-15 connections is the good limit. But is always to prefer <6 connections.


    Regards, http://www.shwetalodha.blogspot.in/

    Saturday, August 18, 2012 5:57 AM