Deleting related entries

Author

Comment

rshope

Registered:1423751622
Posts: 93

Posted 1424269820	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

MVP Consultant
Registered:1191322079
Posts: 963

Posted 1424270248	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:1423751622
Posts: 93

Posted 1424272378	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:1126097590
Posts: 648

Posted 1424293871 · Edited	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

Iron Speed MVP
Registered:1123345706
Posts: 556

Posted 1424613559	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:1126097590
Posts: 648

Posted 1424625253	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

Iron Speed MVP
Registered:1123345706
Posts: 556

Posted 1424713144	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:1126097590
Posts: 648

Posted 1424716876	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

Iron Speed MVP
Registered:1123345706
Posts: 556

Posted 1424764609	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:1126097590
Posts: 648

Posted 1424781740 · Edited	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:1495380524
Posts: 1

Posted 1495632422	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