Generate a menu with 3 stored procedures

I have the following query:

I have 3 stored procedures that give me information to generate the menu of a website, which are:

1st procedure (call it sp_menu)

This procedure generates timely information on a menu (can be more than one)

The 2nd procedure (call it sp_submenu)

This procedure generates information on the submenu of this menu (can be more than one)

3rd Procedure (I'll call sp_item)

This procedure generates information on the sub-menu items (may be more than one)

How I can do to automatically generate the ASP.net menu with the information you give?

The information delivery process is similar to this:

sp_menu

MENU_ID --- MENU_NAME --- MENU_ORDER

1 --- Menú 1 --- 1

2 --- Menú 2 --- 2

3 --- Menú 3 --- 3

sp_submenu

MENU_ID --- SUBMENU_ID --- SUBMENU_NAME

1 --- 1 --- Submenu 1

1 --- 2 --- Submenu 2

2 --- 1 --- Submenu 3

3 --- 1 --- Submenu 4

3 --- 2 --- Submenu 5

sp_item

MENU_ID --- SUBMENU_ID --- ITEM_ID --- ITEM_NAME --- ORDER_ITEM

1 --- 1 --- 1 --- Item 1

1 --- 1 --- 2 --- Item 2

1 --- 2 --- 1 --- Item 3

2 --- 1 --- 1 --- Item 4

2 --- 1 --- 2 --- Item 5

2 --- 1 --- 3 --- Item 6

3 --- 1 --- 1 --- Item 7

3 --- 1 --- 2 --- Item 8

3 --- 1 --- 3 --- Item 9

3 --- 2 --- 1 --- Item 10

This look like this:

Menú 1

--- Submenu 1

-------- Item 1

-------- Item 2

--- Submenu 2

-------- Item 3

Menú 2

--- Submenu 3

-------- Item 4

-------- Item 5

-------- Item 6

Menú 3

--- Submenu 4

-------- Item 7

-------- Item 8

-------- Item 9

--- Submenu 5

-------- Item 10

The menu is ul (unordered list), the submenu are li (list item) and item are ul (unordered list)

How do I get ASP.NET with VB (. NET 4.0) the order automatically?

Help me, please, will tha开发者_StackOverflownk you a lot!

First off, I wonder why you're using three different procs (and I assume three different tables) for this. A hierarchical table, such as:

(using ints instead of the preferred primary key type uniqueidentifier for simplicity's sake)

Test table:

create table menu_item
(
id int primary key 
,parent_id int foreign key references menu_item(id)
,[description] nvarchar(512)
,sort_order int not null default 0 
);

Populate the table with some test data

insert into menu_item (id, parent_id, [description],sort_order) 
select 0, null, 'Menu Root', 0
union all
select 1, 0, 'Item 1', 0 
union all 
select 2, 0, 'Item 2', 1
union all
select 3, 0, 'Item 3', 2
union all
select 4, 1, 'Item 1.1', 0
union all
select 5, 1, 'Item 1.2', 1
union all
select 6, 4, 'Item 1.1.1', 0
union all
select 7, 4, 'Item 1.1.2', 1
union all
select 8, 2, 'Item 2.1', 0

And the stored proc to retrieve the hierarchy in the correct order.

/*
Retrieve part of the menu hierarchy from table menu_item.
*/
create procedure menu_item_get_hierarchy
as
    set nocount on;

    with menu_hierarchy as
    (
        select id, parent_id, 0 as [level], cast('000000' as nvarchar(max)) as sort_key
        from menu_item m1
        where parent_id is null

        union all

        select m.id, m.parent_id, [level]+1, cast(sort_key + '.' + right('000000'+cast(m.sort_order as nvarchar(6)),6) as nvarchar(max))
        from menu_item m
        inner join menu_hierarchy h
        on m.parent_id = h.id
    )
    select  menu_item.[description], menu_hierarchy.*
    from menu_item inner join menu_hierarchy 
    on menu_item.id=menu_hierarchy.id
    order by sort_key, [description]

The only part about this that I don't like is using the string operators to generate the sort_path column as it's pretty inefficient, but I can't think of a better way to do that off of the top of my head, and the number of rows you're likely to have in a nav system shouldn't cause a performance issue. I'm sure someone will chime in with some insight or alternatives.

There are many other ways to store a heirarchy in a single table, but this one is the easiest to maintain as you only need to provide an parent id when you're inserting, and the foreign key relationship prevents orphaned nodes.

Hope this helps.

You do not expect the entire code, do you? If you show what you have tried so far, we can help you.

But I will give you start: you can get this to work with three nested repeaters.