Batch update problem

[Guest]

I have a winform datagrid where a user can modify more than one row at a
time. A button calls theDatapter.Update() to persist changes to db. I wrap
the Update in a client-side transaction and
commit-on-success/rolllback-on-failure. In addition, exception-handler code
invokes RejectChanges to undo user modifications to the DataTable. Now,
here's the problematic scenario: a user modified two rows where the
modification on the 2nd row causes a db error (e.g., constraint violation).
The problem is that RejectChanges won't rollback the DataTable modification
to the first row but only for the 2nd row. At this point, the 1st row's value
and its db row counterpart are no longer in sync. Here's my code:

' N.B.: m_* variables are module-level.
Dim trans As SqlTransaction
Try
m_conn.Open()
trans = m_conn.BeginTransaction
m_da.SelectCommand.Transaction = trans
m_cmdBuilder.RefreshSchema()
m_da.Update(m_ds, "Authors")
trans.Commit()
MessageBox.Show("Update OK!")
Catch exc As Exception
m_ds.RejectChanges()
If Not trans Is Nothing Then
trans.Rollback()
End If
MessageBox.Show(exc.Message)
Finally
m_conn.Close()
End Try

Any workarounds for this? Thanks in advance =)

 

[Miha Markic [MVP C#]]

Hi jester,

You should first create a copy of data using (DataTable or DataSet)
GetChanges method, use it for update and after sucessful update Merge it
with original source.
Take note that you should manually take care of autoinc fields (if you have
any) as Merge will create you duplicate records otherwise.

 

[Guest]

That was the answer I was dreading to hear, though deep inside I had a
strong hunch there was no other way of cutting it. Actually, this scenario
would cause a concurrency violation when using CommandBuilder to
auto-generate commands. Obviously, Update calls AcceptChanges for each row
successfully updated, regardless if subsequent rows raise an exception.
Would've been nice if there was an option to indicate a batch update (i.e.,
AcceptChanges at the DataTable level) when invoking Update. Thanks anyway,
Miha. Really appreciate it =)

Hi jester,

You should first create a copy of data using (DataTable or DataSet)
GetChanges method, use it for update and after sucessful update Merge it
with original source.
Take note that you should manually take care of autoinc fields (if you have
any) as Merge will create you duplicate records otherwise.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I have a winform datagrid where a user can modify more than one row at a
time. A button calls theDatapter.Update() to persist changes to db. I wrap
the Update in a client-side transaction and
commit-on-success/rolllback-on-failure. In addition, exception-handler
code
invokes RejectChanges to undo user modifications to the DataTable. Now,
here's the problematic scenario: a user modified two rows where the
modification on the 2nd row causes a db error (e.g., constraint
violation).
The problem is that RejectChanges won't rollback the DataTable
modification
to the first row but only for the 2nd row. At this point, the 1st row's
value
and its db row counterpart are no longer in sync. Here's my code:

' N.B.: m_* variables are module-level.
Dim trans As SqlTransaction
Try
m_conn.Open()
trans = m_conn.BeginTransaction
m_da.SelectCommand.Transaction = trans
m_cmdBuilder.RefreshSchema()
m_da.Update(m_ds, "Authors")
trans.Commit()
MessageBox.Show("Update OK!")
Catch exc As Exception
m_ds.RejectChanges()
If Not trans Is Nothing Then
trans.Rollback()
End If
MessageBox.Show(exc.Message)
Finally
m_conn.Close()
End Try

Any workarounds for this? Thanks in advance =)

 

[Miha Markic [MVP C#]]

Hi jester,

That was the answer I was dreading to hear, though deep inside I had a
strong hunch there was no other way of cutting it. Actually, this scenario
would cause a concurrency violation when using CommandBuilder to
auto-generate commands.

Why?

Obviously, Update calls AcceptChanges for each row

successfully updated, regardless if subsequent rows raise an exception.

Yup, and it is the correct way.

Would've been nice if there was an option to indicate a batch update
(i.e.,
AcceptChanges at the DataTable level) when invoking Update. Thanks anyway,
Miha. Really appreciate it =)

Hmm. Depends. Normally not, because you want to Update within a transaction
when it succeeds or fails.
Plus, every command may also retrieve some data (autoinc fields, timestamps
for example) - where would you store the data before AcceptChanges?
As I see it, the only problem is with Merge/AutoInc fields - otherwise is
just fine.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi jester,

You should first create a copy of data using (DataTable or DataSet)
GetChanges method, use it for update and after sucessful update Merge it
with original source.
Take note that you should manually take care of autoinc fields (if you
have
any) as Merge will create you duplicate records otherwise.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I have a winform datagrid where a user can modify more than one row at
a
time. A button calls theDatapter.Update() to persist changes to db. I
wrap
the Update in a client-side transaction and
commit-on-success/rolllback-on-failure. In addition, exception-handler
code
invokes RejectChanges to undo user modifications to the DataTable. Now,
here's the problematic scenario: a user modified two rows where the
modification on the 2nd row causes a db error (e.g., constraint
violation).
The problem is that RejectChanges won't rollback the DataTable
modification
to the first row but only for the 2nd row. At this point, the 1st row's
value
and its db row counterpart are no longer in sync. Here's my code:

' N.B.: m_* variables are module-level.
Dim trans As SqlTransaction
Try
m_conn.Open()
trans = m_conn.BeginTransaction
m_da.SelectCommand.Transaction = trans
m_cmdBuilder.RefreshSchema()
m_da.Update(m_ds, "Authors")
trans.Commit()
MessageBox.Show("Update OK!")
Catch exc As Exception
m_ds.RejectChanges()
If Not trans Is Nothing Then
trans.Rollback()
End If
MessageBox.Show(exc.Message)
Finally
m_conn.Close()
End Try

Any workarounds for this? Thanks in advance =)

 

[Miha Markic [MVP C#]]

Ah, you were reffering to original solution.
That's why you have to do the GetChanges() which will create a *copy* of
changed rows (that need to be updated), update it and if successful merge it
with *original* data.
Like a transaction

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

would cause a concurrency violation when using CommandBuilder to
auto-generate commands.

Miha: Why?

Because if the update on the first row goes thru and you can't call
RejectChanges on it in case of an error on the 2nd row, modifying any of
the
columns of the first row to a value other than the original value will
mess
up the WHERE statement built by the CommandBuilder.

Say, a column has a value of 'Smith', then modified to 'Smithson', then
Update was called. This particular row goes thru and invokes AcceptChanges
but the second row causes an error so that transaction rolls back. At this
point, the local row has a value of 'Smithson' but the backend db has a
value
of 'Smith' (the orig value because the transaction was rolled back). If
you
edit this row again to a value other than 'Smith', say 'Smithsonian', the
CommandBuilder will generate the ff WHERE clause for the UPDATE statement:
... WHERE Name = 'Smithson'. But since the db has 'Smith' for its value
(because it was rolled back), the UPDATE will affect no rows. At this
point,
CommandBuilder will generate an exception "Concurrency violation: Update
affect zero rows."

Hi jester,

That was the answer I was dreading to hear, though deep inside I had a
strong hunch there was no other way of cutting it. Actually, this
scenario
would cause a concurrency violation when using CommandBuilder to
auto-generate commands.

Why?

Obviously, Update calls AcceptChanges for each row

successfully updated, regardless if subsequent rows raise an exception.

Yup, and it is the correct way.

Would've been nice if there was an option to indicate a batch update
(i.e.,
AcceptChanges at the DataTable level) when invoking Update. Thanks
anyway,
Miha. Really appreciate it =)

Hmm. Depends. Normally not, because you want to Update within a
transaction
when it succeeds or fails.
Plus, every command may also retrieve some data (autoinc fields,
timestamps
for example) - where would you store the data before AcceptChanges?
As I see it, the only problem is with Merge/AutoInc fields - otherwise is
just fine.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

:

Hi jester,

You should first create a copy of data using (DataTable or DataSet)
GetChanges method, use it for update and after sucessful update Merge
it
with original source.
Take note that you should manually take care of autoinc fields (if you
have
any) as Merge will create you duplicate records otherwise.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com


I have a winform datagrid where a user can modify more than one row
at
a
time. A button calls theDatapter.Update() to persist changes to db.
I
wrap
the Update in a client-side transaction and
commit-on-success/rolllback-on-failure. In addition,
exception-handler
code
invokes RejectChanges to undo user modifications to the DataTable.
Now,
here's the problematic scenario: a user modified two rows where the
modification on the 2nd row causes a db error (e.g., constraint
violation).
The problem is that RejectChanges won't rollback the DataTable
modification
to the first row but only for the 2nd row. At this point, the 1st
row's
value
and its db row counterpart are no longer in sync. Here's my code:

' N.B.: m_* variables are module-level.
Dim trans As SqlTransaction
Try
m_conn.Open()
trans = m_conn.BeginTransaction
m_da.SelectCommand.Transaction = trans
m_cmdBuilder.RefreshSchema()
m_da.Update(m_ds, "Authors")
trans.Commit()
MessageBox.Show("Update OK!")
Catch exc As Exception
m_ds.RejectChanges()
If Not trans Is Nothing Then
trans.Rollback()
End If
MessageBox.Show(exc.Message)
Finally
m_conn.Close()
End Try

Any workarounds for this? Thanks in advance =)