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 HouseRecordThe 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.comhttp://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 835002http://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.comhttp://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 835002http://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.comhttp://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 835002http://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.comhttp://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