Datasets and transactions (SQL CE)

[wooz]

Hi !
I have few datasets, all of them works on the same connection (SQL CE
server). I do some opeations on these datasets and then call an "update"
method on each one.
How to update all datasets transactionally ? For example:

ds1 - dataset
ds2 - dataset
ds3 - dataset

ds1.Tables(0).update - OK
ds2.Tables(0).update - OK
ds3.Tables(0).update - Fail

In this case all changes made by ds1 and ds2 should be rolled up.


Thanks in advance.

 

[Darren Shaffer]

You can create and begin a transaction on your single connection to the SQL
CE
database, but this transaction must be included in each command used to
update the underlying tables in SQL CE. Depending on the approach you
have taken with your SqlCeDataAdapter you may have to move away from
using SqlCeCommandBuilder (which automatically provides your select, insert,
update, delete commands) and provide your own four command objects
for your adapters and include this one transaction in all but the select
commands.
Of course you must ensure that the same connection on which you started
the transaction is used for all three dataset updates.

--
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com