Iron Speed Technical Forums
Register Latest Topics
 
 
 


Reply
  Author   Comment  
rshope

Registered:
Posts: 93
Reply with quote  #1 
What would be the best way to delete rows from a table associated with the current record you're deleting? Let's say we have Table1 that lists the main objects, and Table2 has a list of associated objects, which link to the ID column to Table1. 

What would be the most efficient way to loop through the matching rows in Table2 and delete them before deleting the row in Table1? 
pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 956
Reply with quote  #2 

Do this at the database level with a "on cascade delete" relationship.

Deleting the parent row will then delete all the child rows.



__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting
rshope

Registered:
Posts: 93
Reply with quote  #3 
I had considered that, but I'm working with a database that has to be backwards-compatible with a bunch of old legacy code, so I think it might be best to put together a query that matches the column values and deletes the records in iteration some how.
porphi

Registered:
Posts: 647
Reply with quote  #4 
Rien,
I’ve had to work with legacy databases before that support several different frontends so I know your challenge.

In the Business Layer under app_code find the record class (TableNameRecord) then in that class delegate a new event for DeletedRecord.  In the DeletedRecord event put the code to remove child records. Now anywhere in the application the parent record is deleted the child record(s) will be removed also.

Code:

public class HouseRecord : BaseHouseRecord
{
    public HouseRecord()
    {
        this.DeletedRecord += HouseRecord_DeletedRecord;
    }
    void HouseRecord_DeletedRecord(object sender, EventArgs e)
    {
        // delete child data here
    }
  
} // End class HouseRecord


The other option at the page level is to override the commit transaction then check which button initiated the post back. If it’s the delete button execute the routine to remove the child record(s).
 

__________________
Thank you,
Phil Porter
PPG&A, INC.
pporter@ppgainc.com
http://www.ppgainc.com
https://rapidsprout.com
678-362-2035
timt

Avatar / Picture

Iron Speed MVP
Registered:
Posts: 556
Reply with quote  #5 
Hi

If there is a dependency you may need to delete the child records first, in that case you need to catch the deletingrecord event instead.

HTH

__________________
Tim Titchmarsh Iron Speed Consultant MVP
London UK based
+44 (0)1621 835002
http://www.lightspeeditsolutions.co.uk/dotnetarchitect
timt@dotnetarchitect.co.uk
timt@lsits.co.uk
porphi

Registered:
Posts: 647
Reply with quote  #6 

A few things to consider when choosing events; I chose deleted in case there was an issue deleting the parent record an exception would be thrown and all child record(s) would still be in place keeping the data integrity in place. The event deleting could be used as long as the child delete event were wrapped on the same transaction as the parent delete. If the parent delete fails then it’s all rolled back.  I’m not sure if the child delete transaction would automatically be wrapped in the parent transaction. If not it’s a trivial matter to get an instance of the parent delete transaction and execute the child delete within it even if you’re using custom stored procedures. Database level cascades is the best otherwise try to wrap the parent child delete in the same transaction.  Either way there are several options to consider.

Using the page level commit transaction the parent record would have already been deleted.

__________________
Thank you,
Phil Porter
PPG&A, INC.
pporter@ppgainc.com
http://www.ppgainc.com
https://rapidsprout.com
678-362-2035
timt

Avatar / Picture

Iron Speed MVP
Registered:
Posts: 556
Reply with quote  #7 
Hi Phil

When in these events the transactions are running so you are safe on that front but its a good point to ensure data integrity at all times. If any of the actions fail whilst in here they all roll back nicely.



__________________
Tim Titchmarsh Iron Speed Consultant MVP
London UK based
+44 (0)1621 835002
http://www.lightspeeditsolutions.co.uk/dotnetarchitect
timt@dotnetarchitect.co.uk
timt@lsits.co.uk
porphi

Registered:
Posts: 647
Reply with quote  #8 

Hello Tim,

                I don’t believe if a custom stored procedure is called during any of these events it will be automatically included in the current transaction.  In this situation a handle to the current transaction needs to be assigned when creating the command object.  This is what I was referring to. All of this is good information for the OP.

__________________
Thank you,
Phil Porter
PPG&A, INC.
pporter@ppgainc.com
http://www.ppgainc.com
https://rapidsprout.com
678-362-2035
timt

Avatar / Picture

Iron Speed MVP
Registered:
Posts: 556
Reply with quote  #9 
Hi Phil

If using your own sp's then you're right, youshould definitely be using the transaction context. This is a sample of some code I used to update the hireachy values in my db after grabbing the context.

 
Code:
        private void UpdateHierarachy()
        {
            // get the current transaction
            IDbConnection myConnection = SqlTransaction.GetExistingTransaction().GetADOConnectionByName("xxxManager"); // get the connection string key name from the web.config
            IDbTransaction idbt = SqlTransaction.GetExistingTransaction().GetADOTransaction(myConnection, false);
            
            // this is my own sp code
            DNADBUtils Db = new DNADBUtils("xxxManager");
            Db.ExecuteSPReturnScalar("AddOrganisationHierarchy", idbt, 60, "@OrganisationID", this.OrganisationID, "@ParentOrganisationID", this.ParentOrganisationIDSpecified ? (object)this.ParentOrganisationID : null);
 
        }
 

__________________
Tim Titchmarsh Iron Speed Consultant MVP
London UK based
+44 (0)1621 835002
http://www.lightspeeditsolutions.co.uk/dotnetarchitect
timt@dotnetarchitect.co.uk
timt@lsits.co.uk
porphi

Registered:
Posts: 647
Reply with quote  #10 
Tim,

     That would be it. Might also want to mention that the methods are found in BaseClasses.Data.SqlProvider.

Code:

IDbConnection cnn = BaseClasses.Data.SqlProvider.SqlTransaction.GetExistingTransaction().GetADOConnectionByName("DatabaseConnectionString");
IDbTransaction trans = BaseClasses.Data.SqlProvider.SqlTransaction.GetExistingTransaction().GetADOTransaction(cnn, false);

__________________
Thank you,
Phil Porter
PPG&A, INC.
pporter@ppgainc.com
http://www.ppgainc.com
https://rapidsprout.com
678-362-2035
JohnK

Registered:
Posts: 1
Reply with quote  #11 
I can't find where to initiate a new post, so will ask my question here. 

I'm having an odd occurrence: When I modify DDL's on my Edit page and click save, the data is persisted in the dB, and shows up in the ShowTable. And when I return to the Edit page, the data is bound to the controls. 

Here's the rub: when I use any type of redirect, Cancel button or clicking a menu option, the data in the DDL's is written to the dB as blank. Thus, the Show Table is blank for all those values.

I've looked all through the code behind, and I can't find what is causing this to occur. Any ideas out there?

Thank you so much!

Sincerely,

John
Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.

Download Iron Speed Designer

Terms of Service Privacy Statement