deleting from multiple tables

[Asgill]

hi

i would like to do deletion from multiple tables..for example i hv a table 1 where the primary key acct id is stored..now i hv two other tables(table 2 and table 3) in which the acct id is assigned the foreign key..i do know that the deletion must be done from the lowest level but how do i do it in C#..

i hv some psuedocode below...if its possible pls do tell...or else pls suggest me a way...


delete from table 3 where acct id = "textbox.text";

execute using dataAdapater;

delete from table 2 where acct id = "textbox.text";

execute using dataAdapter;

delete from table 1 where acct id = "textbox.text";

execute using DataAdapter;

is this possible....also how do i set up the dataset to have all these table in it...how do i fill it and clear it...im not so clear about the DATASET part...pls help

[Mike Hildner]

Not an answer, sorry, just a thought. Don't know what DB you're using, but if it's MSSQL 2000, that has a feature that allows cascade delete for foreign keys.

That is, you delete the parent row, and the DB takes care of the children.

[Asgill]

Well...now i wish i had use MSSQL2000 but its too late for that sighhhh....im using MS SQL 7.0....btw whats the feature called?..

[Mike Hildner]

I've never used 7, so not sure if that's a new feature or not. But if you're in enterprise manager and you go to your table and manage your relationships, you see check boxes for "Cascade Update Related Fields" and "Cascade Delete Related Fields".

These same steps, I'm sure, can be done through code, but I haven't tried that yet.

[Mike Hildner]

Just found out that cascade delete is a new feature in 2000, does not exist in 7.

[Asgill]

Well...got it to work by using the DataAdapter, added all the tables that was involved and deleted it one by one(like the psuedocode) but i had to use five different datasets, and had to call the CLEAR and FILL method to clear and fill each Dataset...looks dirty but it works!..thx anyways