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 / [phone]
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"
--