Cascading Deletes in ADO.NET (Please Help!!!)

  • Thread starter Thread starter rguarnieri
  • Start date Start date
R

rguarnieri

Hi !!

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.

Thank you.
Ruben
 
Ruben,

Cascade delete is usually a property of the table relationships in SQL
Server, rather than being driven by the client code.


HTH

Al
 
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.
Click to expand...

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.
 
Same approach applies Ruben:

In the Access design mode, select Tools > Relationships, then add the parent
and child tables.

Drag the parent tables Primary key on top of the child table's foreign key
version of the parent key to create the one - to - many relationship.

The relationship properties dialog will open. Check box to Enforce
Referential Integrity, then

Check box to enforce cascade delete related records.

Needless to say you will of course be providing very clear prompting to
users that this big delete action will be swinging it's thang around the
tables... http://www.granite.ab.ca/access/cascadeupdatedelete.htm

(Just someone else's opinion).

I do use cascade delete, but (hopefully!) with a very clear interface and
messages that minimise "pilot-error" problems.

HTH,

Al
 
Hi David!!

Thanks for your help, but I still have a problem, I send you what
I'm doing:

Dim daGroupItems As OleDbDataAdapter = New OleDbDataAdapter
Dim dsGroupItems As New DataSet

daGroupItems.SelectCommand = New OleDbCommand("select * from
alloy_group", dbConnection)
daGroupItems.Fill(dsGroupItems, "alloy_group")

'Pk Table alloy_group (Parent Table)
Dim pkGroup(0) As DataColumn
pkGroup(0) =
dsGroupItems.Tables("alloy_group").Columns("id_alloy_group")
dsGroupItems.Tables("alloy_group").PrimaryKey = pkGroup

'-----

daGroupItems.SelectCommand = New OleDbCommand("select * from
alloy_group_item", dbConnection)
daGroupItems.Fill(dsGroupItems, "alloy_group_item")

'Pk alloy_group_item (Child Table)
Dim pkItem(0) As DataColumn
pkItem(0) = dsGroupItems.Tables("alloy_group_item").Columns("cd_alloy")
dsGroupItems.Tables("alloy_group_item").PrimaryKey = pkItem

'Fk Column in the Child Table
Dim fkItem(0) As DataColumn
fkItem(0) =
dsGroupItems.Tables("alloy_group_item").Columns("id_alloy_group")

'Create DataRelation.
Dim relGroupItems As DataRelation
relGroupItems = New DataRelation("Group_Items", pkGroup, fkItem)

'Add the relation to the DataSet.
dsGroupItems.Relations.Add(relGroupItems)
relGroupItems.ChildKeyConstraint.DeleteRule = Rule.Cascade

'Delete the parent record
Dim tblItem As DataTable
tblItem = dsGroupItems.Tables("alloy_group")
Dim drCurrent As DataRow
drCurrent = tblItem.Rows.Find(CObj(objItem))
drCurrent.Delete()

Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(daGroupItems)
daGroupItems.Update(dsGroupItems, "alloy_group")
dsGroupItems.AcceptChanges()

The error is:
"Missing the DataColumn 'cd_alloy' in the DataTable 'alloy_group' for
the SourceColumn 'cd_alloy'"

I don't know what is the problem
Thank you very much.
 
Ruben,

If I understand your code correctly, the reason the call to
daGroupItems.Update fails is because that DataAdapter (and CommandBuilder)
at the time you call Update has "SELECT * FROM alloy_group_item" in its
SelectCommand. Your best bet is to use separate DataAdapters for each
DataTable in your DataSet.

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.
 
OK, I will try that, one more question, Do you think that my code is
the best way to do a cascade delete? or there is a better way to do
this.
Thank you very much.
Ruben
 
Back
Top