Another look at Exception Handling

Level: Beginner

Knowledge Required:

• Exception Handling
• ADO.net

Description:
As we have used the Exception Handling (Try...Catch) many times. But in this post we shall understand an important aspect. Consider the following code,

Private Sub InsertSomeData()
    Dim conn As SqlClient.SqlConnection = Nothing
    Dim cmd As SqlClient.SqlCommand
    Dim tran As SqlClient.SqlTransaction = Nothing
    Try
        ' initialize connection
        conn = New SqlClient.SqlConnection("Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True")
        ' open connection
        conn.Open()
        ' begin transaction
        tran = conn.BeginTransaction

        ' create a Command
        cmd = conn.CreateCommand()

        ' setup command to insert data in 1st table
        cmd.CommandText = "INSERT INTO MyTable1(Column1) VALUES('Data in MyTable1.Column1')"
        cmd.ExecuteNonQuery()

        ' setup command to insert data in 2nd table
        cmd.CommandText = "INSERT INTO MyTable2(Column1) VALUES('Data in MyTable2.Column1')"
        cmd.ExecuteNonQuery()

        ' ok now commit the changes
        tran.Commit()
    Catch ex As Exception
        ' an exception is occurred
        ' check if transaction has been initialized then
        ' rollback
        If tran IsNot Nothing Then
            tran.Rollback()
        End If
        ' again throw the exception
        ' this is because we want the caller to be notified
        Throw ex
    Finally
        ' finally block will be executed anyway
        ' whether the exception is occurred or NOT
        ' ================---------------------
        ' Note: we are again throwing the exception in Catch block 
        '       but still the following code will be executed
        If conn IsNot Nothing AndAlso _
           conn.State = ConnectionState.Open Then
            ' if connection was established then close it
            conn.Close()
        End If
        ' =====================-------------------------
    End Try
    ' this line however will NOT be executed if
    ' exception is occurred
    Debug.Print("Outside the Try..Catch block")
End Sub

As you can see this method will be inserting data in 2 tables. Our goal is,

Begin the Transaction
Try to Insert the Data in Tables
If no error occurs on Insertion then Commit the Transaction
If error occurred then
    Rollback the Transaction
    Again throw the Exception (so the caller is being notified)
End If
At the end (whether exception is occred or NOT) Close the connection

Note that if Exception occurs then in Catch block we are throwing the same exception again. At this point it seems that control will immediately exit from this method, but fortunately this does NOT happen in this way. Instead control first jumps to Finally block then after that it exits from method.