Batch queries in stored procedures?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using SQL Server stored procedures to access my tables, but I'm having
problems with concurrency violations when updating.

The ADO.NET Core Reference (Ch. 11, pg 469) says you can use batch queries
to retrieve data after submitting an update via a data adapter. Am I
correct in assuming this same concept be applied to stored procedures? If
so, a code example would be greatly appreciated.

For reference, here are the steps I took so far:
- Set DataAdapter.UpdateCommand.UpdatedRowSource = FirstReturnedRecord
- Added code to stored procedure to SELECT the updated record.
- Added code to update the database:
DataAdapter.Update(DataSet.DataTable.Select("", "", _
DataViewRowState.ModifiedCurrent))

Thank you,

Eric
 
Sure, I show how to do this in my book as well--and yes you can execute
stored procedures to post the update. Let's see your SP called by the
UpdateCommand.
What concurrency violations are you getting? What kind of concurrency
checking are you doing? Is the SP returning 1 for the rows affected value?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Yes you can do multiple resultsets via sprocs.

Concurrency violation - I think you need to implement your own solution
around this. A chapter could be written about this, lets start at "How have
you implemented concurrency checks?"


- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
 
That's in the new book... (in progress).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Addison Wesley Professional--same publisher as the Codd books. ;)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Eric,

The approach does work, but I'd like to know a little more about the
problem you're seeing to make sure the example will cover your scenario.
When is the concurrency exception occurring? On the first update attempt,
or subsequent update attempts? Does the table contain server-generated
data - auto-increment, timestamp, defaults, etc.?

I'd recommend using output parameters over returning a row for
performance reasons.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.
 
Back
Top