Is My Database Object Still Open During Try/Catch Error Handling?

Question

I'm trying to understand/confirm if my database object is still open when I apply "Try/Catch" error handling at the main procedure.

I have two procedures, including a "primary" procedure which handles my main process and then two "called" procedures that 1) does stuff and 2) adds a record to a database respectively. My "Try/Catch" error protection is only in the primary. I did that so that I could catch errors in either of the secondary procedures and handle it in 1 way (was trying to avoid multiple "Try/Catch" blocks to save time and uniform my error handling... if you think otherwise, I'm open to suggestions!).

Anyway, my concern is in the database/second called procedure is if my database objects and connections are still open if an error occurs.

Some sample code my help explain it better. See below. I added some commentary along the way.

Code:

Sub Main_Procedure()
    Try
        Call DoStuffProcedure()
        Call AddRecordProcedure()
    Catch ex As Exception
    End Try
End Sub

Sub DoStuffProcedure()
    'This procedure does a bunch of stuff... assume no errors.
End Sub

Sub AddRecordProcedure()
    Dim vDBLOCAL As String = "MyConnectionString"
    Dim vCNLOCAL As SqlConnection, vSQLOCAL As String, vCMLOCAL As SqlCommand, c As Integer

    vCNLOCAL = New SqlConnection(vDBLOCAL)
    vCNLOCAL.Open()

'Here's where I'm simulating an error. I used the wrong field name!
    vSQLOCAL = "INSERT INTO [MyTable] ([OMG! My Field Is Wrong!]) VALUES ('add something')"
    vCMLOCAL = New SqlCommand(vSQLOCAL, vCNLOCAL)

'Because of the above, the next line would have an error!
    c = vCMLOCAL.ExecuteNonQuery()

'Because of the error above, my Try/Cath would initiate from the "Main_Procedure" and this line
'would never be reached. Is my connection still open?
    vCNLOCAL.Close()
End Sub

As I state in my conde example above, because the error occurs before I close my database connection/object, the Try/Cath error handler gets initiated in the "Main_Procedure" and that line is never be reached. Is my connection still open?

Also, if it's better to do a Try/Catch in each, I'm open to suggestions (and a hint how to still have a uniform handler would be appreciated).

Thanks!!!

Answer 1

So, the database connection will remain open if an 'exception' is thrown in the AddRecordProcedure() before the vCNLOCAL.Close() statement is reached.

The Using block is designed to handle such event and it will dispose the connection properly, even if an error occurs during the execution.

Sub Main_Procedure()
    Try
        Call DoStuffProcedure()
        Call AddRecordProcedure()
    Catch ex As Exception
        ' Log or handle error here
    End Try
End Sub

Sub AddRecordProcedure()
    Dim vDBLOCAL As String = "MyConnectionString"
    Dim vSQLOCAL As String = "INSERT INTO [MyTable] ([OMG! My Field Is Wrong!]) VALUES ('add something')"
    
    ' The Using block ensures that the connection is closed even if an error occurs.
    Using vCNLOCAL As New SqlConnection(vDBLOCAL), vCMLOCAL As New SqlCommand(vSQLOCAL, vCNLOCAL)
        vCNLOCAL.Open()
        
        ' This will throw an error due to the incorrect field name
        Dim c As Integer = vCMLOCAL.ExecuteNonQuery()
    End Using
End Sub