transaction

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
Thu Sep 18 00:08:43 CDT 2003

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


>-----Original Message-----
>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)...
>
>.
>

bill
Thu Sep 18 06:13:46 CDT 2003

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" <wsyeager36@msn.com> wrote in message
>news:023901c37d49$3b675c20$a401280a@phx.gbl...
>> 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)...
>>
>
>
>

bill
Thu Sep 18 06:14:39 CDT 2003

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

Thanks!
>-----Original Message-----
>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
>
>
>>-----Original Message-----
>>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)...
>>
>>.
>>
>.
>