none
Why does DataTable.Rows.RemoveAt(int) cause child row RowState=Deleted ?????

    Question

  •  

    Hello

     

    I have a brief question following a strange observation I have made. I ahve been using typed datatables for a long time and am well used to most of their quirks but this one has me stumped.

     

    I have a dataset with many tables and relations inside. I'm using a new instance of it on my form.

     

    I am looking at two datatables, Customer and Bankee. A customer is-a bankee, so the relationship between them is 1:1 logically. Customers are not the only types of object that can be a Bankee so the datarelation is actually arranged such that Bankee is the parent. This point ought to be unimportant, as you will see:

     

    I fill the Customers table using a PK value, so my Customers table now contains one row.

    I retrieve the Bankee reference id and fill the Bankees table, and there are two Bankee records for this customer, but one is old (closed) so I loop the collection (backwards) looking for the closed flag and remove the row using:

     

    Bankees.Rows.RemoveAt(i) //i is the for-loop counter

     

    As soon as I effect a RemoveAt(i), the Customers row becomes rowstate Deleted

     

     

    Ordinarily, I guess why this would occur, because the datarelation between Customer and Bankee is an FK constraint that cascades, but there are two points I would like to raise:

     

    1) Why does a Remove, which is simply supposed to remove the local row, NOT DELETE IT, set children to be deleted? Removing a row, for some local reason of not wanting to see it, will hence cause data loss next time an update is performed. Had I called .Delete() on the row, then fine, go ahead and set all the children of the cascading FK DataRelation to Delete()d too.. but dont do that for Remove! Remove shouldnt delete anything! At worst, Remove() of a parent should Remove() the children, not mark them as deleted..

     

    2) More curiously, the very first thing I did on this form, in the constructor, was:

    myDataSet.Relations.Clear()

    myDataSet.EnforceConstraints = false

    So, I'm wondering why any action on a parent row had any consequence for a child row at all? There are no relations in existence and if there wyere they wouldnt be enforced.. SO why does the datatable still delete the children (when it shouldnt delete because we are removing)

     

    Thanks in advance for reading my post Smile

    Wednesday, May 28, 2008 6:09 PM

Answers

  • > are no relations in existence and if there wyere they wouldnt be enforced

     

    DataRelation and ForeignKeyConstraint are different.  Most likely you had both a DataRelation and a corersponding ForeignKeyConstraint in your DataSet.  You cleared the DataRelation, leaving the ForeignKeyConstraint still present.  Since the ForeignKeyConstraint is responsible for cascading, the cascading will still occur.  Note that setting EnforceConstraints = false does not stop cascading from occurring (however, FYI, DataTable.BeginLoadData/EndLoadData can be used to temporarily shut off cascades).

     

    > Remove shouldnt delete anything! At worst, Remove() of a parent should Remove() the children, not mark them as deleted..

     

    I was able to reproduce what you are seeing.  For cascade purposes Remove acts like a Delete.  This is somewhat unfortunate; you are probably going to have to add a step in your program where you first figure out all of the related rows that need to be removed before you remove any of them.


    In case anyone is interested, here is the code I used to reproduce this.

     

    Code Snippet

    DataSet ds = new DataSet();

    DataTable dt1 = ds.Tables.Add("Dt1");

    dt1.Columns.Add("ID", typeof(long));

    dt1.PrimaryKey = new DataColumn[] { dt1.Columns["ID"] };

    DataTable dt2 = ds.Tables.Add("Dt2");

    dt2.Columns.Add("ID", typeof(long));

    dt2.PrimaryKey = new DataColumn[] { dt2.Columns["ID"] };

    ds.Relations.Add(dt1.Columns["ID"], dt2.Columns["ID"]);

     

    DataRow parent = dt1.Rows.Add(1);

    DataRow child = dt2.Rows.Add(1);

     

    ds.AcceptChanges();

    dt1.Rows.RemoveAt(0);

     

    Console.WriteLine(dt1.Rows.Count);

    Console.WriteLine(dt2.Rows.Count);

    Console.WriteLine(parent.RowState);

    Console.WriteLine(child.RowState);

     

     

    Wednesday, May 28, 2008 11:56 PM
  • Hi,

    > Remove shouldnt delete anything!

    Remove is actually the same thing as Delete + AcceptChanges
    (look at the Remarks section of the documentation for the DataRowCollection.RemoveAt method). The reason that the child rows are left with RowState = Deleted is that your ForeignKeyConstraint cascades only the Delete part and not AcceptChanges.

    It is AcceptChanges that removes deleted rows from the DataTable. You should make sure AcceptChanges is called also for the deleted child rows. This will happen automatically if you set the Accept/Reject rule of your ForeignKeyConstraint to Cascade, or you can do it manually by adding a call to Customer.AcceptChanges() right after the loop that removes rows from Bankee.

    Hope this helps.

    Sunday, June 15, 2008 1:27 PM

All replies

  • > are no relations in existence and if there wyere they wouldnt be enforced

     

    DataRelation and ForeignKeyConstraint are different.  Most likely you had both a DataRelation and a corersponding ForeignKeyConstraint in your DataSet.  You cleared the DataRelation, leaving the ForeignKeyConstraint still present.  Since the ForeignKeyConstraint is responsible for cascading, the cascading will still occur.  Note that setting EnforceConstraints = false does not stop cascading from occurring (however, FYI, DataTable.BeginLoadData/EndLoadData can be used to temporarily shut off cascades).

     

    > Remove shouldnt delete anything! At worst, Remove() of a parent should Remove() the children, not mark them as deleted..

     

    I was able to reproduce what you are seeing.  For cascade purposes Remove acts like a Delete.  This is somewhat unfortunate; you are probably going to have to add a step in your program where you first figure out all of the related rows that need to be removed before you remove any of them.


    In case anyone is interested, here is the code I used to reproduce this.

     

    Code Snippet

    DataSet ds = new DataSet();

    DataTable dt1 = ds.Tables.Add("Dt1");

    dt1.Columns.Add("ID", typeof(long));

    dt1.PrimaryKey = new DataColumn[] { dt1.Columns["ID"] };

    DataTable dt2 = ds.Tables.Add("Dt2");

    dt2.Columns.Add("ID", typeof(long));

    dt2.PrimaryKey = new DataColumn[] { dt2.Columns["ID"] };

    ds.Relations.Add(dt1.Columns["ID"], dt2.Columns["ID"]);

     

    DataRow parent = dt1.Rows.Add(1);

    DataRow child = dt2.Rows.Add(1);

     

    ds.AcceptChanges();

    dt1.Rows.RemoveAt(0);

     

    Console.WriteLine(dt1.Rows.Count);

    Console.WriteLine(dt2.Rows.Count);

    Console.WriteLine(parent.RowState);

    Console.WriteLine(child.RowState);

     

     

    Wednesday, May 28, 2008 11:56 PM
  • Thanks for the helpful response. I'm puzzled about one thing; if a DataRelation is not a ForeignKeyConstraint, ought there not be a collection on the dataset that holds them, so they can be cleared, or is this code generated in some non-modifiable way and made part of the .Designer.cs ?

     

    Edit: Would have been faster to search the code myself. Seems FK constraints are stored in the .Constraints property of a DataTable, so i'll clear that too.. Presumably also, this code to do that wouldnt have been generated if the relations window had been set to Relation Only

     

    Nuisance.

     

    How do we go about reporting the "Remove causes Delete" as a possible bug to MS?

    Thursday, May 29, 2008 8:18 AM
  • Hi,

    > Remove shouldnt delete anything!

    Remove is actually the same thing as Delete + AcceptChanges
    (look at the Remarks section of the documentation for the DataRowCollection.RemoveAt method). The reason that the child rows are left with RowState = Deleted is that your ForeignKeyConstraint cascades only the Delete part and not AcceptChanges.

    It is AcceptChanges that removes deleted rows from the DataTable. You should make sure AcceptChanges is called also for the deleted child rows. This will happen automatically if you set the Accept/Reject rule of your ForeignKeyConstraint to Cascade, or you can do it manually by adding a call to Customer.AcceptChanges() right after the loop that removes rows from Bankee.

    Hope this helps.

    Sunday, June 15, 2008 1:27 PM