VB.NET•Maintain Data Consistency With Try/Catch

Listing 1. Use a Try/Catch block to determine the transaction's outcome. You also maintain data consistency by calling the Rollback method from within a Catch block when an error occurs. This rolls back changes made to data when an error occurs.

Public Function MakeSale(ByVal _
	connectionString As String, _
	ByVal customerID As String, _
	ByVal productID As Integer, _
	ByVal requestedQty As Integer, 
	ByVal unitPrice As Double, _
	ByVal discount As Double) As Boolean _
	Implements ISalesComponent.MakeSale

		Dim conn As New SqlClient.SqlConnection
		Dim successfulSale As Boolean = True
		Dim dataAccess As New DataAccess
		Dim trans As SqlTransaction

		Try

			conn.ConnectionString = _
				connectionString
			conn.Open()

			' Return a transaction object from the 
			' opened connection
			trans = conn.BeginTransaction()

			' The transaction object is passed 
			' between method calls and is used to 
			' manually enlist each SQLCommand.
			successfulSale = _
				dataAccess.newOrder(conn, trans, _
				customerID, productID, _
				requestedQty, unitPrice, discount)

			If successfulSale Then

			successfulSale = _
				dataAccess.adjustInventory(conn, _
				trans, requestedQty, productID)

			If successfulSale Then

				' Check credit card limit. 
				successfulSale = _
					dataAccess.checkCreditCard()

			End If

		End If

		If successfulSale Then
			trans.Commit()
		Else

			trans.Rollback()
			successfulSale = False

		End If

	Catch ex As Exception

		' If an error occurs, rollback transaction
		trans.Rollback()
		Throw ex

	Finally

		MakeSale = successfulSale
		trans.Dispose()

		If conn.State = ConnectionState.Open Then
			conn.Close()
		End If

	End Try

End Function