Re: Effective use of DataAdapter.Update(DataSet)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ken Allen (kendrhyd_at_sympatico.ca)
Date: 06/02/04 

Date: Wed, 2 Jun 2004 11:52:16 -0400

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

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

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.

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

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

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

> >
> > 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 ***, 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.

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

Quantcast