"Kenneth Myhra" wrote:

> 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
>
>
>
>
>

"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@comcast.netNoSpamM> wrote
in message news:7FC4DD2B-7AAB-4AAD-8DD3-8BDF4362AFF2@microsoft.com...
> The overhead of try ... catch is very low until a problem happens, which
> is
> precisely when you need it. For this reason, I use try when I have
> database
> connections. There are a couple of patterns you can go with. The simplest
> is:
>
> try
> {
>   conn.Open();
>   //Do work here
> }
> finally
> {
>   conn.Dispose();
> }
>
> If there is an error, clean up will still happen, but the exception is
> passed up the stack. As .NET programming is by exception (usually handled
> near the UI), this is a good basic pattern. The following accomplishes the
> same:
>
> using(SqlConnection conn)
> {
>   //Create other objects
>
>   conn.Open();
>   //Do work
> }
>
> Using forces the IDispose.Dispose() method to be called when you exit the
> block. I tend to not use using here because of the way the code looks. As
> I
> have heard no reasoning that suggests using is superior to try ...
> finally, I
> have no reason to change. Pick your preference.
>
> IF, and only IF, you are going to handle the exception, then, do something
> like:'
>
> try
> {
>   conn.Open();
>   //Do work
> }
> catch (SqlException ex)
> {
>    //Log the exception? or similar
>    //AppSpecificException means code your own exception
>    throw AppSpecificException("message", ex);
> }
> finally
> {
>    conn.Dispose();
> }
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> ***************************
> Think Outside the Box!
> ***************************
>
>
> "Kenneth Myhra" wrote:
>
>> 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
>>
>>
>>
>>
>>