Building a database driven menu with ASP.NET, JQue

Question

I'm attempting at creating a menu from a table using the Suckerfish css menu and Jquery. I'm using this as my reference: Suckerfish menu with ASP.NET and JQuery and I have it working with manually supplied links (much like in the article).

Where I'm having issues is writing the recursive function to get the menu items from the database and create the new menu items in the proper hierarchy. My database table looks like so:

Table Menu

---

MenuID ParentID Link Text

The idea being that if an item is a parent-level item the MenuID and ParentID are the same, if it's a child it will have the MenuID of it's parent in the ParentID field. I'm needing to create a function that can go through and find all of the children for the parents (could be a few levels) and have it replace manual entries like this:

        Dim Foo As New MenuItem("#", "Foo", Me)
        Items.Add(Foo)
        Foo.Items.Add(New MenuItem("#", "1", Me))
        Foo.Items.Add(New MenuItem("#", "2", Me))
        Foo.Items.Add(New MenuItem("#", "3", Me))
        Foo.Items.Add(New MenuItem("#", "4", Me))

I'm open to changing the database table structure if necessary and basically doing anything else to get this going.

Thanks for any input, it's much appreciated.

Answer 1

That method of representing hierarchical data is easy to understand for humans but difficult to extract data from, because it requires recursion to extract the full hierarchy. Some flavors of SQL have commands that will do this for you, but that is what is going on behind the scenes.

I suggest you read More Trees & Hierarchies in SQL, and restructure your schema using the materialized path method that it explains. It is easy to query against and scales really well.