Effective use of DataAdapter.Update(DataSet)

[Ken Allen]

I have been reading a lot of material on the use of (specifically) the
SqlClient classes that are used to retrieve, display, edit, and update
database records. The basic approach seems to be:

1. Define a SqlConnection to the database.
2. Define a SqlDataAdapter along with a command for retrieving the data.
3. Set the SqlDataAdapter.MissingSchemaAction to AddWithKey to ensure
Primary Key information will be included.
4. Define a DataSet and use SqlDataAdapter.Fill to populate it.
5. Associate the DataSet (or DataSet.Tables[0]) with a DataGrid or some
other control(s).
6. Permit the user to edit the contents of the DataSet; rows may be added,
deleted or modified.
7. Create a new SqlDataAdapter for the same command used to populate the
original data; again set the MissingSchemaAction to be complete.
8. Either define a SqlCommandBuilder or define commands for the Insert,
Delete and Update members of the SqlDataAdapter.
9. Invoke the SqlDataAdapter.Update method to commit all changes to the
database.
10. Some have suggested then using SqlDataAdapter.Fill on the same DataSet
to merge changes made by other clients with the existing information.

There seem to be a significant number of flaws with this approach, and it is
not easy to find discussions or suggestions for alternatives or methods to
avoid these flaws. Of course, I may be missing something completely and not
understanding how this process is actually working.

I want to make it clear that I develop client and client/server
applications. I have never developed IIS, IIS.Net or Web Service
'applications'.

Issue #1 -- This mechanism, as with the vast majority of the information I
can find on these specific classes, seem to presume single user access to
the database and, for the most part, ignore the impact of concurrent updates
from multiple client applications into the same database. Presuming the
above process is used concurrently by multiple client applications, all
making changes to the same table, there will be no way to control which
changes are made to a specific record! Consider the case where three (3)
clients make changes to the same record, where two of the changes are to
different columns and the third change involves all of the columns from the
first two. The first client issues the Update and the changes are saved; the
second client issues an Update and that change is saved, effectively
reversing the change from the first client; when the third client commits
the changes then both of the first two changes are overwritten -- and none
of the clients are aware of this.

Issue #2 -- The documentation indicates that repeating the
SqlDataAdapter.Fill method call on an existing DataSet will merge the
changes from the table into the DataSet, but this is only partially true --
it does not seem to deal with records that are in the DataSet but which have
been deleted from the database table (likely by another client). In order to
achieve this it seems one should issue a DatSet.Tables[0].Clear method call
before the SqlDataAdapter.Fill call to ensure that this happens, although
none of the documentation mentions this.

Issue #3 -- I have found no references to how one might effectively approach
dealing with the situation where multiple records are modified in different
ways by different concurrent users. Considert the case where client A and
client B both retrieve the same set of records from the same table. Client A
modifies one record and saves the change (the Update succeeds). Client B at
that point has no understanding that changes have been made, and the client
makes changes to several records, including the one changed by client A. It
is not clear how the Update could permit the changes to be made to all of
the records except the one modified by Client A (or even how this would be
detected), and then permit this information to be reported back to client B.
The only option seems to be to fail the entire update from Client B, but
there is still no clear way to explain which records are affected. This is
compounded by the indication that if the Update method succeeds the
AcceptChanges method on the DataSet seems to be called.

Issue #4 -- There does not seem to be any automatic way to indicate, say in
a DataGrid, which records have been modified and which have not. There also
does not seem to be an automatic way to indicate that some record changes
were committed to the database and other changes were not.

Issue #5 -- These issues are compounded when the code is implemented in a
client/server architecture as opposed to a single application (or in a web
application, although I have not personally written any of those).

Issue #6 -- While there is documentation on the SqlDataAdapter.RowUpdating
event, there is no information on how (or if) the code can indicate that the
specific record can be ignored or skipped. Ideally it would be nice to be
able to compare the original record in the DataSet with the current content
of the database table and reject the record accordingly. In order for the
client to understand what happened, one would likely want to retain the
modified record from the DataSet so it can be compared to the new version
from the table and shown to the user (permitting them to make more changes
without having to memorize the specific change they made).

Issue #7 -- There is no documentation on how the Fill and Update method
processes play together when executed against the same table from different
client applications concurrently. Consider the scenario from issue #1 and
attempt to define the specific data values that each of the three clients
will see on their screen.

Issues #8 -- The existing classes and controls seem to be focused solely
upon multiple-record updates. There seems to be little attention paid to
scenarios where the change to each individual record must be processed
before the user can make changes to other records. I know that much of .Net
is geared toward IIS.Net and Web Server applications, but many client/server
applications have traditionally dealt with single record processing, at
least in part to avoid some of the problems that are discussed above. I am
willing to convert to this 'batch' mode of processing, but I cannot see the
degree of control that I need.

I apologize of some of the comments here are not crystal clear.

-ken

 

[William Ryan eMVP]

I have been reading a lot of material on the use of (specifically) the
SqlClient classes that are used to retrieve, display, edit, and update
database records. The basic approach seems to be:

1. Define a SqlConnection to the database.
2. Define a SqlDataAdapter along with a command for retrieving the data.
3. Set the SqlDataAdapter.MissingSchemaAction to AddWithKey to ensure
Primary Key information will be included.
4. Define a DataSet and use SqlDataAdapter.Fill to populate it.
5. Associate the DataSet (or DataSet.Tables[0]) with a DataGrid or some
other control(s).
6. Permit the user to edit the contents of the DataSet; rows may be added,
deleted or modified.
7. Create a new SqlDataAdapter for the same command used to populate the
original data; again set the MissingSchemaAction to be complete.
8. Either define a SqlCommandBuilder or define commands for the Insert,
Delete and Update members of the SqlDataAdapter.
9. Invoke the SqlDataAdapter.Update method to commit all changes to the
database.
10. Some have suggested then using SqlDataAdapter.Fill on the same DataSet
to merge changes made by other clients with the existing information.

There seem to be a significant number of flaws with this approach, and it is
not easy to find discussions or suggestions for alternatives or methods to
avoid these flaws. Of course, I may be missing something completely and not
understanding how this process is actually working.

I want to make it clear that I develop client and client/server
applications. I have never developed IIS, IIS.Net or Web Service
'applications'.

Issue #1 -- This mechanism, as with the vast majority of the information I
can find on these specific classes, seem to presume single user access to
the database and, for the most part, ignore the impact of concurrent updates
from multiple client applications into the same database. Presuming the
above process is used concurrently by multiple client applications, all
making changes to the same table, there will be no way to control which
changes are made to a specific record! Consider the case where three (3)
clients make changes to the same record, where two of the changes are to
different columns and the third change involves all of the columns from the
first two. The first client issues the Update and the changes are saved; the
second client issues an Update and that change is saved, effectively
reversing the change from the first client; when the third client commits
the changes then both of the first two changes are overwritten -- and none
of the clients are aware of this.

It depends on how you set up your update logic. In general you'll throw a
Concurrency exception unless you specifically code it not to.

Issue #2 -- The documentation indicates that repeating the
SqlDataAdapter.Fill method call on an existing DataSet will merge the
changes from the table into the DataSet, but this is only partially true --
it does not seem to deal with records that are in the DataSet but which have
been deleted from the database table (likely by another client). In order to
achieve this it seems one should issue a DatSet.Tables[0].Clear method call
before the SqlDataAdapter.Fill call to ensure that this happens, although
none of the documentation mentions this.

True, but if you change one of those values it will throw a concurrency
exception provided you coded your updates this way.

Issue #3 -- I have found no references to how one might effectively approach
dealing with the situation where multiple records are modified in different
ways by different concurrent users. Considert the case where client A and
client B both retrieve the same set of records from the same table. Client A
modifies one record and saves the change (the Update succeeds). Client B at
that point has no understanding that changes have been made, and the client
makes changes to several records, including the one changed by client A. It
is not clear how the Update could permit the changes to be made to all of
the records except the one modified by Client A (or even how this would be
detected), and then permit this information to be reported back to client B.
The only option seems to be to fail the entire update from Client B, but
there is still no clear way to explain which records are affected. This is
compounded by the indication that if the Update method succeeds the
AcceptChanges method on the DataSet seems to be called.

You can trap the concurrency exception and then pop up something for the
user to choose what they want to happen or hard code a business rule to deal
with this. It takes a little forethought but it's quite flexible.

Issue #4 -- There does not seem to be any automatic way to indicate, say in
a DataGrid, which records have been modified and which have not. There also
does not seem to be an automatic way to indicate that some record changes
were committed to the database and other changes were not.

If you bind to a DataView, you can set the RowStateFilter
http://www.knowdotnet.com/articles/dataviews1.html and accomodate this. I do
it all the time. You can see all the recrods, modified ones, deleted ones,
added ones..lot's of flexibility here and you only need one more line of
code to create the dataview

Issue #5 -- These issues are compounded when the code is implemented in a
client/server architecture as opposed to a single application (or in a web
application, although I have not personally written any of those).

It depends on how the app is coded b/c you can have a more complicated
version on a single instance than a multi-user although they correlate very
highly. A LOT depends on design and how you handle things.

Issue #6 -- While there is documentation on the SqlDataAdapter.RowUpdating
event, there is no information on how (or if) the code can indicate that the
specific record can be ignored or skipped. Ideally it would be nice to be
able to compare the original record in the DataSet with the current content
of the database table and reject the record accordingly. In order for the
client to understand what happened, one would likely want to retain the
modified record from the DataSet so it can be compared to the new version
from the table and shown to the user (permitting them to make more changes
without having to memorize the specific change they made).

You can, you need to check RowState and you can get use the DataRowVersion
to tell what the original values were and what the current ones are.

Issue #7 -- There is no documentation on how the Fill and Update method
processes play together when executed against the same table from different
client applications concurrently. Consider the scenario from issue #1 and
attempt to define the specific data values that each of the three clients
will see on their screen.

It depends on the RDBMS. Remember you can use many different sources as an
Adapter and update an Excel Sheet, a CSV file, XML File, etc. Update fires
one row at a time and there's a row lock while that's happening

Issues #8 -- The existing classes and controls seem to be focused solely
upon multiple-record updates. There seems to be little attention paid to
scenarios where the change to each individual record must be processed
before the user can make changes to other records. I know that much of ..Net
is geared toward IIS.Net and Web Server applications, but many client/server
applications have traditionally dealt with single record processing, at
least in part to avoid some of the problems that are discussed above. I am
willing to convert to this 'batch' mode of processing, but I cannot see the
degree of control that I need.

I'm not sure I follow you here, could you explain a little more.

I apologize of some of the comments here are not crystal clear.

-ken

I answered everything at the 50,000 foot level, if you need elaboration,
please let me know.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp

 

[David Browne]

I have been reading a lot of material on the use of (specifically) the
SqlClient classes that are used to retrieve, display, edit, and update
database records. The basic approach seems to be:

ADO.NET has a bunch of built-in tools for using disconnected data access and
optimistic concurrency, but you can alway use RDBMS-specific features to to
enforce whatever concurrency strategy you want. By using transactions,
setting the isolation level, using locking hints and lock timeouts you can
prevent multiple users from trying to updating the same data.

David

 

[Ken Allen]

I have been reading a lot of material on the use of (specifically) the
SqlClient classes that are used to retrieve, display, edit, and update
database records. The basic approach seems to be:

1. Define a SqlConnection to the database.
2. Define a SqlDataAdapter along with a command for retrieving the data.
3. Set the SqlDataAdapter.MissingSchemaAction to AddWithKey to ensure
Primary Key information will be included.
4. Define a DataSet and use SqlDataAdapter.Fill to populate it.
5. Associate the DataSet (or DataSet.Tables[0]) with a DataGrid or some
other control(s).
6. Permit the user to edit the contents of the DataSet; rows may be added,
deleted or modified.
7. Create a new SqlDataAdapter for the same command used to populate the
original data; again set the MissingSchemaAction to be complete.
8. Either define a SqlCommandBuilder or define commands for the Insert,
Delete and Update members of the SqlDataAdapter.
9. Invoke the SqlDataAdapter.Update method to commit all changes to the
database.
10. Some have suggested then using SqlDataAdapter.Fill on the same DataSet
to merge changes made by other clients with the existing information.

There seem to be a significant number of flaws with this approach, and

it

is
not easy to find discussions or suggestions for alternatives or methods to
avoid these flaws. Of course, I may be missing something completely and not
understanding how this process is actually working.

I want to make it clear that I develop client and client/server
applications. I have never developed IIS, IIS.Net or Web Service
'applications'.

Issue #1 -- This mechanism, as with the vast majority of the information I
can find on these specific classes, seem to presume single user access to
the database and, for the most part, ignore the impact of concurrent updates
from multiple client applications into the same database. Presuming the
above process is used concurrently by multiple client applications, all
making changes to the same table, there will be no way to control which
changes are made to a specific record! Consider the case where three (3)
clients make changes to the same record, where two of the changes are to
different columns and the third change involves all of the columns from the
first two. The first client issues the Update and the changes are saved; the
second client issues an Update and that change is saved, effectively
reversing the change from the first client; when the third client commits
the changes then both of the first two changes are overwritten -- and none
of the clients are aware of this.

It depends on how you set up your update logic. In general you'll throw a
Concurrency exception unless you specifically code it not to.

I presume that you mean that the Update method will throw the concurrency
exception. Yes, I read this -- the documentation is so scatter/gather that
it is difficult to find all of the pertinent information from one
location -- but this treats the Update method call as a transaction, does it
not? Or does it only fail that record and all that follow? What is the state
of the DataSet contents if, say, the 10th modifed record fails because that
record was already modified by someone else?

Issue #2 -- The documentation indicates that repeating the
SqlDataAdapter.Fill method call on an existing DataSet will merge the
changes from the table into the DataSet, but this is only partially true --
it does not seem to deal with records that are in the DataSet but which have
been deleted from the database table (likely by another client). In

order

to
achieve this it seems one should issue a DatSet.Tables[0].Clear method call
before the SqlDataAdapter.Fill call to ensure that this happens, although
none of the documentation mentions this.

True, but if you change one of those values it will throw a concurrency
exception provided you coded your updates this way.

I am not certain what your answer means here. Why would I get a concurrency
exception on an Update/Clear/Fill sequence? This is the only way to fully
synchronize the DataSet with the database table, is it not?

Client client
You can trap the concurrency exception and then pop up something for the
user to choose what they want to happen or hard code a business rule to deal
with this. It takes a little forethought but it's quite flexible.

Where can I trap this concurrency exception and handle it? Must this be done
in the RowUpdated event handler? If I am in a client/server model, then
there is little opportunity for the server code to pop up individual dialogs
for each record that requries processing. I suspect that I need to extract
the 'problem' records from the original DataSet and return them as part of
the result of the Update process request from the user interface.

Your last comment is, as you mention later, part of a 50,000 foot view -- my
problem is that the available documentation is not making it clear how I can
go about solving the solving the issues, which makes it difficult to
understand the flexibility as I think about it.

If you bind to a DataView, you can set the RowStateFilter
http://www.knowdotnet.com/articles/dataviews1.html and accomodate this. I do
it all the time. You can see all the recrods, modified ones, deleted ones,
added ones..lot's of flexibility here and you only need one more line of
code to create the dataview

I can understand that I may be able to indicate which records the user has
modified via the client application, but once I issue the Update call it is
not completely clear whether or not I can have some records updated in the
database and the modified ones remain in the DataSet, especially if I want
to synchronize the DataSet with the table once the Update is complete. There
seems to be conflicting indications as to whether all changes within a
DataSet/DataTable must be processed as a transaction or not, and whether any
modified records can remain when the Update is complete -- since the
references indicate that AcceptChanges is implied, the latter does not seem
possible.

It depends on how the app is coded b/c you can have a more complicated
version on a single instance than a multi-user although they correlate very
highly. A LOT depends on design and how you handle things.

I agree with you last statement, but I am gaving difficulty understanding
how to leverage the power of the .Net facilities to make this work easier to
code. I can see how some of the facilities are powerful in simple cases, but
once one attempts to deal with anything other than the simplest of
scenarios, it becomes unclear how much effort C# and .Net will provide over
classical client/server programming before .Net -- I am beginning to suspect
that with respect to this specific topic the answer may be 'not much', but I
hope not.

You can, you need to check RowState and you can get use the DataRowVersion
to tell what the original values were and what the current ones are.

It is not clear whether the DataRowVersion.Current represents the current
value in the DataSet or the current value in the database table, but it
seems the former is intended. Before (or during in the case of the
RowUpdating event) the Update, the only information available seems to be
the original and new (proposed or current) value of each column, but not the
actual values in the database table. It is not clear what information is
available when the concurrency exception is thrown, or how this could be
used.

It depends on the RDBMS. Remember you can use many different sources as an
Adapter and update an Excel Sheet, a CSV file, XML File, etc. Update fires
one row at a time and there's a row lock while that's happening

Yes, this was exactly my point. Consider the case of using SQL Server -- the
more concurrent updates are occurring, the more likely one or more of the
updates with retrieve obsolete information from the database! There do not
seem to be any mechanisms defined for detecting changes to a table and
invoking a refresh on any associated DataSets.

I'm not sure I follow you here, could you explain a little more.

The .Net approach leans heavily in the direction of supporting disconnected
data activities and batch processing, as exemplified by the use of the
DataSet and SqlDataAdapter.Update processes. It is not readily apparrent
whether these facilities will be useful to me or not, especially in a
client/server architecture. If I permit the user to edit multiple records
and commit the changes in one step, then I neet a way to process those that
I can and somehow explain to the user which of the records could not be
updated -- possibly by showing the changes proposed by that user and the
most recent data from the database, and then permitting the user to modify
the most recent data; and of course this can be a recursive process.

I answered everything at the 50,000 foot level, if you need elaboration,
please let me know.

Yes, please. I am beginning to pull my hair out attempting to 'think' about
how to leverage the .Net facilities to implement my database access in a
manner that will provide a high level of control over concurrency. As I
hinted above, one of the larger problems that I have encountered in the past
is where a user makes changes to several records, and sometimes the changes
are more than a simple edit (may involve multiple edits and significant text
entry in more than one column), and when the update fails, they do not want
to have to re-enter all their changes again. In the past I have addressed
this by keeping and displaying their updated version of the record along
with the most recent from the database, permitting them to see the
differences, and permitting them to make changes to the new (most recent)
version using copy and paste or direct editing as appropriate. The user
interface issues are not a problem, but the server-side (or even in-process)
control over managing this when the information comes from an editable
DataGrid is considerably more complex!

 

[William Ryan eMVP]

My comments are inline but generally speaking, if you deal with small
datasets concurrency is less a problem. The more records you pull the more
issues you have. You can deal with just about any scenario that pops up but
if you use small or reasonably sized datasets It's hard to imagine this
being that much of a problem. In a web site, even in auctions, you don't
know if the udpate worked until it's submitted. Ok so you're client/server
oriented and this won't cut it. If the current framework won't work, then
use the connected model. Concurrency isn't the only issue here and locked
records were a big pain in the a55 in a lot of the apps I created. A lot
depends on how much users edit the same data simultaneously. Keeping record
sized small is key. Yes, there is some downside with ADO.NET in comparison
to old ADO. However I had this problem happen just today... I had an ADO app
open and was working on it. The DB went down and I lost my connection. All
of my editing was lost and it sucked. This wouldn't have happened with
ADO.NET if you coded for this.

So the longer you keep your data the more you run the risk of concurrency.
On the other hand, you could run your whole company all day with the DB
Server down from 8:05 to 4:55 for instance without a glitch. Compare this
to what would happen in ADO?

There's trade offs. If concurrency is your sole concern then I'd keep the
data fresh but this adds overhead obviously. The disconnected model works
in just about every environment out there. It's been used and banged on for
three years now and I have yet to hear a yearning for old ADO from even one
person who has taken the time to learn it properly. I've seen this concern
over and over when people are transitioning but it's totally easy to work
with. If this model works for web sites with tons of concurrent users it can
work for standard client server.

Also, if you submit an update after every edit for instance, it's hard to
imagine that you could have that many concurrency collissions. The timing
of updates is critical but it's very manageable.

Comments inline below..

I have been reading a lot of material on the use of (specifically) the
SqlClient classes that are used to retrieve, display, edit, and update
database records. The basic approach seems to be:

1. Define a SqlConnection to the database.
2. Define a SqlDataAdapter along with a command for retrieving the data.
3. Set the SqlDataAdapter.MissingSchemaAction to AddWithKey to ensure
Primary Key information will be included.
4. Define a DataSet and use SqlDataAdapter.Fill to populate it.
5. Associate the DataSet (or DataSet.Tables[0]) with a DataGrid or some
other control(s).
6. Permit the user to edit the contents of the DataSet; rows may be added,
deleted or modified.
7. Create a new SqlDataAdapter for the same command used to populate the
original data; again set the MissingSchemaAction to be complete.
8. Either define a SqlCommandBuilder or define commands for the Insert,
Delete and Update members of the SqlDataAdapter.
9. Invoke the SqlDataAdapter.Update method to commit all changes to the
database.
10. Some have suggested then using SqlDataAdapter.Fill on the same DataSet
to merge changes made by other clients with the existing information.

There seem to be a significant number of flaws with this approach, and

it

is
not easy to find discussions or suggestions for alternatives or

methods

to and
not

information

I from
the saved;
the
It depends on how you set up your update logic. In general you'll throw a
Concurrency exception unless you specifically code it not to.

I presume that you mean that the Update method will throw the concurrency
exception. Yes, I read this -- the documentation is so scatter/gather that
it is difficult to find all of the pertinent information from one
location --

David Sceppa's ADO.NET Core Reference has very detailed discussion of this.
So does Bill Vaughn's ADO & ADO.NET Best practices. The information both
provide give is superb.

but this treats the Update method call as a transaction, does it

not?

It depends but essentailly the answer is No. If 5 rows get successfully
updated and the sixth one fails, then 5 will be affected, the rest won't.
You can get around this by setting the ContinueUpdateOnError property of the
Adapter to true.
Or does it only fail that record and all that follow? What is the state

of the DataSet contents if, say, the 10th modifed record fails because that
record was already modified by someone else?

Again it depends on your update statement. YOu can blow it off, you can pop
up a messagebox asking the user to make a decision etc. In general, if it
blows up you'll lose the rowstate info.

Issue #2 -- The documentation indicates that repeating the
SqlDataAdapter.Fill method call on an existing DataSet will merge the
changes from the table into the DataSet, but this is only partially true --
it does not seem to deal with records that are in the DataSet but

which
have

been deleted from the database table (likely by another client). In

order

to
achieve this it seems one should issue a DatSet.Tables[0].Clear method call
before the SqlDataAdapter.Fill call to ensure that this happens, although
none of the documentation mentions this.

True, but if you change one of those values it will throw a concurrency
exception provided you coded your updates this way.

I am not certain what your answer means here. Why would I get a concurrency
exception on an Update/Clear/Fill sequence?

You'd get the exception on the update. If you called update and say the
rowstate was modified/added etc and the record didn't exist b/c someone had
already deleted it than it'd throw a concurrency excpetion if your update
logic was checking.
This is the only way to fully

synchronize the DataSet with the database table, is it not?
would This

Where can I trap this concurrency exception and handle it? Must this be done
in the RowUpdated event handler? If I am in a client/server model, then
there is little opportunity for the server code to pop up individual dialogs
for each record that requries processing. I suspect that I need to extract
the 'problem' records from the original DataSet and return them as part of
the result of the Update process request from the user interface.

That's a good place to do it.

Your last comment is, as you mention later, part of a 50,000 foot view -- my
problem is that the available documentation is not making it clear how I can
go about solving the solving the issues, which makes it difficult to
understand the flexibility as I think about it.

Those two books I recommend address each of these issues in depth.

I

I can understand that I may be able to indicate which records the user has
modified via the client application, but once I issue the Update call it is
not completely clear whether or not I can have some records updated in the
database and the modified ones remain in the DataSet, especially if I want
to synchronize the DataSet with the table once the Update is complete. There
seems to be conflicting indications as to whether all changes within a
DataSet/DataTable must be processed as a transaction or not, and whether any
modified records can remain when the Update is complete -- since the
references indicate that AcceptChanges is implied, the latter does not seem
possible.

You can certainly wrap it in a transaction if that's the functionality
needed.

in

I agree with you last statement, but I am gaving difficulty understanding
how to leverage the power of the .Net facilities to make this work easier to
code. I can see how some of the facilities are powerful in simple cases, but
once one attempts to deal with anything other than the simplest of
scenarios, it becomes unclear how much effort C# and .Net will provide over
classical client/server programming before .Net -- I am beginning to suspect
that with respect to this specific topic the answer may be 'not much', but I
hope not.

I find it sooooo much easier and more flexible that you couldn't pay me to
go back to the old way.

It is not clear whether the DataRowVersion.Current represents the current
value in the DataSet or the current value in the database table, but it
seems the former is intended. Before (or during in the case of the
RowUpdating event) the Update, the only information available seems to be
the original and new (proposed or current) value of each column, but not the
actual values in the database table. It is not clear what information is
available when the concurrency exception is thrown, or how this could be
used.

Right, it's the current value in the dataset. But this is checked against
the value in the db if you code it that way

Yes, this was exactly my point. Consider the case of using SQL Server -- the
more concurrent updates are occurring, the more likely one or more of the
updates with retrieve obsolete information from the database! There do not
seem to be any mechanisms defined for detecting changes to a table and
invoking a refresh on any associated DataSets.

In ADO.NET 2.0 they made some serious strides in this regards but it's not
great for tons of usage and that doesn no good right now. You can send the
changes to a MessageQueue and/Or use Notification services and poll for new
messages.

We have one system in place with somewhere between 20 and 300 concurrent
users depending on the time and day. All of the base functionality has been
more than enough to deal with concurrency. There are a lot of specialized
issues so I can't really hand you one approach but think of it this way.
Old School, I grab a record and you can't edit it. Ok, now, we both grab
the record, you edit and submit and you set your updates up to stop me from
updating. Same boat in the final analysis

 

[Ken Allen]

My comments are inline but generally speaking, if you deal with small
datasets concurrency is less a problem. The more records you pull the more
issues you have. You can deal with just about any scenario that pops up but
if you use small or reasonably sized datasets It's hard to imagine this
being that much of a problem. In a web site, even in auctions, you don't
know if the udpate worked until it's submitted. Ok so you're client/server
oriented and this won't cut it. If the current framework won't work, then
use the connected model. Concurrency isn't the only issue here and locked
records were a big pain in the a55 in a lot of the apps I created. A lot
depends on how much users edit the same data simultaneously. Keeping record
sized small is key. Yes, there is some downside with ADO.NET in comparison
to old ADO. However I had this problem happen just today... I had an ADO app
open and was working on it. The DB went down and I lost my connection. All
of my editing was lost and it sucked. This wouldn't have happened with
ADO.NET if you coded for this.

So the longer you keep your data the more you run the risk of concurrency.
On the other hand, you could run your whole company all day with the DB
Server down from 8:05 to 4:55 for instance without a glitch. Compare this
to what would happen in ADO?

There's trade offs. If concurrency is your sole concern then I'd keep the
data fresh but this adds overhead obviously. The disconnected model works
in just about every environment out there. It's been used and banged on for
three years now and I have yet to hear a yearning for old ADO from even one
person who has taken the time to learn it properly. I've seen this concern
over and over when people are transitioning but it's totally easy to work
with. If this model works for web sites with tons of concurrent users it can
work for standard client server.

Also, if you submit an update after every edit for instance, it's hard to
imagine that you could have that many concurrency collissions. The timing
of updates is critical but it's very manageable.

<snipped/>

Yes, you are basically confirming the opinion at which I have arrived -- the
ADO.Net facilities are primarily aimed at an optimistic approach based on
the expectation of a small number of columns and a relatively low
concurrency rate. This approach begins to become extremely difficult to
manage when the records contain a reasonable number of columns (consider a
simple address record, where one user might edit the phone and FAX info,
another the email info and another the postal info -- this could all be
split into separate tables, but normalized form does not require this since
they are all tightly coupled).

The other thing that bothers me is the extreme lack of information on how to
avoid some of the problems. For example, there is no indication of the
processing that happens if the ContinueUpdateOnError flag is set, other that
to indicate that processing does not stop on the offending row. It does not
indicate whether the RowUpdated event is fired or not, so it is not clear
how my code could determine which records were not properly processed, and
retain those records as the user modified them for further processing.

-ken