Query re ADO.NET, constraints, relations, and cascading updates/deletes

[Frnak McKenney]

One part of a customer project I'm working on involves what seem
like fairly straightforward updates to a set of related tables.
While I've developed software for a number of years (it only seems
like centuries on days like this <grin>), I'm new to C# and ADO.NET
and I'm running into problems with record deletions.

I (think I am) applying ForeignKeyConstraints correctly. I'm not
applying DataRelations (yet), but those _appear_ to be related to
record retrieval rather than record deletion. Yet my "Cascade"
Rules don't appear to be having the effect I want, that of deleting
child records relating to the parent (Employee) record I'm
attempting to delete.

My fallback is to do it myself: starting a transaction and then
"tree-walking" in my procedural code to delete child and grandchild
records. Is that my only choice? Or am I doing something obviously
wrong, omitting something critical, or simply performing the right
steps in the wrong order?

Any hints will be appreciated (including "it doesn't work in .NET
2003, wait for .NET 2005-or-RealSoonNow(tm)").

Rather than post a set of clips from about eight modules (so far),
let me lay out what I'm attempting to do and ask whether I seem to
at least be approaching the problem correctly.

----

Imagine I have a staff of employees, each with zero or more Phone
Numbers (office, home, cell, etc.) and zero or more Skills. Each
works on zero or more Projects, and each Project has zero or more
Deadlines:

Employees: Employee_ID (Primary Key, autoincrement),
Employee_Name, etc.

PhoneNumbers: Phone_Item_ID (Primary Key, autoincrement),
Employee_ID, Phone_Number, etc.

Skills: Skill_Item_ID (Primary Key, autoincrement),
Employee_ID, Skill_Description, etc.

Projects: Project_ID (Primary Key, autoincrement),
Employee_ID, Project_Name, etc.

Deadlines: Deadline_ID (Primary Key, autoincrement),
Project_ID, Deadline_Date, Deadline_Description,
etc.

The relationships are fairly straightforward (using [1:M] because I
can't find an "infinity" symbol on my keyboard):

Employees--[1:M on Employee_ID]--PhoneNumbers
Employees--[1:M on Employee_ID]--Skills
Employees--[1:M on Employee_ID]--Projects
Projects---[1:M on Project_ID]---Deadlines

I can load all of the tables from the database into a DataSet using
a set of OleDbDataAdapters, one per table. I can browse the
Employee table. But when I attempt to delete a row from the
Employee table, even my best attempts to date continue to yield an
error message equivalent to "The record cannot be deleted or changed
because table 'Skills' includes related records."

At startup, I load each table into my (one) DataSet 'dset':

// Set up the commands and TableMappings
da_em = new OleDbDataAdapter();
da_em.SelectCommand =
new OleDbCommand("SELECT * FROM Employees", conn);
cb_em = new OleDbCommandBuilder(da_em);
da_em.TableMappings.Add("Employees", "em");
(4 more similar sections of code, once for each remaining table)

// Fill the table Schema and Tables
da_em.FillSchema(dset, SchemaType.Mapped, "Employees");
da_em.Fill(dset, "Employees");
(4 more similar sections of code, once for each remaining table)

// Apply constraints
// Employees [1:M] Skills
fkc_em_sk = new ForeignKeyConstraint(
"Employees_OneToMany_Skills",
dset.Tables["em"].Columns["Employee_ID"],
dset.Tables["sk"].Columns["Employee_ID"]
);
fkc_em_sk.DeleteRule = Rule.Cascade;
fkc_em_sk.UpdateRule = Rule.Cascade;
fkc_em_sk.AcceptRejectRule = AcceptRejectRule.Cascade;
dset.Tables["sk"].Constraints.Add(fkc_em_sk);
(3 more similar sections of code, once for each remaining FK
constraint)

dset.EnforceConstraints = true;
conn.Close();


Later, when I'm attempting to delete an Employee record, I use the
following code:

// Following the Remarks section under "DataSet Class [C#]"...
// Issue Delete for current record
dset.Tables["em"].Rows[currentposition].Delete();
// Extract changes
DataTable dt = dset.Tables["em"].GetChanges();
if (dset.HasErrors) {
PgtCommon.PgtPopUpMsg("Errors in EditEmployee deletions");
}

// Use changes to update DataSet
**daem.Update(dt); // Apply this change via DataAdapter
dset.Merge(dt);
dset.Tables["em"].AcceptChanges(); // GONE

The ** line marks where the exception occurs.

For what it's worth, the deletion process works just fine when the
Employee records _don't_ have any child/grandchild records.

As I said, any clues will be appreciated.


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)
--
Literature exists for the ordinary educated man, and any
literature that actively requires enormous training can be at best
of only peripheral value. Moreover, such a mood in literature
produces the specialist who only knows about literature. The man
who only knows about literature does not know even about
literature. -- Robert Conquest, "The Dragons of Expectation"
--

 

[William \(Bill\) Vaughn]

We've discussed this before (if I understand what you're asking for). This
is the classic "parent/child" syndrome. Remember that ADO.NET won't
automatically manage maintenance on tables where there are parent/child
relationships in place. You'll have to delete any children first and then in
a separate operation, delete, add and change their parents. This second step
can also change existing children and add new ones.
I wrote an article that might help--"Managing an @@Identity Crisis" (see
www.betav.com/articles)

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

One part of a customer project I'm working on involves what seem
like fairly straightforward updates to a set of related tables.
While I've developed software for a number of years (it only seems
like centuries on days like this <grin>), I'm new to C# and ADO.NET
and I'm running into problems with record deletions.

I (think I am) applying ForeignKeyConstraints correctly. I'm not
applying DataRelations (yet), but those _appear_ to be related to
record retrieval rather than record deletion. Yet my "Cascade"
Rules don't appear to be having the effect I want, that of deleting
child records relating to the parent (Employee) record I'm
attempting to delete.

My fallback is to do it myself: starting a transaction and then
"tree-walking" in my procedural code to delete child and grandchild
records. Is that my only choice? Or am I doing something obviously
wrong, omitting something critical, or simply performing the right
steps in the wrong order?

Any hints will be appreciated (including "it doesn't work in .NET
2003, wait for .NET 2005-or-RealSoonNow(tm)").

Rather than post a set of clips from about eight modules (so far),
let me lay out what I'm attempting to do and ask whether I seem to
at least be approaching the problem correctly.

----

Imagine I have a staff of employees, each with zero or more Phone
Numbers (office, home, cell, etc.) and zero or more Skills. Each
works on zero or more Projects, and each Project has zero or more
Deadlines:

Employees: Employee_ID (Primary Key, autoincrement),
Employee_Name, etc.

PhoneNumbers: Phone_Item_ID (Primary Key, autoincrement),
Employee_ID, Phone_Number, etc.

Skills: Skill_Item_ID (Primary Key, autoincrement),
Employee_ID, Skill_Description, etc.

Projects: Project_ID (Primary Key, autoincrement),
Employee_ID, Project_Name, etc.

Deadlines: Deadline_ID (Primary Key, autoincrement),
Project_ID, Deadline_Date, Deadline_Description,
etc.

The relationships are fairly straightforward (using [1:M] because I
can't find an "infinity" symbol on my keyboard):

Employees--[1:M on Employee_ID]--PhoneNumbers
Employees--[1:M on Employee_ID]--Skills
Employees--[1:M on Employee_ID]--Projects
Projects---[1:M on Project_ID]---Deadlines

I can load all of the tables from the database into a DataSet using
a set of OleDbDataAdapters, one per table. I can browse the
Employee table. But when I attempt to delete a row from the
Employee table, even my best attempts to date continue to yield an
error message equivalent to "The record cannot be deleted or changed
because table 'Skills' includes related records."

At startup, I load each table into my (one) DataSet 'dset':

// Set up the commands and TableMappings
da_em = new OleDbDataAdapter();
da_em.SelectCommand =
new OleDbCommand("SELECT * FROM Employees", conn);
cb_em = new OleDbCommandBuilder(da_em);
da_em.TableMappings.Add("Employees", "em");
(4 more similar sections of code, once for each remaining table)

// Fill the table Schema and Tables
da_em.FillSchema(dset, SchemaType.Mapped, "Employees");
da_em.Fill(dset, "Employees");
(4 more similar sections of code, once for each remaining table)

// Apply constraints
// Employees [1:M] Skills
fkc_em_sk = new ForeignKeyConstraint(
"Employees_OneToMany_Skills",
dset.Tables["em"].Columns["Employee_ID"],
dset.Tables["sk"].Columns["Employee_ID"]
);
fkc_em_sk.DeleteRule = Rule.Cascade;
fkc_em_sk.UpdateRule = Rule.Cascade;
fkc_em_sk.AcceptRejectRule = AcceptRejectRule.Cascade;
dset.Tables["sk"].Constraints.Add(fkc_em_sk);
(3 more similar sections of code, once for each remaining FK
constraint)

dset.EnforceConstraints = true;
conn.Close();


Later, when I'm attempting to delete an Employee record, I use the
following code:

// Following the Remarks section under "DataSet Class [C#]"...
// Issue Delete for current record
dset.Tables["em"].Rows[currentposition].Delete();
// Extract changes
DataTable dt = dset.Tables["em"].GetChanges();
if (dset.HasErrors) {
PgtCommon.PgtPopUpMsg("Errors in EditEmployee deletions");
}

// Use changes to update DataSet
**daem.Update(dt); // Apply this change via DataAdapter
dset.Merge(dt);
dset.Tables["em"].AcceptChanges(); // GONE

The ** line marks where the exception occurs.

For what it's worth, the deletion process works just fine when the
Employee records _don't_ have any child/grandchild records.

As I said, any clues will be appreciated.


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)
--
Literature exists for the ordinary educated man, and any
literature that actively requires enormous training can be at best
of only peripheral value. Moreover, such a mood in literature
produces the specialist who only knows about literature. The man
who only knows about literature does not know even about
literature. -- Robert Conquest, "The Dragons of Expectation"

 

[W.G. Ryan eMVP]

Applying a DataRelation should fix it for you.

http://msdn.microsoft.com/library/d...tml/frlrfsystemdatadatarelationclasstopic.asp

The key point is "Relationships can also cascade various changes from the
parent DataRow to its child rows." but this should fix it for you.

Let me know if it doesn't.

Cheers,

Bill

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

One part of a customer project I'm working on involves what seem
like fairly straightforward updates to a set of related tables.
While I've developed software for a number of years (it only seems
like centuries on days like this <grin>), I'm new to C# and ADO.NET
and I'm running into problems with record deletions.

I (think I am) applying ForeignKeyConstraints correctly. I'm not
applying DataRelations (yet), but those _appear_ to be related to
record retrieval rather than record deletion. Yet my "Cascade"
Rules don't appear to be having the effect I want, that of deleting
child records relating to the parent (Employee) record I'm
attempting to delete.

My fallback is to do it myself: starting a transaction and then
"tree-walking" in my procedural code to delete child and grandchild
records. Is that my only choice? Or am I doing something obviously
wrong, omitting something critical, or simply performing the right
steps in the wrong order?

Any hints will be appreciated (including "it doesn't work in .NET
2003, wait for .NET 2005-or-RealSoonNow(tm)").

Rather than post a set of clips from about eight modules (so far),
let me lay out what I'm attempting to do and ask whether I seem to
at least be approaching the problem correctly.

----

Imagine I have a staff of employees, each with zero or more Phone
Numbers (office, home, cell, etc.) and zero or more Skills. Each
works on zero or more Projects, and each Project has zero or more
Deadlines:

Employees: Employee_ID (Primary Key, autoincrement),
Employee_Name, etc.

PhoneNumbers: Phone_Item_ID (Primary Key, autoincrement),
Employee_ID, Phone_Number, etc.

Skills: Skill_Item_ID (Primary Key, autoincrement),
Employee_ID, Skill_Description, etc.

Projects: Project_ID (Primary Key, autoincrement),
Employee_ID, Project_Name, etc.

Deadlines: Deadline_ID (Primary Key, autoincrement),
Project_ID, Deadline_Date, Deadline_Description,
etc.

The relationships are fairly straightforward (using [1:M] because I
can't find an "infinity" symbol on my keyboard):

Employees--[1:M on Employee_ID]--PhoneNumbers
Employees--[1:M on Employee_ID]--Skills
Employees--[1:M on Employee_ID]--Projects
Projects---[1:M on Project_ID]---Deadlines

I can load all of the tables from the database into a DataSet using
a set of OleDbDataAdapters, one per table. I can browse the
Employee table. But when I attempt to delete a row from the
Employee table, even my best attempts to date continue to yield an
error message equivalent to "The record cannot be deleted or changed
because table 'Skills' includes related records."

At startup, I load each table into my (one) DataSet 'dset':

// Set up the commands and TableMappings
da_em = new OleDbDataAdapter();
da_em.SelectCommand =
new OleDbCommand("SELECT * FROM Employees", conn);
cb_em = new OleDbCommandBuilder(da_em);
da_em.TableMappings.Add("Employees", "em");
(4 more similar sections of code, once for each remaining table)

// Fill the table Schema and Tables
da_em.FillSchema(dset, SchemaType.Mapped, "Employees");
da_em.Fill(dset, "Employees");
(4 more similar sections of code, once for each remaining table)

// Apply constraints
// Employees [1:M] Skills
fkc_em_sk = new ForeignKeyConstraint(
"Employees_OneToMany_Skills",
dset.Tables["em"].Columns["Employee_ID"],
dset.Tables["sk"].Columns["Employee_ID"]
);
fkc_em_sk.DeleteRule = Rule.Cascade;
fkc_em_sk.UpdateRule = Rule.Cascade;
fkc_em_sk.AcceptRejectRule = AcceptRejectRule.Cascade;
dset.Tables["sk"].Constraints.Add(fkc_em_sk);
(3 more similar sections of code, once for each remaining FK
constraint)

dset.EnforceConstraints = true;
conn.Close();


Later, when I'm attempting to delete an Employee record, I use the
following code:

// Following the Remarks section under "DataSet Class [C#]"...
// Issue Delete for current record
dset.Tables["em"].Rows[currentposition].Delete();
// Extract changes
DataTable dt = dset.Tables["em"].GetChanges();
if (dset.HasErrors) {
PgtCommon.PgtPopUpMsg("Errors in EditEmployee deletions");
}

// Use changes to update DataSet
**daem.Update(dt); // Apply this change via DataAdapter
dset.Merge(dt);
dset.Tables["em"].AcceptChanges(); // GONE

The ** line marks where the exception occurs.

For what it's worth, the deletion process works just fine when the
Employee records _don't_ have any child/grandchild records.

As I said, any clues will be appreciated.


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)
--
Literature exists for the ordinary educated man, and any
literature that actively requires enormous training can be at best
of only peripheral value. Moreover, such a mood in literature
produces the specialist who only knows about literature. The man
who only knows about literature does not know even about
literature. -- Robert Conquest, "The Dragons of Expectation"

 

[Frnak McKenney]

First, thank you for responding. My first four postings to the MS
"dotnet" groups received no reply... not even an insulting one. I
was beginning to worry that Earthlink's news server wasn't bothering
to propagate them. <grin>

We've discussed this before (if I understand what you're asking
for).

I don't recall running across it, but I've noticed that Google
Groups "beta" sems to return a _lot_ fewer hits than I recall
getting from its predecessor. Or maybe I just picked the wrong
keywords.

... This is the classic "parent/child" syndrome. Remember that
ADO.NET won't automatically manage maintenance on tables where there
are parent/child relationships in place.

Oooooo-kay. But then what am I to make of the following paragraph
from John Papa's "DataRelations in ADO.NET" article near your
"Managing an @@IDENTITY Crisis" at

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnadonet/html/manidcrisis.asp

"Support for cascading updates and deletes through foreign
keys is a new feature of SQL Server 2000. For example, if you
turn on these features in SQL Server 2000 and you delete the
parent row, the related child rows will also be deleted.

ADO.NET has a similar feature that can be controlled through
the DeleteRule and UpdateRule properties of the
ForeignKeyConstraint object. By default, these rules are set
to allow cascading modifications to the related data. So if
you change the value of CustomerID in the customer DataTable,
the value of CustomerID in the orders DataTable will be
updated accordingly."

What is the point of having the ForeignKeyConstraint UpdateRule and
DeleteRule properties if they're going to be ignored?

... You'll have to delete any
children first and then in a separate operation, delete, add and
change their parents.

I was afraid of that. Sigh.

I think I'm going to write one single routine that will manage _all_
updates so I can centralize the logic:

Step 1: Change anything
Step 2: UpdateAnyChangesAtAllToTheDataSet();

... This second step can also change existing
children and add new ones. I wrote an article that might
help--"Managing an @@Identity Crisis" (see www.betav.com/articles)

Yes. Thank you for the pointer -- I was able to find the article
with a bit of hunting. Your section on MSAccess/Jet will come in
very handy in the next few days.

Thank you especially for including a specific order for performing
Parent-Child updates.



Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

 

[Frnak McKenney]

Bill,

Thanks for taking the time to respond.

Applying a DataRelation should fix it for you.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/
cpref/html/frlrfsystemdatadatarelationclasstopic.asp

The key point is "Relationships can also cascade various changes
from the parent DataRow to its child rows." but this should fix it
for you.

Let me know if it doesn't.

Um. Er. It didn't. Here's a quick summary of my results:

Given a Parent-Child table relationship via unique keys,

1) You can set up a DataRelationship (per the page you mentioned).
-or-
2) You can set up a ForeignKeyConstraint

but trying to add both creates a "duplicate constraint" error.

John Papa's "DataRelations in ADO.NET" article explains why (click
on it in the TOC at the following URL):

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnadonet/html/manidcrisis.asp

Constraints and Cascading

Just like with databases, foreign key constraints in DataSet
objects help enforce data integrity. When I created the
Customer2Order DataRelation in Figure 3, it automatically
created a unique constraint on the parent table's key
(customer.customerid) and a foreign key constraint on the
child table (order.customerid). ...

This seems to be an interesting choice for Parent-Child tables
related by YeOldeAutonumberID fields. Both

fkc_pc = new ForeignKeyConstraint(
"ParentChild",
dset.Tables["Parent"].Columns["ParentID"],
dset.Tables["Child"].Columns["ParentID"]
);
dset.Tables["Child"].Constraints.Add(fkc_pc);

and

rel_pc = new DataRelation(
"ParentChild",
dset.Tables["Parent"].Columns["ParentID"],
dset.Tables["Child"].Columns["ParentID"]
);
dset.Relations.Add(rel_pc);

can be applied, but only a ForeignKeyConstraint() is documented as
supporting "cascading" record deletions (DataRelation() doesn't
offer any "Rule" options).

And neither actually seems to _perform_ cascaded record deletions.

Am I missing something obvious here where I need to set up my FKCs
_before_ I fill the DataSet or something similar? None of the
examples I've found seem to indicate that.

Anyway, thanks for your suggestions. Between your reply and Bill
Vaughn's I tried a lot of new things and uncovered a huge stock of
articles I hadn't run into before.

At this point I (reluctantly) admit that I've spent so much time on
the problem that the difference between "a bug" and "I'm too dumb to
figure it out" is too small to worry about. I'll go ahead with Mr.
Vaughn's "do it by hand" approach and worry about perfection later.

Thanks again...


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)