All or nothing for DataAdapter

[Maxwell2006]

Hi,



I am using DataAdapter.Update to update database tables based on changes in
my DataTable.



How can I make sure that the whole Update operation (which can include
several update, delete and insert operations) is within a transaction. If
anything goes wrong, the Update should rollback all changes.



Is that DataAdapter's default behavior?





Thank you,

Max

 

[Marina Levit [MVP]]

If this is not in a transaction, then some updates could succeed and others
could fail.

To put it in a transaction, begin a transaction, and assign it to the
transaction property of the update/insert/delete command objects of the data
adapter. Or, if you are using the command builder, to the select command
before your create the command builder. Then you are responsible for
committing/rolling back the transaction.

 

[Maxwell2006]

This is something I am always confuse about.



My assumption was that the Update method automatically uses a transaction,
unless we explicitly deny that by using . Isn't it the case?



If the answer is NO, then Can I use System.Transactions in ADO.NET 2.0 to do
the transactions?



Thank you for help,

Max

 

[Marina Levit [MVP]]

I don't believe it works that way. You can certainly test this, to confirm.

Yes, you can use the 2.0 transactions. I haven't had a chance to use them
yet myself, just did some reading. But I think in your case it will end up
just being an ADO transaction created for you behind the scenes anyway.

 

[Sahil Malik [MVP C#]]

Update does not automatically use Transactions.

In order for you to use Transaction on a DataAdapter, you can do so by
creating a SqlTransaction object and assign it to the
Update/Delete/InsertCommand.Transaction properties before calling "Update".

Yes you could technically use System.Transactions but that would be a bad
solution **unless** you

a) Manage connection lifetime yourself (don't let DataAdapter do that for
you).
b) Work on SQL Server 2005 (not lower).

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Maxwell2006]

Thank you for help.

Could you refer me to a link that explains why System.Transaction is a bas
solution?

We are using Oracle 9i.

Thanks again,
Max

 

[Sahil Malik [MVP C#]]

Max,

Well I had a conversation with Jim Johnson from the System.Transactions team
on his blog and then further offline with the ADO.NET team and the
System.Transactions team. So the only link with half cooked information is
on Jim Johnsons' blog in the comments over here -
http://pluralsight.com/blogs/jimjohn/archive/2005/09/15/14838.aspx#FeedBack
.. You may also find similar information on my blog as well.

Unfortunately a better cooked version that is more understandable is coming
up in my code-magazine article which was supposed to be in print in February
but got bumped up to next month .

Anyway, so just trust me on this - you don't want to mix
DataAdapter/TableAdapter with Sys.Tx - they tend to screw up on
transactions. You can get around this behavior by using SQL Server 2005 and
maintaining the connection lifetime yourself.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------