Deleting children - how!?

jas_nhs posted on Wednesday, June 13, 2007

I can't believe that I have got this far in CSLA before coming across this problem - simply, I get the normal FK exception when deleting an editable root object without deleting the child collection first.

One option is to use cascade deletion in the database, but this is not straightforward as the design does not allow direct deletion of records, rather, a trigger does the deleting and so to delete a record, you set a deletion flag to true (in the trigger recs are copied to an audit table etc, so this design is fixed). So I assume this rules out simple cascade deletion.

The other option is to Clear() the collection or loop through and remove the children. The problem here is that the deletion does not actually take place - rather, the child is marked for deletion. Fine, except that the delete function, DataPortal_Delete, for the root object DOES perform the deletion directly (via a call to ExecuteDelete) - leaving the children unattached, hence FK exception.

Now, I imagine that my code is wrong to be deleting the root object so directly under its Delete() method, if so, what should I do instead?  Mark the object for deletion with actual deletion taking place upon the call to Save() (how?) - but then how and when is the deletion physically done (i.e. when is ExecuteDelete called)?



Hope someone can help, for the general good!

James.
BTW - using V2.0.3 (I think)

Bowman74 replied on Wednesday, June 13, 2007

What I personally do for direct deletion is that my delete stored procs also delete all children (and all their children, etc) first.  That way only one call to the database is made for a direct delete.  While this may seem like a maintenance PITA for the stored procs (they need to know about all data in objects below them), all mine are generated anyway so I don't even notice. 

For deletion through the Save method my stored procs do the same thing though they take a timestamp as a parameter for concurrency (concurrency is ignored for any children/grandchildren).  Then the update/Insert/Delete dataportal calls are not cascaded into any of the deleted object's children because it is not necessary, as everything has been deleted and no child of a deleted parent is valid to save.

I'm sure you will hear other ways to handle this, but this is my method.  It works and it performs well.

Thanks,

Kevin

JoeFallon1 replied on Wednesday, June 13, 2007

Typically when you delete a Root with children you are given the PK of the Root.
Based on this, I code gen DP_Delete like this:

Protected Overrides Sub DataPortal_Delete(ByVal criteria As Object)
  'determine criteria here
  Dim strSQL As String
  strSQL = DAO.Delete(crit.Key)

  Dim tr As IDbTransaction = Nothing

  Try
    tr = Me.BeginTransaction()
   
    'Delete child objects before deleting the parent.
    DeleteChildren(tr, criteria)

    'delete the parent
    DAL.ExecuteNonQuery(tr, CommandType.Text, strSQL)

    PostDeleteData(tr)

    Me.EndTransaction(tr)

  Catch ex As Exception
    Me.RollbackTransaction(tr)
    Throw
  End Try
End Sub

Protected Overridable Sub DeleteChildren(ByVal tr As IDbTransaction, ByVal criteria As Object)
  'marker method that can be overridden in child class
End Sub

Protected Overridable Sub PostDeleteData(ByVal tr As IDbTransaction)
  'marker method that can be overridden in child class
End Sub

Then in my hand written classes I can override DeleteChildren and use the Root PK value to help with the coding.

I also have the option of overriding PostDeleteData to take action once the delete has occurred.

Joe

jas_nhs replied on Wednesday, June 13, 2007

Both replies much appreciated!

To keep things going for now, I just loop through all children (there are 5 levels of descendents from the root) and do .Clear() at each level, then MyRootObject.Save() and then do MyRootObject.Delete() and MyRootObject.Save(). A stop-gap measure.

Kevin: I am disinclined to implicate the object heirarchy in the SP code, but still, the single db call has advantages.

Joe: in which class is your code, above?  presumably you do the physical deletion in DeleteChildren,  rather than via MyRootObject.Save() ?

Thanks

James.

Bowman74 replied on Wednesday, June 13, 2007

James,

I understand.  I too was disinclined to do the same thing for a long time due to:

1) Complexity of the delete and fetch procs knowing about their children/grandchildren/great grandchildren, etc.  Hard to read, hard to maintain.
2) What if I want to reuse a class in a different hierarchy?

What I noticed over time is:

1)  That I generated my classes and stored procs almost exclusively.  Mostly any overrides were made to business logic.  Very rarely did I ever override a stored proc.  In addition, once I had the generator stabilized, I almost never looked at the darn things; they just worked.
2) I never took a class and moved it around from one hierarchy to another.  Outside of issues around it being a questionable design practice, I just never had a need to do it.
3) On a complex object hierarchy I could find myself easily making 100 calls to the DB to do a fetch or delete operation.  It just seemed so unneeded and wasteful.

In the end I converted the generator to make centralized fetch and delete procs.  You're not really coding the object hierarchy into the stored procs, just recognizing the data key relationships that you are probably already enforcing due to referential integrity anyway.  If I could figure out a simple and understandable method to send all the data for an object hierarchy to the insert and update procs, I would do those the same way too.  But alas, not yet. 

No, I have no place to serialize it all into XML to tear it apart on the other side or storing it as XML in SQL Server 2005's XML data type so I can do single update/insert procs.  Not yet anyway.

Your mileage may vary.

Thanks,

Kevin

JoeFallon1 replied on Wednesday, June 13, 2007

Joe: in which class is your code, above?  presumably you do the physical deletion in DeleteChildren,  rather than via MyRootObject.Save() ?

Thanks

James.

====================================================================

The code is in the Root BO.

DeleteChildren method runs code like:

Dim parentkey As Integer = crit.Key

ExecuteNonQuery(tr, CommandType.Text, MyChildSQL.DeleteByParentKey(parentkey ))

Joe