Creating hierarchical menu in asp.net using SQL

Question

I'm trying to use to create a hierarchical menu from a SQL data source in asp.net. I'm having trouble ordering my table so that I can easily create the menu in asp.net. There may be a better way to do it if anyone has any ideas...

I currently have a table that looks like this (made up sample data), there are only folders at the root and only folders can have folder Ids:

+-------------------+---------------------+-----------+-----------+
| Name_FolderorItem | Parent_Folder       | Id_Folder | Menuplace |
+-------------------+---------------------+-----------+-----------+
| c FOLDER          | ROOT                | c_FOLDER  | 1         |
| d FOLDER          | j_FOLDER            | d_FOLDER  | 2         |
| a FOLDER          | ROOT                | a_FOLDER  | 1         |
| j FOLDER          | ROOT                | j_FOLDER  | 1         |
| f FOLDER          | ROOT                | f_FOLDER  | 1         |
| r FOLDER          | f_FOLDER            | r_FOLDER  | 2         |
| i FOLDER          | d_FOLDER            | i_FOLDER  | 3         |
| a ITEM            | j_FOLDER            |           | 2         |
| d ITEM            | c_FOLDER            |           | 2         |
| z ITEM            | f_FOLDER            |           | 2         |
| r ITEM            | d_FOLDER            |           | 3         |
+-------------------+---------------------+-----------+-----------+

I'm thinking that If I order it to get this which is alphabetical on the first level then alphabetical on each deeper level:

+-------------------+---------------------+-----------+-----------+
| Name_FolderorItem | Parent_Folder       | Id_Folder | Menuplace |
+-------------------+---------------------+-----------+-----------+
| a FOLDER          | ROOT                | a_FOLDER  | 1         |
| c FOLDER          | ROOT                | c_FOLDER  | 1         |
| d ITEM            | c_FOLDER            |           | 2         |
| f FOLDER          | ROOT                | f_FOLDER  | 1         |
| r FOLDER          | f_FOLDER            | r_FOLDER  | 2         |
| z ITEM            | f_FOLDER            |           | 2         |
| j FOLDER          | ROOT                | j_FOLDER  | 1         |
| a ITEM            | j_FOLDER            |           | 2         |
| d FOLDER          | j_FOLDER            | d_FOLDER  | 2         |
| i FOLDER          | d_FOLDER            | i_FOLDER  | 3         |
| r ITEM            | d_FOLDER            |           | 3         |
+-------------------+---------------------+-----------+-----------+

Then I can use a listview to get this menu structure:

a FOLDER
c FOLDER
- d ITEM
f FOLDER
- r FOLDER (r FOLDER is located in f folder)
- z ITEM
j FOLDER
- a item
- d FOLDER
- - i FOLDER
- - r ITEM

I can't seem to figure out the required SQL to take a folder and then prioritise what is inside of it instead of the other folders/items on that level.

If you have any ideas on the SQL statements that would allow for this ordering I would appreciate it, thanks in advance

EDIT: Here is the Query that I'm now using, thanks for the help

SELECT *
FROM table
START WITH Parent_Folder LIKE 'ROOT'
CONNECT BY PRIOR Id_Folder LIKE Parent_folder;

kevin · Accepted Answer · 2014-03-04 15:38:08Z

1

This is basically the problem of storing an ordered tree in a database. I have tried a few approaches in the past and would recommend this one:

-----------------------------------
| NodeID |   ParentID  |  Order   |
-----------------------------------
| 1      |       -1    |     1    |
| 2      |        1    |     1    |
| 3      |        1    |     2    |
| 4      |        3    |     1    |
-----------------------------------

The GIST is each row corresponds to a node in the tree (or a menu item in your case), and it has a parentID that points to the parent. The root node either has null or -1 has its parentID since it does not have one. Now, to get all the nodes that are directly under the root, you'd use

SELECT * FROM table WHERE ParentID = -1 ORDER BY [Order]

[Order] tells you how to order the nodes in a directly under a specific parent node. (OK, it's not so good to name a column using a SQL keyword, but let's forget that for the moment). So in my example you can see both "2" and "3" are under the root, but "2" comes first. To select the nodes directly under "3" (its children in other words), you'd use

SELECT * FROM table WHERE ParentID = 3 ORDER BY [Order]

The advantage of this approach is it's easy to query the menu. The disadvantage is when you update, you have to make sure nodes that share the same ParentID (in other words under the same parent) do not share the name [Order].

Alternative approach

An alternative approach is to store the tree as something like a link list:

-----------------------------------
|NodeID |  ParentID  | PrevID     | (optionally you can store NextID as well)
-----------------------------------
| 1     |  -1        | -1         |
| 2     |   1        | -1         |
| 3     |   1        | 2          |
| 4     |  3         | -1         |
-----------------------------------

If your tree is very large and you need to quickly browse the sibling nodes, this approach would offer a better performance.

answered Mar 4, 2014 at 15:38

kevin

2,2071 gold badge21 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fabian Bigler Over a year ago

+1 maybe, you also want to mention hierarchical CTE for showing all children with one query en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL

user3220901 Over a year ago

Thanks to both you, I knew how to do it with individual queries before, but Fabian's had exactly what I needed i will edit my question with the answer if anyone needs it

kevin Over a year ago

I wonder though, if you need to display the entire tree at once, why would you store it in a database rather than a xml file? xml is by far easier to manage, it's just very slow if you have a very large tree (e.g. 1000+ nodes with a depth of at least 8).

Collectives™ on Stack Overflow

Creating hierarchical menu in asp.net using SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related