asked on

Wrapping many database changes in a transaction

I have a web service which uses a dataset input parameter to update the master database. Most of update/insert code is of this variety

connection.open
For each row in dataset.tables("Table1").rows
'update or insert
next
For each row in dataset.tables("Table2").rows
'update or insert
next
...
For each row in dataset.tables("Table10").rows
'update or insert
next
connection.close

it updates 10+ tables.

My problem is that when we get errors with connectivity or bad data, we get it partly updated.

What is the best way to wrap this in a transaction? I have done them before but not to this degree. Does wrapping all the statements in one transaction work?

Also, one of the tables has this:

For each row in dataset.tables("Table6").rows
row.beginedit
'update or insert
row.acceptchanges
next

how does that affect using a transaction?

Thanks in advance

Bob Learned

If you apply the transaction to the Connection object, then committing or rolling back should apply to all changes made during that session.

gdinunzio

ASKER

Thanks.

Apparantly using the transactionscope is the preferred way to do it now as opposed to the old transaction object.

This is my code

 Try
            Using ts As New TransactionScope
                Using cnn As New OracleConnection(connectionstring)
                    cnn.Open()
                    Using cmdVldtUsr As New OracleCommand("SELECT ...", cnn)
                        With cmdVldtUsr
                            .BindByName = True                           
                            rdr = .ExecuteReader
                        End With
                        'Validate User
                        If rdr.Read() Then                           
                            Using cmd As New OracleCommand()
				'Update all tables here   
                            End Using                              
                        End If
                        rdr.Close()
                    End Using
                End Using
                'Commit Changes
                ts.Complete()
            End Using
        Catch ex As TransactionAbortedException
            ' throw("TransactionAbortedException Message: {0}", ex.Message)
        Catch ex As ApplicationException
            ' MessageBox.Show("ApplicationException Message: {0}", ex.Message)
        Finally

        End Try

Open in new window

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gdinunzio

ASKER

I took the MSDN info as gospel, but you are correct since i am only connecting to one data source, I'll test both and report back what i find.

Thanks again.