Transactions in ADO.NET 2.0 (VB.NET) with Oracle

[tab773]

This is how we got transactions to work using ADO.NET 2.0 (VB.NET) with Oracle:

'''''''''''''''''''''''''''''''''''''''''''''''''''
'Defined outside of the class
Imports VB = Microsoft.VisualBasic
Imports System.Data.OracleClient

'Friend Class frmYourForm
' Inherits System.Windows.Forms.Form

'cnGlobal is a previously defined ADO.NET connection that is OPEN

1: Dim oraTrans As OracleTransaction
2: oraTrans = cnGlobal.BeginTransaction()

'define the command object we use
3: Dim cmd1 As New OracleCommand
4: cmd1.Connection = cnGlobal
5: cmd1.Transaction = oraTrans

6: sSQL = "delete from tbl_YourTable"
'asssign the command object the SQL statement and execute the SQL
7: cmd1.CommandText = sSQL
8: cmd1.ExecuteNonQuery()

9: oraTrans.Commit()


'Best Regards,
'Tim

 

[chiph]

If you disassemble the SqlCommand object (from the framework, for MS-SQL, using ILDASM or Reflector) you'll see that it doesn't use the transaction object at all -- it uses the one from the SqlConnection object.

It looks like Oracle is the opposite -- the OracleCommand object really does use it!

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[IT4EVR]

I could be wrong but I don't think transactions run off of command objects. Even in the code posted above, he is starting the transaction off of the connection object, committing the transaction from the connection object. It just needs to know what the command object's connection object is.

 

[misterstick]

does anyone have experience with using System.Transactions.TransactionScope?

from the help it looks like this would be much easier, although that mostly doesn't apply.


mr s. <

 

[tab773]

Thanks for the comments ... the below code runs and will rollback the tranaction on the 2nd (non-existent table name) SQL call ... comments are appreciated, for sure.

Best,
Tim

'''''''''''''''''''''''''''''''''''''''''''''''

Imports VB = Microsoft.VisualBasic
Imports System.Data.OracleClient
Imports System.Exception

'Friend Class frmYourForm
' Inherits System.Windows.Forms.Form

'cnGlobal is a previously defined ADO.NET connection that is OPEN


Private Sub YourSub()

8: Dim oraTrans As OracleTransaction
9: oraTrans = cnGlobal.BeginTransaction() 'required per original application functionality, ADO.NET, 20070404, tab

Try

'define the command object we use to execute the SQL statement
Dim cmd1 As New OracleCommand
cmd1.Connection = cnGlobal
cmd1.Transaction = oraTrans

'DELETE SQL
10: sSQL = "delete from tbl_YourTable"

'asssign the command object the SQL statement and execute the SQL
cmd1.CommandText = sSQL
cmd1.ExecuteNonQuery()

'DELETE with non-existent table name
14: sSQL = "delete from tbl_NonExistent_TableName"

'asssign the command object the SQL statement and execute the SQL
16: cmd1.CommandText = sSQL
17: cmd1.ExecuteNonQuery()

65: oraTrans.Commit() 'tab - commit the transaction

Catch ex As OracleException
Application.DoEvents()
MsgBox("Please check the following message, Thank You: " & vbCrLf & ex.Message & " " & vbCrLf _
& Me.Name & " ERROR - Perform_Copy() ", MsgBoxStyle.ApplicationModal)
Application.DoEvents()
LogSysEvents(strInterface, Me.Name & " : Perform_Copy", Err.Number, Err.Description, Erl())
sMsg = "Transaction Rolled back"
oraTrans.Rollback()

Catch ex1 As Exception
MsgBox("Please check the following message, Thank You: " & vbCrLf & ex1.Message & " " & vbCrLf _
& Me.Name & " ERROR - Perform_Copy() ", MsgBoxStyle.ApplicationModal)
LogSysEvents(strInterface, Me.Name & " : Perform_Copy", Err.Number, Err.Description, Erl())
sMsg = "Transaction Rolled back"
oraTrans.Rollback()

Finally
'nothing for now
End Try

End Sub

 

[IT4EVR]

You should also remove your database objects from memory in your code, such as the command object, connection object and transaction object.

I like to enclose my database objects in using statements, which takes care of closing and/or disposing of the object without having to explicitly call the Dispose or Close methods.

I'm not as sure how using statements work in VB.NET because I use C#. Also I noticed you have a global connection object. Are you keeping a connection open for the full life of the application? That may not be a good idea.

using(OracleCommand cmd1 = new OracleCommand())
{
//code goes here
cmd1.ExecuteNonQuery();
}

Also, I have found that I'd rather pass back the exception message to the client, using a form, and display the message on the form. Someone could possibly be away from the computer when the exception occurs, so any further processing of the application would stop until someone hit OK on the MessageBox.