dataSet and transaction

[Joël]

I have a dataset with 2 tables and a foreign constraint between them.
Some changes have been made in the tables (insert, update and delete).
Now I want to update the database. I call Update on each dataAdapter. One by
table.
But the second Update fail.

What is the best solution to rollback the first Update in the database ?

Worst, I can keep the DataSet unchanged, using the merge technique and have
high differencies between the dataset and the database.
Is there any way to prevent this situation, and how ?

 

[Gavin Joyce]

Hi,

Have a look at the following sample code. Notice how each method (Insert,
Update, Delete etc.) has an overload that accepts a SqlTransaction.

http://www.gavinjoyce.com/files/nTierGen/samplecode/ContactDAO_Base.cs.txt

If you design your data access classes like this, you can aggregate them
together under the one transation as follows:

http://www.gavinjoyce.com/forums/ShowPost.aspx?PostID=228

Thanks,
Gavin

--
___________________________________________________________
nTierGen.NET Code Generator - http://www.nTierGen.NET/

Stored Procedures (Get, GetPaged, Insert, Update, Delete)
Data Access Layer - C#
Business Rules Layer - C# & VB.NET
Strongly-Typed DataSets - C#
Web Services - C#
___________________________________________________________

 

[Teemu Keiski]

Hi,

specify the underlying Command objects (UpdateCommand in this case if you
want to access it through DataAdapter.) to use one and same transaction
object. If you use ADO transactions, you get a transaction object by calling
Connection object's BeginTransaction method.

Then when creating Command objects, give them the same transaction object
into their Transaction property.
Then do the call for updates inside Try..Catch block and if error is threwn
call Transaction object's Rollback method. This makes sure all updates etc
on that same pending transaction are rollbacked and no changes is done to
the Database. If you want to take this further to the DataSet, it has
methods AcceptChanges and RejectChanges that you can respectively call to
either finalize or rollback the changes you did to the DataSet.

So the pseudo-logic could be something like:

-Create connection and open it
-Call connection's BeginTransaction to get Transaction object
-Create command's and give them the Transaction (if logic requires you can
do this also only when trying to update)
-Create DataAdapters from Commands
-Get ds and do changes in it.
-Try to Update changes to database by using DataAdapter. Do this in try
catch block
-If update fails, call Transaction object's RollBack method to rollback db
changes and call DataSet's RejectChanges to rollback changes in DataSet
-If update is OK, call Transaction object's Commit method and dataSet's
AcceptChanges method.

Note that there are SqlTransaction or OleDbTransaction same way as there are
OleDbDataAdapter or SqldataAdapter vased on the provider used.

Hope this helps. If you need concrete example, let me know.

--
Teemu Keiski
MCP, Designer/Developer
Mansoft tietotekniikka Oy
http://www.mansoft.fi

ASP.NET Forums Moderator, www.asp.net
AspAlliance Columnist, www.aspalliance.com

Email:
(e-mail address removed)

 

[devnet]

PureObjects is a free web based code generator that builds several major portions of a .NET application including the database access layer, stored procedures, business, and model layers for C# and VB.NET
http://www.pureobjects.com