Looping through multiple children??

[david.1982]

Hi all,

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!

[Rashakil Fol]

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!).

This doesn't make any sense. Constraints aren't specified in C#. What database are you using?

Edit: oh, I see. More reply pending.

Edit 2: One option: write a function that does what you say, deleting the children first.

void DeleteCategoryTree(int catID) {
    int[] children = GetChildIDs(catID);
    foreach(int child in children)
        DeleteCategoryTree(child);
    DeleteCategoryRow(catID);
}

Edit 3: And if you think there can be children so deep as to cause a stack overflow, use a Stack.

void DeleteCategoryTree(int catID) {
    var stack = new Stack<int>();
    var deletionStack = new Stack<int>();
    stack.Push(catID);
    while (stack.Count != 0) {
        int id = stack.Pop();
        deletionStack.Push(id);
        foreach (int child in GetChildIDs(id));
            stack.Push(child);
    }
    while (deletionStack.Count != 0) {
        DeleteCategoryRow(deletionStack.Pop());
    }
}

[david.1982]

But what if that child has children, and it's children have children? See my problem?

[Rashakil Fol]

But what if that child has children, and it's children have children? See my problem?

Read my function (now functions) again, you'll see that it deletes the children.

[david.1982]

Thanks!