Try Catch

[Morten Snedker]

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

 

[Stephany Young]

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.

 

[Phill W.]

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.

 

[Brian Gideon]

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

 

[JimmyKoolPantz]

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.

 

[Cor Ligthert [MVP]]

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