Re: Merging DataSet (Added and Unchanged)

Greg,

This is an alternative to using Merge, which (I hope) works better in the
remoted case.  The code (in a class that inherits from the typed DataSet)
is as follows:

/// <summary>
/// Adds records from each table in the source DataSet.
/// Similar to a Merge, but doesn't overwrite records with the same primary
key value.
/// </summary>
/// <param name="source"></param>
public void AddRecords(AnalysisDataSet source)
{
    bool constraintsEnabled = this.EnforceConstraints;
    this.EnforceConstraints = false;

    foreach (ProjectRow sourceRow in source.Project.Rows)
    {
        ProjectRow destRow = this.Project.NewProjectRow();
        CopyRow(sourceRow, destRow);
        this.Project.AddProjectRow(destRow);
    }

    foreach (AccountRow sourceRow in source.Account.Rows)
    {
        AccountRow destRow = this.Account.NewAccountRow();
        CopyRow(sourceRow, destRow);
        this.Account.AddAccountRow(destRow);
    }

    <etc>

    this.EnforceConstraints = constraintsEnabled;
}


private void CopyRow(DataRow source, DataRow dest)
{
    foreach(DataColumn dc in source.Table.Columns)
    {
        bool columnIsInKey = false;

        foreach (DataColumn primaryKeyCol in source.Table.PrimaryKey)
            if (primaryKeyCol == dc)
            {
                columnIsInKey = true;
                break;
            }

        if (columnIsInKey)
            // Update primary key in the source row, to trigger Cascade
Update rule
            // This should cause all of its child record foreign key fields
to be updated
            source[dc.ColumnName] = dest[dc.ColumnName];
        else
            dest[dc.ColumnName] = source[dc.ColumnName];
    }
}


Bill

On Thu, 26 Feb 2004 12:27:04 -0500, Greg Robinson <greg@xxxxxxxxxx> wrote:

>"I believe that temporarily turning off EnforceConstraints in the
>destination DataSet, then turning it back on after all of the tables have
>been copied, is also important."
>
>doesn't the call to Merge do this anyway?  Maybe I missed something in the
>thread.
>
>-----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 12:14 PM
>To: DOTNET-WINFORMS@xxxxxxxxxxxxxxxxxxx
>Subject: Re: [DOTNET-WINFORMS] Merging DataSet (Added and Unchanged)
>
>
>I believe I've expanded my solution to include related child records (with
>the help of ADO.NET).  The help came from the fact that DataRelations are
>defined in the source DataSet, with their Update rule set to Cascade (which
>is the default).
>
>I'm still using the general approach of creating a new record in the
>destination DataTable for each one in the source.  However, in addition to
>copying all of the non-PK fields from the source row to the destination
>row, I also now copy the PK fields from the destination row to the source
>row.  This should trigger the cascade update, and cause all of its child
>record foreign key fields to be updated.
>
>I believe that temporarily turning off EnforceConstraints in the
>destination DataSet, then turning it back on after all of the tables have
>been copied, is also important.
>
>Bill
>
>On Thu, 26 Feb 2004 09:11:02 -0500, Block, Jeffrey A. (Jeff)
><jeff.block@xxxxxxxxxx> wrote:
>
>>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
>>**********************************************************************