I'm a PHP programmer and I'm just now getting into C#. Here's the problem I'm having:
I have a table, 'categories', with 'catID', 'parentID' and 'name' fields. CatID is the primary key, and parentID is either 0 (no parent, top level) or an existing CatID.
With this setup, Categories can be nested infinitely deep, as many levels down as the user wishes.
In PHP, I would have just set up parentID as a foreign key referencing the CatID on delete cascade; however I've found that in C#, I'm not allowed to set up a foreign key constraint that self-references a column in the same table (lame!).
Does anyone know of a good way I could loop down through my table, as many levels and needed, in order to remove all children? For example if I delete a top-level category, I want to make sure all it's subcats are fully deleted, but if I'm already down 4 levels, I only want to delete the children of that current level. Make sense?
Oh and I'm using a local database (.sdf) not service-based db...
Any help appreciated!