transaction

[bill yeager]

I need to update records in a database. In order to do
this, I'm performing a "delete" (on the original record)
and "insert" (on the new record) since the record I'm
deleting is part of the key. This is being done via
the "da.update(dt)" method. The first record is marked for
deletion and the second one for insertion from the
rowstate inside the datatable.

Another way is to make separate passes to the db creating
a transaction before the "delete" and commiting after
the "insert". However, because of the application logic,
this is not conducive to the scenario.

What I'd like to know is: "Do I need to encircle the
above "update" statement within a transaction or does .Net
do this for you automatically. The reason I'm wondering is
because the "update" statement will perform this step in
one db pass (although each row within the dataset actually
gets updated accordingly to the rowstate)...

 

[Duray AKAR]

Do you mean you are doing a delete followed by an insert
instead of updating the row ?

Anyway, when you call Update, the rows are processed one
by one till an error occurs, and it stops there by
default...
So you would need a transaction to rollback everything in
case of an error...
or you can trap the error during updates, and keep
updating the rest of the records with a recursive
function...

if this is the case, wouldnt it be less costly and more
controlled to send first the deleted record(s) to the
adapter, and then send the insert(s) if deletes are
successfull? You can do this by dt.Select() with
appropriate rowstate filters...

but another question is why is it required to to this for
updating a record if this is the case? wouldnt you be
dealing with more concurrency issues ?
If this is required due to the child relations, you can
define a relation in the dataset that the parent and
child tables belongs to, and enable Cascade updates on
the relation, which will solve the relation problem for
the new inserted records...

hope it helps...

Duray

 

[bill yeager]

Thanks Bill. I'll do that.........

-----Original Message-----
I don't see how the second method is different from the first other than the
transaction part...so maybe I'm misunderstanding this. But if you want to
make sure that the delete and insert happen together, you'll need either a
Stored Proc that implements the transaction, or use a ADO.NET Transaction.
DataAdapter.Update goes one change at a time sequentially, so if the first
10 changes take and the last one fails, you have 10 successful updates.

I'm attaching a draft of a tutorial I'm working on on transactions. I use
it in a ExecuteNonQuery in this example, but begin the transaction right
before Da.UPdate, and commit it right afterward. In the Exception Handler,
implement the rollback. Basically, substutie da.Update in your code for
cmd.ExecuteNonQuery in mine and you should be good to go.

Let me know if you have any problems.

HTH,

Bill

 

[bill yeager]

Duray, I'll wrap the da.update within a transaction.

Thanks!