.NETGURU
  Search:  
NamespacesDiscussions.NET v1.1Dot Net Guru's BlogIT Certification
Batch update problem
Messages   Related Types
This message was discovered on microsoft.public.dotnet.framework.adonet.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.
Post a new message to this list...

jester (VIP)
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 =)
Reply to this message...
 
    
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.

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

"jester" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]
Reply to this message...
 
    
jester (VIP)
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 =)

"Miha Markic [MVP C#]" wrote:

[Original message clipped]
Reply to this message...
 
    
Miha Markic [MVP C#]
Hi jester,

"jester" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Why?

Obviously, Update calls AcceptChanges for each row
> successfully updated, regardless if subsequent rows raise an exception.

Yup, and it is the correct way.

[Original message clipped]

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

[Original message clipped]
Reply to this message...
 
    
jester (VIP)
[Original message clipped]

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."

"Miha Markic [MVP C#]" wrote:

[Original message clipped]
Reply to this message...
 
    
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

"jester" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]
Reply to this message...
 
 
System.Data.DataSet
System.Data.DataTable
System.Data.SqlClient.SqlTransaction
System.Windows.Forms.MessageBox




ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
Ad


Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews
.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT
Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project
FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.
Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials
 
ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
 Copyright © ExamGuru, Inc. 2001-2006
Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification