locked
Logic Flaw in CopyToDataTable? RRS feed

  • Question

  • I am seeing something in CopyToDataTable that strikes me as a bit of a logic problem.

     

    First, calling:

     

        dataRow.Field<string>("SomeColumn", DataRowVersion.Original)

     

    when a field has no original value throws an exception.  Calling AcceptChanges() on the DataTable that the DataRow is in causes a snapshot to occur of the original versions alleviating that problem.  Of course when you call the AcceptChanges() method, the current version of each field becomes the original version, and is still the current version too.

     

    So, I have an already populated DataTable (destination), and I want to copy a DataTable (source) from another DataSet into my already populated DataTable and specify the LoadOption.Upsert parameter.  This says to update the current value only (I assume) for records with a matching primary key.  I say assume because the help that came with VS Orcas Beta1 doesn' t actually define what they mean that I can find, but I am going by the definitions from the May 2006 LINQ CTP for LoadSequence.

     

    So, the copy occurs, but there is a problem.  Since I am going to the trouble of specifying a LoadOption, odds are good that I am interested in original version and current version information.  If a record was copied from the source DataTable into the destination DataTable that did not already exist, meaning the DataRow gets appended, when I try to access the original version for the field, an exception is thrown because that DataRow was not in the DataTable when AcceptChanges() was called.  So, the code blows up if I don't call AcceptChanges() on the destination DataTable again.  However, if I call AcceptChanges() on the destination DataTable, all the current versions become original versions for all DataRows alleviating the exception, but now I have lost all the original versions for any fields that were actually changed.

     

    It seems as though there is an imcompatability here.  I hope I have made that clear enough.

     

    Thanks.
    Thursday, May 31, 2007 5:27 AM

Answers

  •  Joe Rattz wrote:

    Imagine a situation where when you call the CopyToDataTable method, some rows will be inserted while others will be updated.  After calling CopyToDataTable, how do you access a row's original value for any particular column?  If the row was inserted, trying to access its original version will throw an exception unless you first call the AcceptChanges method.  But if you call the AcceptChanges method, you just lost the original version for any record that was updated.

     

    Does that make sense?

     

    Joe, seeing this last description of the problem, I think the problem you are facing might not be an issue with CopyToDataTable, but more of an issue on your use of original versions. Here is a reference that is .net framework1.1, but I am pretty sure it applies: http://msdn2.microsoft.com/en-us/library/ww3k31w0(VS.71).aspx

     

    Take a look at the table that lists information about the row versions.

    Original The original values for the row. This row version does not exist for rows with a RowState of Added.

     

    It isn't supposed to be valid when it is an added record.

     

    Edit:The Orca's version: http://msdn2.microsoft.com/en-us/library/ww3k31w0(vs.90).aspx

    Also in the reference:

    You can test whether a DataRow has a particular row version by calling the HasVersion method and passing a DataRowVersion as an argument. For example, DataRow.HasVersion(DataRowVersion.Original) will return false for newly added rows before AcceptChanges has been called.

    Wednesday, August 15, 2007 2:31 AM

All replies

  • This behavior has been changed in Beta 2 and at an initial glance, appears to be working as I would expect.  No one from MS ever responded to this post.  Can someone in the know about this explain what was changed and how it is now supposed to work?  Just want to make sure this was intentional and will not be going away.  ;-)

     

    Thanks.

     

    Sunday, August 5, 2007 11:26 PM
  • No, there is still something wrong here.  CopyToDataTable is behaving a little differently, but there still seems to be a logic problem.

     

    It appears that when calling the CopyToDataTable operator that has no arguments, it's as though the AcceptChanges method is called for you automatically because an original version for each field is established...at least provides a value instead of throwing an exception.  So this prototype of the operator seems improved to me.

     

    But with the prototype of the CopyToDataTable operator that accepts a destination DataTable and LoadOption there is still a problem.  After calling the CopyToDataTable operator say with a LoadOption value of Upsert, if you don't call AcceptChanges and there are any inserted rows, an exception will be thrown if you attempt to access any field's original version.  If you call the AcceptChanges method, you just lost the original version of the already existing rows.  This seems to me to be a problem.

     

    If doing an upsert causes some existing rows to be modified and some new rows to be inserted, I either can't access the original version without throwing an exception, or I lose my original version, depending on whether I call AcceptChanges.

     

    Thanks.

     

    Sunday, August 5, 2007 11:49 PM
  • Joe,

     

    The Upsert is a combination of Insert and Update, so that if you have existing DataRows (matched by PK), then the data will updated, else the new data will be inserted. You definitely will end up with a mix of updated and new rows.

     

    Can you over the exact sequence of operations that you're seeing the logic problem with? It almost seems like what you are expecting is that all the versions of the DataRows would be copied in a CopyToDataTable operation with the Upsert causes an insert.

     

    Thanks,

    Erick

    Monday, August 13, 2007 11:37 PM
  • Imagine a situation where when you call the CopyToDataTable method, some rows will be inserted while others will be updated.  After calling CopyToDataTable, how do you access a row's original value for any particular column?  If the row was inserted, trying to access its original version will throw an exception unless you first call the AcceptChanges method.  But if you call the AcceptChanges method, you just lost the original version for any record that was updated.

     

    Does that make sense?

    Wednesday, August 15, 2007 1:29 AM
  •  Joe Rattz wrote:

    Imagine a situation where when you call the CopyToDataTable method, some rows will be inserted while others will be updated.  After calling CopyToDataTable, how do you access a row's original value for any particular column?  If the row was inserted, trying to access its original version will throw an exception unless you first call the AcceptChanges method.  But if you call the AcceptChanges method, you just lost the original version for any record that was updated.

     

    Does that make sense?

     

    Joe, seeing this last description of the problem, I think the problem you are facing might not be an issue with CopyToDataTable, but more of an issue on your use of original versions. Here is a reference that is .net framework1.1, but I am pretty sure it applies: http://msdn2.microsoft.com/en-us/library/ww3k31w0(VS.71).aspx

     

    Take a look at the table that lists information about the row versions.

    Original The original values for the row. This row version does not exist for rows with a RowState of Added.

     

    It isn't supposed to be valid when it is an added record.

     

    Edit:The Orca's version: http://msdn2.microsoft.com/en-us/library/ww3k31w0(vs.90).aspx

    Also in the reference:

    You can test whether a DataRow has a particular row version by calling the HasVersion method and passing a DataRowVersion as an argument. For example, DataRow.HasVersion(DataRowVersion.Original) will return false for newly added rows before AcceptChanges has been called.

    Wednesday, August 15, 2007 2:31 AM
  • Joe,

     

    That does make sense. However, that is an expected side effect of the upsert operation. That operation is a mix of insert/update, and the RowVersions available reflect that. If you need to get at the original values for updated records, I would suggest using a LINQ to DataSet query to find the DataRows with an original version (via the HasVersion method).

     

    Thanks,

    Erick

     

    Wednesday, August 15, 2007 6:01 PM
  • Thanks.  I was missing the HasVersion method.

     

    Thursday, August 16, 2007 1:56 AM