Knowledge Required:
- Exception Handling
- ADO.net
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.
