Ruben,
I'm trying to delete a record in a parent/child relation, so,
when I want to delete a parent record, the childs will be deleted
automatically. Does anyone have an clear example of how to use
cascading deletes using ado.net in visual basic 2005? I didn't
find any example for this problem.
I'm not sure what you've tried so far, and what concepts make sense.
So, I'll try to provide a little background information.
First, you'll want to create a DataRelation between the related tables
in your DataSet, and make sure the DataRelation has a ForeignKeyConstraint.
The following code creates a DataRelation in code and automatically
associates the DataRelation with a ForeignKeyConstraint. DataRelations
created implicitly using the DataSet designer in Visual Studio 2005 does
not create a ForeignKeyConstraint.
Dim ds As New DataSet()
...
Dim rel As DataRelation
rel = ds.Relations.Add("Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID")
If you delete a DataRow and there's a ForeignKeyConstraint defined,
the ForeignKeyConstraint will cascade that change to the related child rows
if the ForeignKeyConstraint object's DeleteRule property is set to Cascade.
When you submit a pending change (insert, update or delete) via a
DataAdapter, the DataAdapter will implicitly call the DataRow's
AcceptChanges method if the database reports that the DataAdapter
successfully updated the corresponding row in the database. Calling
AcceptChanges marks the DataRow so it no longer contains pending changes.
There are two things that can happen when you submit a pending
deletion to a parent row to your database if there are child rows still in
the database.
1.) If the database's foreign key constraint that does not cascade pending
deletions, the deletion attempt fail due to the foreign key constraint. In
this scenario, your best bet in ADO.NET is to submit pending deletions for
child rows before pending parent deletions.
2.) If the database's foreign key constraint cascades pending deletions,
the database will delete the parent and child rows. The approach listed in
scenario #1 will still work, but there's another possibility that can save
a little bandwidth.
You can ask the ForeignKeyConstraint in the DataSet to cascade the
call to AcceptChanges when submitting deleted rows. The
ForeignKeyConstraint class has an AcceptRejectRule property that controls
whether or not calls to AcceptChanges and RejectChanges cascade down to
related child rows. However, you only want that call to cascade down for
pending deletions. There's no switch on the property to get this exact
behavior, but you can get the desired result by temporarily changing the
AcceptRejectRule just prior to submitting pending deletions.
You can use the following code to submit just the pending deletions
for a DataTable:
Dim rows As DataRow()
rows = ds.Tables("Customers").Select("", "", DataViewRowState.Deleted)
CustomersAdapter.Update(rows)
Now, you can use this code in conjunction with the AcceptRejectRule
property, using the following code:
rel.ChildKeyConstraint.AcceptRejectRule = AcceptRejectRule.Cascade
Dim rows As DataRow()
rows = ds.Tables("Customers").Select("", "", DataViewRowState.Deleted)
CustomersAdapter.Update(rows)
rel.ChildKeyConstraint.AcceptRejectRule = AcceptRejectRule.None
I hope this information proves helpful.
David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2006 Microsoft Corporation. All rights reserved.
