best practices for cleaning up connections, commands and transactions after errors occur?

[Andrew]

I would like people's opinions on how best to clean up SQL Server
connections and transactions after errors occur while using them. For
example, assume you have the following:

Public Function test()
Dim conn As New SqlClient.SqlConnection("connection string here")
Dim cmd As New SqlClient.SqlCommand("MyStoredProc", conn)

Try
conn.Open()

cmd.ExecuteNonQuery()

conn.Close()
Catch ex as Exception
' Possibly do something more than rethrow.
throw ex
Finally
conn.Dispose()
cmd.Dispose()
End Try
End Function

Now let us assume that ExecuteNonQuery() throws a SQL Exception.

1. After the exception, should I still try to call Close() on the connection
in the Catch block?
2. If the answer to #1 is yes, shouldn't one rather move the Close() to the
finally block where it will always be called?
3. Is there a chance that calling Close() will itself throw an exception -
either within the finally or catch blocks?


At the end of the day, what is important is to ensure that this connection
is returned to the connection pool as quickly as possible no matter what the
outcome of the function call.

Thoughts and ideas would be appreciated.

Andrew

 

[Ajay [MVP]]

When you have an error during nonexecution query
you need to call a close. But why would get a error during
close?

Then, if you think you would be getting an error during
close then the error would come when you try to instatiate
(in this code).

So writing close during finally is not a best practice,
coz if the connection is not open the close would sure to
get an error.

Think in this lines then

 

[Andrew]

Thanks for the reply... comments are inline...

When you have an error during nonexecution query
you need to call a close. But why would get a error during
close?

I have no idea, that is why I'm asking if the close implementation raises
errors. The documentation says it may raise a SQL Exception, but I'm not
sure under what conditions this may be raised. Any ideas?

So writing close during finally is not a best practice,
coz if the connection is not open the close would sure to
get an error.

Fair enough. However, would I still need to call close after an exception
has been thrown? Is there any state the connection could be in where a call
to Close() would be invalid?

Thanks, Andrew