Bind DropDownList with Hierarchy from SQL Server Table with ASP.NET

Question

I have the following sql table which contains menu (website menu) data.

Table Name: MenuItems
Columns: Id, MenuId, ParentMenuItemId, Text.

My goal is to bind a DDL according to the following hierarchy (example):

Id: 1, MenuId: 1, ParentMenuItemId: -1, Text: 'One'
Id: 2, MenuId: 1, ParentMenuItemId: 1, Text: 'Two'
Id: 3, MenuId: 1, ParentMenuItemId: 1, Text: 'Three'
Id: 4, MenuId: 1, ParentMenuItemId: 2, Text: 'Four'
Id: 5, MenuId: 1, ParentMenuItemId: 4, Text: 'Five'

Requested result in DDL:

One
-- Two
---- Four
------ Five
-- Three

I think it should contain 'WITH' SQL command.

Note: I'm using C#.

Answer 1

I didn't get the issue. are you facing problem in traversing a hierarchy using SQL command Or you issue is to show hierarchy in Dropdown??? if its the first case there are many stored procedures available to traverse hierarchies and if its the second case:

i don't think a normal dropdown can show multilevel hierarchy in it.. it can only support two level hierarchy(called grouping).. you better try something like treeview to show such data.

But if you really want a solution in dropdown try this: http://www.givainc.com/labs/mcdropdown_jquery_plugin.htm

Regards.