William
Wed Jun 02 12:23:29 CDT 2004
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..
"Ken Allen" <kendrhyd@sympatico.ca> wrote in message
news:%231ohLmLSEHA.1764@TK2MSFTNGP10.phx.gbl...
> "William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
> news:uljYHvKSEHA.1348@TK2MSFTNGP12.phx.gbl...
> >
> >
> > "Ken Allen" <kendrhyd@sympatico.ca> wrote in message
> > news:Ox6b3eJSEHA.3300@TK2MSFTNGP09.phx.gbl...
> > > 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 --
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?
>
> > >
> > > 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.
>
> 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.
>
> > >
> > > 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
>
> 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.
>
> > >
> > > 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.
>
> 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.
>
> > >
> > > 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.
>
> 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
>
> > >
> > > 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
>
> 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
>
> > >
> > > 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.
>
> 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 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.
>
> 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!
>
> > >
> > >
> >
> >
> > --
> >
> > 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
> >
> >
>
>