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

  • Thread starter Thread starter James Radke
  • Start date Start date
J

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
 
James Radke said:
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
Click to expand...

Here's the correct order:
 
James Radke said:
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
Click to expand...

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
 
James
Try
set up SQL connection
open the SQL connection
execute SQL query
Catch
Throw
Finally
close the SQL connection
end try
Click to expand...
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?
Click to expand...
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/default.asp?url=/library/en-us/vbls7/html/vblrfVBSpec8_11_1_1.asp

Hope this helps
Jay
 
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
 
Back
Top