Transaction scope

[bryanhale]

Firstly excuse any stupidness, after doing a vb.net course 3 years ago I started working on it properly for the first time about 2 weeks ago!

I am working on a project where I am converting some VB code to VB.net. The app connects to oracle so I have decided to do this by using oracleclient. To make sure I have everything working I have built a new basic app that connects to oracle, runs a select, insert and update, each in separate subs and then commits them if successful.

I have managed to share transactions across different subs and then commit when they are run successfully by using the following code...

In the main module I have declared globals for the database and transaction vars

[COLOR="Blue"]
    Public gOraDatabase As New OracleConnection

    Public gTransaction As OracleTransaction

    Public gTransCommand As New OracleCommand[/COLOR]

Then in the main sub after creating oracle connection named gOraDatabase…

           [COLOR="blue"] 'set up connection field for global transaction command

            gTransCommand.Connection = gOraDatabase

 

            ' Start a transaction

            gTransaction = gOraDatabase.BeginTransaction(IsolationLevel.ReadCommitted)

 

            ' Assign transaction object for a pending transaction

            gTransCommand.Transaction = gTransaction

 

            'run sub to select data from db

            selectData()

 

            'run sub to insert data in to a table on db

            insertData()

 

            'run sub to update data on a table on db

            updateData()

 

            'If no errors have occured in code above then commit the changes.  Only want to commit if 

            'all the above subs have run okay, i.e. haven't thrown an exception back.

            gTransaction.Commit()[/COLOR]

In the insertdata and updatedata subs I have set strSQL as the insert or update statement and then done the following…

[COLOR="blue"]
            gTransCommand.CommandText = strSQL

            gTransCommand.ExecuteNonQuery()[/COLOR]

Is this the correct way of handling transactions that are shared among different subs or is there a different way of doing it, e.g. may be I pass the transaction as a parameter?

Any advice would be much appreciated.

 

[bryanhale]

If anyone has any information on transaction processing - i.e. how to set it up when sharing a transaction across different sub procedures that would be much appreciated.

 

[Tom]

You can't run multiple commands to the db in the middle of a transaction. Stay within your transaction using your same db objects.