Database connections and try catch finally?

[Kenneth Myhra]

Hi guys & girls!

We're having a discussion at work whether we should use a try catch finally
statement when opening a database connection, so that we can close the
database connection in the finally statement.
Some of us think that the try catch finally statement uses too much
resources to justify using it, while other of us think that closing it in
the finally statement is a good think, and should always be done.
I also added some code in the end of this mail to describe in code what our
disagreement is.

So what is the expert advice?
Should we use try catch finally statements, or should we stop using them and
instead rely on the .NET framework releasing our connections if an error
occurs before we have closed the connection?
Can we rely on the .NET framework to release our connections before we have
done it explicitly ourselves?

<code description="With try catch finally statement">

SqlConnection conn = null;
try {
conn = new SqlConnection(...);
conn.Open();
// Additional code where an error could occur, the database connection will
then be closed in the finally statemen...
}
catch { throw; }
finally {
if(conn != null) {
conn.Close()
conn = null;
}
// ...
}

</code>


<code description="Without try catch finally statement">

SqlConnection conn = new SqlConnection(...);
// Additional code where an error could occur, the database connection will
not be closed...
conn.Open();
conn.Close()
conn = null;
// ...

</code>


Best regards,
Kenneth Myhra
System Developer

 

[Cor Ligthert [MVP]]

Kenneth,

My favorite is to use even an extra try finaly block inside the connection
block for handling the errors.

What do you mean waste of resources by the way.
I don't see much resources used, just code.

However if you are absolute sure that there cannot be any error thrown, than
you can as well use the 'using' in C#.

Which is in fact

Try
Finally

I hope this helps,

Cor

 

[Guest]

What do you mean waste of resources by the way.
There is a slight overhead to have something watching code run (try ..
catch), but I will agree it is minimal when one considers the protection.

My two cents: Connection objects are expensive resources. Leaving them open
via exception means you have expensive resources temporarily tied up. Not a
good idea.

I have also posted a more lengthy answer in the
microsoft.public.dotnet.framework group, where this was also posted.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Miha Markic [MVP C#]]

Hi Kenneth,

You should use try/finally scenario (or "using" C# keyword which does the
same).
You never know when you'll get an exception doing database IO.

 

[Dumitru Sbenghe]

Relying on the GC finalizing thread to cleanup resources is the worst thing
you can do.

With a little luck you can get all the connections from the connection pool
and not close them and stay in this status, with your application unusable,
until the GC will run the finalization thread ... which will run who knows
when ...

And all these only because the try/catch/finally has a minor performance
overhead ... which it is bigger only when you actually throw/catch an
exception.

Dumitru

 

[Omar Al Zabir [MVP]]

I follow this approach:

using( SqlConnection con = new SqlConnection(...) )
{
con.Open(); // If it crashes here, we are fired
try
{
... work on the connection object ...
}
finally
{
if( ConnectionState.Open == con.State )
con.Close();
}
}

the "using" block ensures connection is disposed. Don't forget to Dispose
connection, command and reader objects.

A detailed use is shown here:

using( SqlConnection con = new SqlConnection(...) )
{
con.Open(); // If it crashes here, we are fired
try
{
using( SqlCommand cmd = new ...)
{
using( IDbReader reader = cmd.ExecuteReader() )
{
...

reader.Close(); // Don't forget to do this.
}
}
}
finally
{
if( ConnectionState.Open == con.State )
con.Close();
}
}

Isn't this a nice clean full proof approach?

 

[Kenneth Myhra]

Great! Thanks for your answers, it was just what I was hoping for.

I see that Greagory Beamer has answered the resource used answer, and I
completely agree with him that the minimal resources that is used by the try
finally statement is well worth it when hitting an exception.

Again thank you very much!

Regards,
Kenneth Myhra

 

[Paul Clement]

¤ Hi guys & girls!
¤
¤ We're having a discussion at work whether we should use a try catch finally
¤ statement when opening a database connection, so that we can close the
¤ database connection in the finally statement.
¤ Some of us think that the try catch finally statement uses too much
¤ resources to justify using it, while other of us think that closing it in
¤ the finally statement is a good think, and should always be done.

I'd agree with the latter. Making excuses about not using error handling in this instance is rather
lame.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Miha Markic [MVP C#]]

Hi Omar,

Out of curiosity - is explicit Close really necessary (Dispose should close
it...)?

 

[Sahil Malik [MVP]]

Here is an unequivocal unambigious answer -

USE TRY CATCH FINALLY !!! Don't leave it upto the framework to clean up
after you.

Okay now for a little more detail --

Connections MUST be closed - you SHOULD NOT leave that for the framework to
clean up. In Chapter 4 of my book I demonstrate the difference of letting
the finalizer pick after you, versus you picking after yourself - the
difference was like 18 times !!

So you must either close them in the "Finally" block or you must use the
"using" construct to ensure they are closed when disposed.

And whoever said Try Catch Finally is expensive? Finally will execute
regardless of error (catch) or not - throwing exceptions may be expensive,
but seriously do you really think the alternative is "not catch exceptions"
...

Again, YOU MUST close connections yourself. (Put the close in the finally
block of a TCF).

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Sahil Malik [MVP]]

if( ConnectionState.Open == con.State )

con.Close();

You don't really need to check for the connection state

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Mark Ashton]

connection Close & Dispose do the exact same thing (except Dispose also sets
the ConnectionString to "").

--
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Hi Omar,

Out of curiosity - is explicit Close really necessary (Dispose should
close it...)?

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

I follow this approach:

using( SqlConnection con = new SqlConnection(...) )
{
con.Open(); // If it crashes here, we are fired
try
{
... work on the connection object ...
}
finally
{
if( ConnectionState.Open == con.State )
con.Close();
}
}

the "using" block ensures connection is disposed. Don't forget to Dispose
connection, command and reader objects.

A detailed use is shown here:

using( SqlConnection con = new SqlConnection(...) )
{
con.Open(); // If it crashes here, we are fired
try
{
using( SqlCommand cmd = new ...)
{
using( IDbReader reader = cmd.ExecuteReader() )
{
...

reader.Close(); // Don't forget to do this.
}
}
}
finally
{
if( ConnectionState.Open == con.State )
con.Close();
}
}

Isn't this a nice clean full proof approach?

 

[Miha Markic [MVP C#]]

I know, that's why I am asking

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

connection Close & Dispose do the exact same thing (except Dispose also
sets the ConnectionString to "").

--
This posting is provided "AS IS", with no warranties, and confers no
rights. Please do not send email directly to this alias. This alias is for
newsgroup purposes only.

Hi Omar,

Out of curiosity - is explicit Close really necessary (Dispose should
close it...)?

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

I follow this approach:

using( SqlConnection con = new SqlConnection(...) )
{
con.Open(); // If it crashes here, we are fired
try
{
... work on the connection object ...
}
finally
{
if( ConnectionState.Open == con.State )
con.Close();
}
}

the "using" block ensures connection is disposed. Don't forget to
Dispose connection, command and reader objects.

A detailed use is shown here:

using( SqlConnection con = new SqlConnection(...) )
{
con.Open(); // If it crashes here, we are fired
try
{
using( SqlCommand cmd = new ...)
{
using( IDbReader reader = cmd.ExecuteReader() )
{
...

reader.Close(); // Don't forget to do this.
}
}
}
finally
{
if( ConnectionState.Open == con.State )
con.Close();
}
}

Isn't this a nice clean full proof approach?

Hi guys & girls!

We're having a discussion at work whether we should use a try catch
finally statement when opening a database connection, so that we can
close the database connection in the finally statement.
Some of us think that the try catch finally statement uses too much
resources to justify using it, while other of us think that closing it
in the finally statement is a good think, and should always be done.
I also added some code in the end of this mail to describe in code what
our disagreement is.

So what is the expert advice?
Should we use try catch finally statements, or should we stop using
them and instead rely on the .NET framework releasing our connections
if an error occurs before we have closed the connection?
Can we rely on the .NET framework to release our connections before we
have done it explicitly ourselves?

<code description="With try catch finally statement">

SqlConnection conn = null;
try {
conn = new SqlConnection(...);
conn.Open();
// Additional code where an error could occur, the database connection
will then be closed in the finally statemen...
}
catch { throw; }
finally {
if(conn != null) {
conn.Close()
conn = null;
}
// ...
}

</code>


<code description="Without try catch finally statement">

SqlConnection conn = new SqlConnection(...);
// Additional code where an error could occur, the database connection
will not be closed...
conn.Open();
conn.Close()
conn = null;
// ...

</code>


Best regards,
Kenneth Myhra
System Developer