Re: Merging DataSet (Added and Unchanged)

" It would be nice if ADO.Net had an internal GUID or something that
would get
propogated with it from dataset to dataset and was actually used for the
merge reconciliation of inserted records.  Just a thought..."

there is... :)
it's public and it's the primary key field!
And it's even used for merge reconciliation...


Sorry, couldn't resist.
:D

-----Original Message-----
From: Discussion forum for developers using Windows Forms to build apps
and controls [mailto:DOTNET-WINFORMS@xxxxxxxxxxxxxxxxxxx] On Behalf Of
Block, Jeffrey A. (Jeff)
Sent: Thursday, February 26, 2004 9:50 AM
To: DOTNET-WINFORMS@xxxxxxxxxxxxxxxxxxx
Subject: Re: [DOTNET-WINFORMS] Merging DataSet (Added and Unchanged)

That was the path I was on, we effectively know that there "typically",
but
nothing is _always_, have a unique key in most tables that is comprised
of
one or more columns.  I was proposing exactly what you are saying, I
believe, in previous threads, e.g. changing the PK of both tables to the
actual "OrderNumber" not the ID field, since OrderNumber should be
unique in
the Orders table, for example, mergeing on that, then changing back to
the
ID PK field.  But most eluded that they were doing the "remove added
rows
before the merge" thing, so I haven't pursued it further.

It would be nice if ADO.Net had an internal GUID or something that would
get
propogated with it from dataset to dataset and was actually used for the
merge reconciliation of inserted records.  Just a thought...


Jeff Block


-----Original Message-----
From: Philip Sayers [mailto:psayers@xxxxxxxxxxxxxxxxx]
Sent: Thursday, February 26, 2004 9:41 AM
To: DOTNET-WINFORMS@xxxxxxxxxxxxxxxxxxx
Subject: Re: [DOTNET-WINFORMS] Merging DataSet (Added and Unchanged)

In the ado.net core reference book, there is an alternative method
covered.

It involves manipulating the table structure before submitting it to
your
database.

You add a new primary key to the table (removing the 'PrimaryKey'
attribute of the original key field), then since stored procedure
parameters
are mapped to column names, the PK fro the db is mapped to the correct
field
in the dataset, and also the new 'fake' key's in the new fake key column
are
used to merge the datasets together so 'Added'
rows match up properly.  Then you just remove the fake key column ,and
your
all set.

I haven't thought this one through too much, I'm not sure how it will
affect
related tables in the dataset.

Phil


-----Original Message-----
From: Discussion forum for developers using Windows Forms to build apps
and
controls [mailto:DOTNET-WINFORMS@xxxxxxxxxxxxxxxxxxx] On Behalf Of Greg
Robinson
Sent: Thursday, February 26, 2004 9:21 AM
To: DOTNET-WINFORMS@xxxxxxxxxxxxxxxxxxx
Subject: Re: [DOTNET-WINFORMS] Merging DataSet (Added and Unchanged)

We do not use the identity columns in sql server.  We build the key
ourselves in the insert sproc.

I know there are issues with sending large datasets across the wire so I
am
not a fan of this, however if it makes life easier back on the
client......





-----Original Message-----
From: Discussion forum for developers using Windows Forms to build apps
and
controls [mailto:DOTNET-WINFORMS@xxxxxxxxxxxxxxxxxxx]On Behalf Of Block,
Jeffrey A. (Jeff)
Sent: Thursday, February 26, 2004 9:11 AM
To: DOTNET-WINFORMS@xxxxxxxxxxxxxxxxxxx
Subject: Re: [DOTNET-WINFORMS] Merging DataSet (Added and Unchanged)


Well, I think it _does_ work, since the ID of -1 for the newly added row
after the DB update has a 10 (or whatever).  And, like I said, it can
(or so
I've read, but never experienced first-hand) propogate to related child
rows.

>From your previous posts and other threads, I think we're all having the
challenge with remoted datasets.  When we do something like .GetChanges
to
create a new dataset, send that over the wire and then try to merge the
results back, we get into trouble.  Seems like overkill, but maybe we
should
remote the entire dataset over the wire, do the update, and then remote
the
entire dataset back.  Now the client has the updated dataset from the DB
layer code!?!  No merge required, simply replace the current dataset
with
the returned one.  Of course, now you have broken data binding...or have
you?  I'll have to dig into this a little more.  Maybe this is the
answer?
Sounds like a bad approach when you change a minimal number of rows
relative
to the total number, but heh, its only bandwidth, right?

But, that said, the docs recommend the .GetChanges() method, but it
seems to
be broke, but only in the remoted case.


Jeff Block


-----Original Message-----
From: Greg Robinson [mailto:greg@xxxxxxxxxx]
Sent: Thursday, February 26, 2004 8:55 AM
To: DOTNET-WINFORMS@xxxxxxxxxxxxxxxxxxx
Subject: Re: [DOTNET-WINFORMS] Merging DataSet (Added and Unchanged)

My main concern is why do the docs recommend this approach when Adding
DataRows and it simply does not work.  There is no way to get this to
work
without manually doing something.  The PKs will never match up so why
even
suggest doing this in the docs?



-----Original Message-----
From: Discussion forum for developers using Windows Forms to build apps
and
controls [mailto:DOTNET-WINFORMS@xxxxxxxxxxxxxxxxxxx]On Behalf Of Bill
Schmidt
Sent: Thursday, February 26, 2004 8:41 AM
To: DOTNET-WINFORMS@xxxxxxxxxxxxxxxxxxx
Subject: Re: [DOTNET-WINFORMS] Merging DataSet (Added and Unchanged)


Jeff,

Actually, my bug fix was more comprehensive than I described:  I turned
off
EnforceConstraints, copied the rows for every table in our typed DataSet
(which has relationships), then turned on EnforceConstraints (which
would
generate an error if any relationships are violated).  I then tested it
for
a case where only a single record was returned.

Thanks to your posting, I now realize that my 'fix' won't work in the
case
where related records (in different tables) are returned.  I guess its
back
to the drawing board.  I appreciate learning about a bug in our system
before the testers discover it ;)  I'll post again if I come up with
anything worth mentioning.

Bill

On Thu, 26 Feb 2004 06:40:32 -0500, Block, Jeffrey A. (Jeff)
<jeff.block@xxxxxxxxxx> wrote:

>I've been watching this thread with great interest...I too feel that
>the synchronizing of the DB and client is cumbersome for added rows.
>The GUID approach is viable and eliminates most of the challenges that
>we are
seeing,
>I believe.  Personally, I just like the "feel" of that sequential
>numbering of the identity/autoincrement column.
>
>Just last night, as I was reviewing some other system challenges due to
>the latest virus, I caught the fact that one of our PCs had a bad date
>setting (due to some _other_ 3rd party software, grrrrrr), that was
>putting 2/4/2023, instead of 2/23/2004, but was able to quickly
>discount them as having nothing to do to the current issues since the
>ID column was out of "sequence" with the current rows being added.  I
>don't think this would
have
>not been possible with GUIDs.
>
>So, I would really like to stay away from the GUID, if possible, but
>more from a comfort standpoint than anything technical.  That said,
>there is a lot more overhead in the tables from a GUID perspective than
>an int[32] column since they would just be stored as a char(32)
>(haven't really looked at the length, could be off, but I think the
>point comes across...) in the main and referencing tables.
>
>With the current means of removing the added rows, I would assume that
>you are not able to create any child rows of that added row, correct?
>One of the big selling points is that you would be able to add say an
>order and
all
>its items into the Orders and OrderItems DataTables and then just
>perform the update and the keys get cascaded (I believe there is a
>property that controls this, but is escapes me currently, but it can be
>done from the reading I've done.) properly.  Unless you don't have the
>relationship created or have EnforceConstrants = false (which sort of
>defeats the
purpose
>of the DataSet), it seems that you would not be able to perform this
>type
of
>operation without first "inserting" the order row into the DB and
>getting the real ID returned and updated as has been discussed.
><shrug>
>
>It is just this remoted case that has me, and I think most others,
stumped.
>Are we missing something or are the workarounds and kludgy feelings
>necessary.  Is there anything being worked on for Whidbey to address
>this (i.e. ObjectSpaces?!?).
>
>Jeff Block
>
>
>-----Original Message-----
>From: Bill Schmidt [mailto:billschmidt2@xxxxxxx]
>Sent: Thursday, February 26, 2004 12:28 AM
>To: DOTNET-WINFORMS@xxxxxxxxxxxxxxxxxxx
>Subject: Re: [DOTNET-WINFORMS] Merging DataSet (Added and Unchanged)
>
>Greg,
>
>I just fixed a bug today having to do with this subject.  We have a
>remoted

>method that passes back a DataSet with a DataTable containing a single
>(new) row.  Since this is a typed DataSet with AutoIncrement turned on
>for the primary key columns, the PK for the returned row is always -1.
>
>Prior to the bug fix, we were merging the returned DataSet into our
>master DataSet on the client.  This works fine the first time, but the
>second and subsequent times it overwrites the row merged in on the
previous
execution.
>The fix was to write a routine that adds a new (empty) row and copies
>all
of
>the non-PK fields from the returned row, instead of doing the Merge.
>
>Bill
>
>On Wed, 25 Feb 2004 09:57:15 -0500, Greg Robinson <greg@xxxxxxxxxx>
wrote:
>
>>The docs state
>>
>>"When merging a new source DataSet into the target, any source rows
>>with a DataRowState value of Unchanged, Modified, or Deleted, are
>>matched to
>target
>>rows with the same primary key values. Source rows with a DataRowState
>value
>>of Added are matched to new target rows with the same primary key
>>values as the new source rows."
>>
>>This part:
>>
>>"Source rows with a DataRowState value of Added are matched to new
>>target rows with the same primary key values as the new source rows."
>>
>>h
>>
>>as always confused me.  Our app sends an Added DataRow to our DAL (we
>>call the client Dataset's GetChanges method and send this smaller
>>DataSet across the wire).
>>
>>Our DAL does the insert.  A primary key is returned as part of the
insert.
>>The DataRow now has the PrimaryKey value and is now marked as
Unchanged.
>>The client 'Added' DataRow does not have the PrimaryKey though.  It is
>>storing a dummy PrimaryKey.
>>
>>So, we return to the client with an Unchanged DataRow that is storing
>>the correct PrimaryKey from sql server.
>>
>>So, how can this 'match' work?  The Source DataRow is marked as Added.
>>However, the returning DataRow's PrimaryKey will never match.
>>
>>What we do now, is just prior to calling Merge, we remove all Added
>DataRows
>>from the client DataSet.  The we call Merge.
>>
>>This works, but seems like extra overhead to me.
>>
>>Greg Robinson
>>Custom Data Systems, Inc.
>>www.cds-am.net
>
>
>**********************************************************************
>PLEASE NOTE: The above email address has recently changed from a
>previous
naming standard -- if this does not match your records, please update
them
to use this new name in future email addressed to this individual.
>
>This message and any attachments are intended for the individual or
>entity named above. If you are not the intended recipient, please do
>not forward, copy, print, use or disclose this communication to others;
>also please notify the sender by replying to this message, and then
>delete it from your system.
>
>The Timken Company
>**********************************************************************


**********************************************************************
PLEASE NOTE: The above email address has recently changed from a
previous
naming standard -- if this does not match your records, please update
them
to use this new name in future email addressed to this individual.

This message and any attachments are intended for the individual or
entity
named above. If you are not the intended recipient, please do not
forward,
copy, print, use or disclose this communication to others; also please
notify the sender by replying to this message, and then delete it from
your
system.

The Timken Company
**********************************************************************


**********************************************************************
PLEASE NOTE: The above email address has recently changed from a
previous naming standard -- if this does not match your records, please
update them to use this new name in future email addressed to this
individual.

This message and any attachments are intended for the
individual or entity named above. If you are not the intended
recipient, please do not forward, copy, print, use or disclose this
communication to others; also please notify the sender by
replying to this message, and then delete it from your system.

The Timken Company
**********************************************************************