Today I need to implement transaction in .NET. Front end language was VB.NET and database was Oracle.
This transaction is being implemented using Oracle Data Access Provider - ODP.NET.
Simple implementation is that create and open a connection, begin transaction using that connection, create command using that connection, call stored procedures or statments using command(s), if every thing gone fine and success then call commit of that transaction else rollback, and in last close and dispose connection and transaction.
Be sure not to use commit, rollback or statement that causes transaction invalidate inside the procedure that is being called within .NET transaction, otherwise that .NET transaction scope will no longer valid as within that connection commit or rollback have been called.
Sample code that make my work done is :-
=============
Private Sub Save()
Dim conn As New OracleConnection("ConnString")
Dim trans As OracleTransaction
Dim success as Boolean = False
Try
conn.Open()
trans = conn.BeginTransaction
success = saveThingOne(conn)
If success Then
success = saveThingTwo(conn)
End If
If success Then
trans.Commit()
else
trans.Rollback()
End If
Catch ex As Exception
trans.Rollback()
Finally
trans.Dispose()
conn.Close()
conn.Dispose()
End Try
End Sub
Private Sub saveThingOne(ByVal conn As OracleConnection) As Boolean
Dim success as Boolean = False
Using comm As New OracleCommand("Save_Thing_One_Stored_Procedure", conn)
comm.CommandType = CommandType.StoredProcedure
comm.Parameters.Add("IN_ID", OracleType.Number).Value = intID
comm.Parameters.Add("IN_THING_ONE", OracleType.VarChar, 60).Value = strThingONe
comm.Parameters.Add("OUT_RESULT", OracleType.Number).Direction = ParameterDirection.Output
comm.ExecuteNonQuery()
‘Here if that procedure successfully perform action then will return 0 in case of success and 1 in case of failure
success = Iif(comm.Parameters(“OUT_RESULT”).Value.ToString().equals(“0”), True, False)
Return success
End Using
End Sub
Private Sub saveThingTwo(ByVal conn As OracleConnection)
Dim success as Boolean = False
Using comm As New OracleCommand("Save_Thing_Two_Stored_Procedure", conn)
comm.CommandType = CommandType.StoredProcedure
comm.Parameters.Add("IN_ID", OracleType.Number).Value = intID
comm.Parameters.Add("IN_THING_TWO", OracleType.VarChar, 60).Value = strThingTwo
comm.Parameters.Add("OUT_RESULT", OracleType.Number).Direction = ParameterDirection.Output
comm.ExecuteNonQuery()
‘Here if that procedure successfully perform action then will return 0 in case of success and 1 in case of failure
success = Iif(comm.Parameters(“OUT_RESULT”).Value.ToString().equals(“0”), True, False)
Return success
End Using
End Sub