Question on Closing connections.
I just got an error:
Message: 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.
Once I stopped and restarted the server, it was fine.
Is there some way to check to see how many pooled connections you have left
to access - either from a web page or by the Sql Server Enterprise Manager?
I found this on a site talking about the the best way to close your
connection.
The problem is, it appears to contradict itself. Below is the section from
the article in question.
It tells you that you should put the conn.Close(); inside a Finally clause.
The last paragraph then says not to do this:
Last but not the least, never Close or Dispose your connection or any other
managed object in the class destructor or your Finalize method.
Now I am confused?
What is the best way to do this?
**********************************************************************
Closing your connections
When you intend to close your database connection, you want to make sure
that you are really closing it. The following code looks fine yet causes a
connection leak:
SqlConnection conn = new SqlConnection(myConnectionString);
conn.Open();
doSomething();
conn.Close();
If doSomething() throws an exception - conn will never get explicitly
closed. Here is how this can be corrected:
SqlConnection conn = new SqlConnection(myConnectionString);
try
{
conn.Open();
doSomething(conn);
}
finally
{
conn.Close();
}
or
using (SqlConnection conn = new SqlConnection(myConnectionString))
{
conn.Open();
doSomething(conn);
}
Did you notice that in the first example we called conn.Close() explicitly
while in the second one we make the compiler generate an (implicit) call to
conn.Dispose() immediately following the using block? The C# using block
guarantees that the Dispose method is called on the subject of the using
clause immediately after the block ends. Close and Dispose methods of
Connection object are equivalent. Neither one gives you any specific
advantages over the other.
When returning a connection from a class method - make sure you cache it
locally and call its Close method. The following code will leak a
connection:
OleDbCommand cmd new OleDbCommand(myUpdateQuery, getConnection());
intres = cmd.ExecuteNonQuery();
getConnection().Close(); // The connection returned from the first call
to getConnection() is not being closed. Instead of closing your connection,
this line creates a new one and tries to close it.
If you use SqlDataReader, OleDbDataReader, etc., close them. Even though
closing the connection itself seems to do the trick, put in the extra effort
to close your data reader objects explicitly when you use them.
Last but not the least, never Close or Dispose your connection or any other
managed object in the class destructor or your Finalize method. This not
only has no value in closing your connections but also interferes with the
garbage collector and may cause errors. For more information see
http://msdn.microsoft.com/library/en...collection.asp.
***********************************************************************************************************
Thanks,
Tom
Similar Threads
