By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,485 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,485 IT Pros & Developers. It's quick & easy.

Try Catch

P: n/a
The use of Try in the Finally part - is that overkill?. I think of the
code failing before opening sqlCon - that would generate an error in
the Finally part. How would Finally handle that?
Try
Dim cmd As New SqlCommand
cmd.CommandText = "spSetAdLinks"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Connection = sqlCon

sqlCon.Open()

AdList = cmd.ExecuteReader

Catch ex As Exception
AdList = Nothing
Finally
Try
sqlCon.Close()
sqlCon.Dispose()
Catch ex As Exception
End Try
End Try
/Morten
Jan 5 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
A more reliable way to handle it is:

...
Finally
If sqlCon.State <SqlConnectionState.Closed Then sqlCon.Close()
End Try

and even more reliable:

...
Finally
If sqlCon IsNot Nothing AndAlso sqlCon.State <ConnectionState.Closed
Then sqlCon.Close()
End Try

The documentation states that the Close and Dispose methods of the
SqlConnection are functionally equivalent so you don't need to call both
methods. It is a matter of chaoice as to which one you call but I prefer
Close.
"Morten Snedker" <morten_spammenot_ATdbconsult.dkwrote in message
news:jt********************************@4ax.com...
The use of Try in the Finally part - is that overkill?. I think of the
code failing before opening sqlCon - that would generate an error in
the Finally part. How would Finally handle that?
Try
Dim cmd As New SqlCommand
cmd.CommandText = "spSetAdLinks"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Connection = sqlCon

sqlCon.Open()

AdList = cmd.ExecuteReader

Catch ex As Exception
AdList = Nothing
Finally
Try
sqlCon.Close()
sqlCon.Dispose()
Catch ex As Exception
End Try
End Try
/Morten

Jan 5 '07 #2

P: n/a
Morten Snedker wrote:
The use of Try in the Finally part - is that overkill?
In this case, yes. Getting rid of a Connection can't go [that] far
wrong. :-)

However, it probably reads better to check for a null object before
calling the method and handling the possible Exception, as in

Finally

If Not ( sqlCon Is Nothing ) Then ' still VB'2003
sqlCon.Dispose() ' It's got a Dispose method - use it ;-)
End If

End Try

Also, I would advise /against/ the use of empty Catch blocks - ever.
If nothing else, put a comment inside them so that, in months to come,
you remind yourself (or someone else!) why you're prepared to ignore a
particular Exception in a particular circumstance, as in (AspNet example):

Try
Response.Redirect( "http://somewhere/...", True )

Catch ex as ThreadAbortException
' Thrown by Response.Redirect
' Do Nothing

End Try

HTH,
Phill W.
Jan 5 '07 #3

P: n/a
Morten,

In this case there's no need for a nested try-catch. All you need to
do is call Dispose. Like all properly implemented Dispose methods the
SqlConnection.Dispose will not throw exceptions.

Brian

Morten Snedker wrote:
The use of Try in the Finally part - is that overkill?. I think of the
code failing before opening sqlCon - that would generate an error in
the Finally part. How would Finally handle that?
Try
Dim cmd As New SqlCommand
cmd.CommandText = "spSetAdLinks"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Connection = sqlCon

sqlCon.Open()

AdList = cmd.ExecuteReader

Catch ex As Exception
AdList = Nothing
Finally
Try
sqlCon.Close()
sqlCon.Dispose()
Catch ex As Exception
End Try
End Try
/Morten
Jan 5 '07 #4

P: n/a
Typically, all code except for simple variable declarations should
occur within Try Blocks. This impoves the user experience when
problems occur and greatly simplifies debugging problems. However,
exception handling does incur a slight performance penalty.

Problem 1 your code:
Variable Declaration should be moved "OutSide" of the Try Block. This
is neccessary because the Finally block cannot access variables that
are declared within the Try block.

Example:

Dim cmd as New SqlConnection
Try
Catch
Finally
End Try

Problem 2 your code:
To close database connects, use the connection object's Close method.
Technically, you can also call the Dispose method of the connection
object to close the connection, but the preferred techinique is to call
the Close method.

You do not need to use Both SQLConn.Close, and SQLConn.Dispose

The OverKill:
The Finally block runs after the try block and any catch blocks have
finished executing. You should use the Finally block to close any open
objects. You do not need to place a try block in the Finally block.

Advice:
1. You can inspect the value of the connections current state. By
creating an event handler, or use an if statement, such as:

If sqlCon..State = ConnectionState.Open then
'do something
End if

2. You can catch an SQL exception. And inspect the
SQLException.Errors property to access a collection of error that are
returned from the SQL server.

Morten Snedker wrote:
The use of Try in the Finally part - is that overkill?. I think of the
code failing before opening sqlCon - that would generate an error in
the Finally part. How would Finally handle that?
Try
Dim cmd As New SqlCommand
cmd.CommandText = "spSetAdLinks"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Connection = sqlCon

sqlCon.Open()

AdList = cmd.ExecuteReader

Catch ex As Exception
AdList = Nothing
Finally
Try
sqlCon.Close()
sqlCon.Dispose()
Catch ex As Exception
End Try
End Try
/Morten
Jan 5 '07 #5

P: n/a
Morten,

The way you do it is almost the same as using the Using.
(Using connection)

Using connection As New SqlConnection(connectionString)
connection.Open()
End using

However if you want it catched, than I would go for

Try
connection
try handling
catch handling
catch connection
finaly
close connection
Cor

Jan 6 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.