Closing a connection confusion

[tshad]

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/e...programmingessentialsforgarbagecollection.asp.

***********************************************************************************************************

Thanks,

Tom

 

[Sericinus hunter]

Question on Closing connections. ....
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?

Finally clause is not the same as Finalize method. That's probably
the source of the confusion.

 

[W.G. Ryan - MVP]

In the 2.0 framework there's a perfmon stat that tells you your pooled
connections.

BTW, there's no contradiction, a finally block isn't a destructor. For
instance, you can have 20 zillion try/catch/finally's in the same method -
if you're using C#, you can use a using block and play it safe though (or I
believe VB new has that construct as well).

HTH,

Bill

 

[tshad]

In the 2.0 framework there's a perfmon stat that tells you your pooled
connections.

BTW, there's no contradiction, a finally block isn't a destructor. For
instance, you can have 20 zillion try/catch/finally's in the same method -
if you're using C#, you can use a using block and play it safe though (or
I believe VB new has that construct as well).

I see.

If I get the message, is there some way, other than waiting for a long
period of time or shutting down Sql Server and restarting it, to close some
of the open connections?

Obviously, best to find all the places that it is not being closed and close
them there, but just curious if there was a way to do it programmatically.

Thanks,

Tom

 

[Cor Ligthert [MVP]]

Tom,

Using the using in database handling is relying that your connection exist
forever. Not any check is done that the Database sever is running or
whatever, so if not your program blows up. I don't like it here. In my idea
it has to be something as in a kind of pseudo code.

try
open connection
try
do your database stuff
catch
do your error handling in the database stuff
catch
do your error handling in the connection
finally
close your connection

I hope this helps,

Cor

 

[tshad]

Tom,

Using the using in database handling is relying that your connection exist
forever. Not any check is done that the Database sever is running or
whatever, so if not your program blows up. I don't like it here. In my
idea it has to be something as in a kind of pseudo code.

try
open connection
try
do your database stuff
catch
do your error handling in the database stuff
catch
do your error handling in the connection
finally
close your connection

Makes sense.

Thanks,

Tom