Try.. Catch... Throw - where does execution stop?

[James Radke]

Hello,

I am attempting to use the proper Try/Catch technique when accessing my
Microsoft SQL server database and have a question...

If I use something similar to the following:

Try
set up SQL connection
open the SQL connection
execute SQL query
Catch
Throw
Finally
close the SQL connection
set the SQL connection object to nothing
end try
Since I am using a Throw to push the error back up to the calling module, if
the execute query causes the issue I am thinking that the throw causes an
immediate stop to the sub - is this correct? so, wouldn't I really want to
also close the connection (after checking if it is open, of course) also
just prior to the throw statement as follows?

Catch ex as exception
if SQL connection open
close SQL connection
end if
set the SQL connection object to nothing
throw ex
Finally ....
Wouldn't this ensure that all resources are freed up/closed correctly?

Every example I have found does not do this, but instead simply closes the
SQL connection in the finally block - am I missing something?

Thanks!

Jim

[David Browne]

"James Radke" <jr*****@wi.rr.com> wrote in message
news:uT**************@TK2MSFTNGP09.phx.gbl...

Hello,

I am attempting to use the proper Try/Catch technique when accessing my
Microsoft SQL server database and have a question...

If I use something similar to the following:

Try
set up SQL connection
open the SQL connection
execute SQL query
Catch
Throw
Finally
close the SQL connection
set the SQL connection object to nothing
end try

Here's the correct order:

Since I am using a Throw to push the error back up to the calling module, if the execute query causes the issue I am thinking that the throw causes an
immediate stop to the sub - is this correct? so, wouldn't I really want to also close the connection (after checking if it is open, of course) also
just prior to the throw statement as follows?

Catch ex as exception
if SQL connection open
close SQL connection
end if
set the SQL connection object to nothing
throw ex
Finally ....
Wouldn't this ensure that all resources are freed up/closed correctly?

Every example I have found does not do this, but instead simply closes the
SQL connection in the finally block - am I missing something?

Thanks!

Jim

[David Browne]

"James Radke" <jr*****@wi.rr.com> wrote in message
news:uT**************@TK2MSFTNGP09.phx.gbl...

Hello,

I am attempting to use the proper Try/Catch technique when accessing my
Microsoft SQL server database and have a question...

If I use something similar to the following:

Try
set up SQL connection
open the SQL connection
execute SQL query
Catch
Throw
Finally
close the SQL connection
set the SQL connection object to nothing
end try

oops

Here's the correct order:

set up SQL connection
open the SQL connection
Try
execute SQL query
Catch
Throw
Finally
close the SQL connection
set the SQL connection object to nothing
end try

The Catch clause is optional, and you can use it to rethrow a more
informative error, perhaps including the SQL query as well as the error
reported from sql server.

Throw does not cause an immediate end to execution. The Finally block
_always_ runs, even after an exception is thrown or a return or other
statement branches execution.
David

[Jay B. Harlow [MVP - Outlook]]

James

Try
set up SQL connection
open the SQL connection
execute SQL query
Catch
Throw
Finally
close the SQL connection
end try This is the correct setup.
Every example I have found does not do this, but instead simply closes the
SQL connection in the finally block - am I missing something? You are correct in that a Throw will cause the current sub to "immediately
stop executing", however Finally blocks are always executed! Even with the
Throw in the catch block, the code in the Finally block will be executed.

For details see:
http://msdn.microsoft.com/library/de...ec8_11_1_1.asp

Hope this helps
Jay

"James Radke" <jr*****@wi.rr.com> wrote in message
news:uT**************@TK2MSFTNGP09.phx.gbl... Hello,

I am attempting to use the proper Try/Catch technique when accessing my
Microsoft SQL server database and have a question...

If I use something similar to the following:

Try
set up SQL connection
open the SQL connection
execute SQL query
Catch
Throw
Finally
close the SQL connection
set the SQL connection object to nothing
end try
Since I am using a Throw to push the error back up to the calling module, if the execute query causes the issue I am thinking that the throw causes an
immediate stop to the sub - is this correct? so, wouldn't I really want to also close the connection (after checking if it is open, of course) also
just prior to the throw statement as follows?

Catch ex as exception
if SQL connection open
close SQL connection
end if
set the SQL connection object to nothing
throw ex
Finally ....
Wouldn't this ensure that all resources are freed up/closed correctly?

Every example I have found does not do this, but instead simply closes the
SQL connection in the finally block - am I missing something?

Thanks!

Jim

[William Ryan]

Looks like David already answered your question, but as a precaution, I'd
recommend wrapping the Open in a try catch block of its own because many
thinga unlrelated to your code and cause that to fail and you can't do
anything with a connection that isn't open.

Cheers,

Bill
"James Radke" <jr*****@wi.rr.com> wrote in message
news:uT**************@TK2MSFTNGP09.phx.gbl...

Hello,

I am attempting to use the proper Try/Catch technique when accessing my
Microsoft SQL server database and have a question...

If I use something similar to the following:

Try
set up SQL connection
open the SQL connection
execute SQL query
Catch
Throw
Finally
close the SQL connection
set the SQL connection object to nothing
end try
Since I am using a Throw to push the error back up to the calling module, if the execute query causes the issue I am thinking that the throw causes an
immediate stop to the sub - is this correct? so, wouldn't I really want to also close the connection (after checking if it is open, of course) also
just prior to the throw statement as follows?

Catch ex as exception
if SQL connection open
close SQL connection
end if
set the SQL connection object to nothing
throw ex
Finally ....
Wouldn't this ensure that all resources are freed up/closed correctly?

Every example I have found does not do this, but instead simply closes the
SQL connection in the finally block - am I missing something?

Thanks!

Jim