save to database by transaction

[md110]

hi
i have 2 table relation in database (order and order_details)
my database is Access
how to save SYNCHRONIC in database by transaction
thanks

 

[jmcilhinney]

You have two options:

1. Use an OleDbTransaction:

connection.Open()

Dim transaction As OleDbTransaction = connection.BeginTransaction()

Try
    adapter1.Update(table1)
    adapter2.Update(table2)

    transaction.Commit()
Catch ex As Exception
    transaction.Rollback()
Finally
    connection.Close()
End Try

2. Use a TransactionScope:

Try
    Using transaction As New TransactionScope
        connection.Open()

        adapter1.Update(table1)
        adapter2.Update(table2)

        transaction.Complete()
    End Using
Catch ex As Exception

Finally
    connection.Close()
End Try

In both cases it is assumed that both DataAdapters use the same connection.

Please, if you have questions about either or both classes, read the documentation for yourself first. Most of what you will need to know is there. If you have more questions after that, post back then.

 

[cjard]

Does the first route work? I've always had to add code to a partial class to enroll every command within an adapter in a transaction.. Jolly nuisance!

The TransactionScope option is easy, but quite heavyweight; causes a noticeable drag the first time it's used.

OP: I cobbled together a class a while back that uses reflection to enroll tableadapters in transactions, if youre using them..

 

[jmcilhinney]

Does the first route work? I've always had to add code to a partial class to enroll every command within an adapter in a transaction.. Jolly nuisance!

The TransactionScope option is easy, but quite heavyweight; causes a noticeable drag the first time it's used.

OP: I cobbled together a class a while back that uses reflection to enroll tableadapters in transactions, if youre using them..

Hey, that's right! It's so long since I used a SqlTransaction or the like I forgot that you had to set the Transaction property of all the Command objects explicitly. It seems I got too used to the ease of using the TransactionScope.

 

[cjard]

TransactionScope causes me the most major headaches on Oracle.. By default Oracle shipped a debug version of OraMTS.dll with the 9i client and it gives spurious "Attempt to access protected memory. This is often an indication other memory is corrupt" errors. Drove the users nuts, so eventually I put the reflected class together to use classic conenction transaction stuff.. And you wouldnt believe the performance difference! Maybe its Oracle's implementation of MTS, but there's no 10 second disk thrash the first time the user clicks save.. I have no idea whether it's better for other platforms, but I wouldnt go near MTS on oracle now..

Shame cause as you say, it's much easier

 

[jmcilhinney]

TransactionScope causes me the most major headaches on Oracle.. By default Oracle shipped a debug version of OraMTS.dll with the 9i client and it gives spurious "Attempt to access protected memory. This is often an indication other memory is corrupt" errors. Drove the users nuts, so eventually I put the reflected class together to use classic conenction transaction stuff.. And you wouldnt believe the performance difference! Maybe its Oracle's implementation of MTS, but there's no 10 second disk thrash the first time the user clicks save.. I have no idea whether it's better for other platforms, but I wouldnt go near MTS on oracle now..

Shame cause as you say, it's much easier

I'm not sure of all the details but with SQL Server the TransactionScope uses a lightweight, i.e. local, transaction where possible and then promotes to a distributed transaction if and when required. Maybe Oracle uses a distributed transaction by default, which could account for the overhead. Haven't done any direct performance comparisons though. Might be worth it.