DataAdapter.Update bug updating Child table via Relations / RowState corrupted?

  • Thread starter Thread starter Chris Bordeman
  • Start date Start date
C

Chris Bordeman

Hi all. .Net 2.0.

It seems the DataAdapter.Update Method changes the RowState of child records
to 'Modified' when updating them through a Relation.

If the child records' .RowState is "Added" or "Deleted," shouldn't they STAY
that way? Otherwise when you try to update the child table, you get
concurrency errors (record doesn't exist to update) or records fail to
delete.

Chris B.
 
Chirs,

I saw the same as Dave, to show he is not the only one.

Cor

"Chris Bordeman"
 
I'm too lazy now to work up an example project but here are some posts by
people having the same problem:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928845&SiteID=1

Here's how to reproduce:

Create a dataset, add 2 tables and a relationship with cascading updates
between the two and add some new rows to both tables. All rows will have a
RowStatus of 'Added'.

Use a dataadapter to update the parent table, with AcceptChangesDuringUpdate
set to false.

You'll see the RowStatus of all the rows in the child table are changed to
'Modified.'
 
Hi Chris,

I still can't reproduce the problem. I followed your specifications and
wrote the following code, which results in no errors, 3 records persisted in
the parent database table and all related child records having a RowState of
Added:

// Note: I tried with an auto-increment PK and with a string PK
// and both produced the same results

DataSet data = new DataSet();

DataTable parent = data.Tables.Add("ParentTable");
DataColumn pk = parent.Columns.Add("ParentID", typeof(int));
pk.AutoIncrement = true;
pk.AllowDBNull = false;
pk.ReadOnly = true;
parent.Constraints.Add("PK_ParentID", pk, true);
parent.Columns.Add("Text", typeof(string));

DataTable child = data.Tables.Add("ChildTable");
child.Columns.Add("ChildID", typeof(string));
child.Columns.Add("ParentID", typeof(int));

ForeignKeyConstraint foreignKeyConstraint =
(ForeignKeyConstraint) child.Constraints.Add("FK_Child_Parent",
parent.Columns[0], child.Columns[1]);

foreignKeyConstraint.UpdateRule = Rule.Cascade;

child.Rows.Add("C1",
parent.Rows.Add(null, "P1")["ParentID"]);
child.Rows.Add("C2",
parent.Rows.Add(null, "P2")["ParentID"]);
child.Rows.Add("C3",
parent.Rows.Add(null, "P3")["ParentID"]);

foreach (DataRow row in parent.Rows)
Debug.Assert(row.RowState == DataRowState.Added);

foreach (DataRow row in child.Rows)
Debug.Assert(row.RowState == DataRowState.Added);

using (SqlConnection connection = new SqlConnection(
Properties.Settings.Default.TestingConnectionString))
{
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
using (SqlCommand command = new SqlCommand(
@"INSERT ParentTable SELECT @Text;
SELECT SCOPE_IDENTITY();", connection))
{
command.Parameters.Add("@Text", SqlDbType.NVarChar, 50, "Text");

adapter.AcceptChangesDuringUpdate = false;
adapter.InsertCommand = command;
adapter.Update(parent); // parent is a DataTable
}
}
}

foreach (DataRow row in parent.Rows)
// check for Modified since pk is updated by adapter
Debug.Assert(row.RowState == DataRowState.Modified,
row["Text"].ToString() + ": Invalid parent RowState: " +
row.RowState.ToString());

foreach (DataRow row in child.Rows)
Debug.Assert(row.RowState == DataRowState.Added,
row["ChildID"].ToString() + ": Invalid child RowState: " +
row.RowState.ToString());

MessageBox.Show("Done!");
 
Hi,

"Chris Bordeman"
I'm too lazy now to work up an example project but here are some posts by
people having the same problem:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=928845&SiteID=1

Here's how to reproduce:

Create a dataset, add 2 tables and a relationship with cascading updates
between the two and add some new rows to both tables. All rows will have
a RowStatus of 'Added'.

Use a dataadapter to update the parent table, with
AcceptChangesDuringUpdate set to false.
Click to expand...

The DataAdapter will obviously use your InsertCommand foreach Added row, but
it does it like this:

-> RowState.Added
1) setup command/set parameter values ...etc...
2) execute InsertCommand
3) if the InsertCommand.UpdatedRowSource = FirstReturnedRecord
(which is quite normal if you use autonumber keys), then it will _always_
call AcceptChanges
-> RowState.Unmodified
4) load the values from FirstReturnedRecord into the DataRow
(this will set the new pk)
-> RowState.Modified
5) if AcceptChangesDuringUpdate = true, call AcceptChanges again
-> RowState.Unmodified

So, if you use an InsertCommand which retrieves the autonumber key, then
turning off AcceptChangesDuringUpdate will make the RowState go from Added
to Modified.

And if the ForeignKeyConstraint has AcceptRejectRule=Cascade then a similar
thing will happen for the related child rows: when AcceptChanges is called
on the parent row (3) it will cascade and call AcceptChanges on the child
row, changing its state to unmodifed, then when the new key is retrieved (4)
it will propagate the key to the child row (UpdateRule=Cascade), making the
child row enter a modified state.

Offcourse i don't know whether you are using AcceptRejectRule=Cascade but if
you are you should probely not, there are very few situations where this is
wanted.

HTH,
Greetings
 
Bart, I had the relation on Accept/Reject Cascade, set it to None and it
still didn't fix. But the following combination worked.

"Both Relation and Foreign Key constraint"
Accept/Reject rule to None
Uncheck "Nested Relation"

Thanks Bart.
 
Hi Chris,

A ForeignKeyConstraint with the UpdateRule set to Cascade shouldn't have any
effect, as I've already mentioned. Take a look at the code in my other
response and you'll see that I used Cascade just as you mentioned and the
RowState remains as Added for related child records.

You may want to post a short code example of the problem you are having if
my code doesn't help you.
 
Hi Chris,

I didn't read "AcceptRejectRule" - I just assumed "UpdateRule". My example
doesn't really apply.
 
Hi,

"Chris Bordeman"
Bart, I had the relation on Accept/Reject Cascade, set it to None and it
still didn't fix. But the following combination worked.

"Both Relation and Foreign Key constraint"
Accept/Reject rule to None
Uncheck "Nested Relation"
Click to expand...

AFAIK "Nested Relation" had nothing to do with it. "Nested Relation" only
applies to the xml representation of the DataSet (either WriteXML or
XmlDataDocument), if Nested is true, then the child elements will be nested
inside the parent elements.

HTH,
Greetings
 
Back
Top