Re: Query re ADO.NET, constraints, relations, and cascading updates/deletes
- From: "W.G. Ryan eMVP" <WilliamRyan@xxxxxxxxx>
- Date: Thu, 9 Jun 2005 13:30:55 -0400
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.
Cheers,
Bill
--
W.G. Ryan, MVP
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Frnak McKenney" <frnak@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:im_pe.1573$pa3.1278@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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"
> --
>
>
.
- Follow-Ups:
- Re: Query re ADO.NET, constraints, relations, and cascading updates/deletes
- From: Frnak McKenney
- Re: Query re ADO.NET, constraints, relations, and cascading updates/deletes
- Prev by Date: Re: Performance Advice
- Next by Date: Re: Bug retrieving output params and a recordset from a stored proc?
- Previous by thread: Re: Query re ADO.NET, constraints, relations, and cascading updates/deletes
- Next by thread: Re: Query re ADO.NET, constraints, relations, and cascading updates/deletes
- Index(es):
Relevant Pages
|
