How to Create hierarchical Menu from different table based on sample database structure

Question

I am working on a website with a menu structure so that it can read submenus from multiple table below is Sample Menu example

I have several table like pg_Pages, art_Article, art_Categories, Magazine each table has a FK PageID with PK in pg_Pages table.

I want to create a sql query which will read data from these tables and create a hierarchical Menu Structure with each link pointing to correct page.

I may need to pass several query string as part of href such as PageID, LanguageID, IssueID and CategoryID

Sample Link

Home = Default.aspx?LanguageId=1&IssueID=101&PageID=1

About Us = Page.aspx?LanguageId=1&IssueID=101&PageID=2

Categories = Article-Category.aspx?LanguageId=1&IssueID=101&PageID=6

--Politics = Article-Category.aspx?LanguageId=1&IssueID=101&PageID=6&CategoryID=1

--Economy= Article-Category.aspx?LanguageId=1&IssueID=101&PageID=6&CategoryID=2

--Business= Article-Category.aspx?LanguageId=1&IssueID=101&PageID=6&CategoryID=16

Multimedia = Multimedia.aspx?LanguageId=1&IssueID=101&PageID=10

--Video= Video.aspx?LanguageId=1&IssueID=101&PageID=11

I am not sure how handle this in best manner with no room for error, Since i have to pass different query-string to different menu i am bit confused how to handle this approach, should i create a multiple sql query with Union to handle this or something different. I need this for an asp.net website

I have also added sample sql query, & Output from each table

SQL

SELECT PageId AS ParentID,SUBSTRING(PageName,0,20) AS PARENT_MENU,SUBSTRING(PageInternalLinkURL,0,24) AS PageHandler, PageLinkPosition,SUBSTRING(PageLayoutPosition,0,14) AS MENU_Type,PageLangID,PageInheritance  FROM pg_Pages

SELECT p.PageID as ParentID, SUBSTRING(c.ArticleCategoryName,0,20) AS CHILD_MENU,SUBSTRING(p.PageInternalLinkURL,0,24) AS PageHandler,              ArticlePostion,ArticleID AS CHILDID,c.ArticleCategoryID,IssueID,LanguageID FROM art_Articles a JOIN art_Category  c ON a.ArticleCategoryID = c.ArticleCategoryID
JOIN pg_pages p ON p.PageID = a.PageID

SELECT p.PageID AS ParentID, IssueCode as CHILD_MENU,SUBSTRING(p.PageInternalLinkURL,0,24) AS PageHandler, m.IssueCode,IssueID AS CHILDID,CurrentIssue,IssueDate,LangID FROM Magazine m JOIN pg_pages p ON m.PageID = p.PageID 

SELECT p.PageID AS ParentID, SUBSTRING(c.ArticleCategoryName,0,20) AS CHILD_MENU,SUBSTRING(p.PageInternalLinkURL,0,24) AS PageHandler, LangID,ArticleCategoryID AS CHILDID FROM art_Category c JOIN pg_Pages p ON c.PageID = p.PageID 

OUTPUT

ParentID    PARENT_MENU          PageHandler              PageLinkPosition MENU_Type      PageLangID  PageInheritance
----------- -------------------- ------------------------ ---------------- -------------- ----------- ---------------
1           Home                 Default.aspx             10               TopMenu        1           0
2           About Us             Page.aspx                20               TopMenu        1           0
3           News                 News.aspx                30               TopMenu        1           0
4           Publication          Publication.aspx         40               TopMenu        1           0
5           Articles             Articles.aspx            20               TopMenu        1           0
6           Categories           Article-Category.aspx    25               TopMenu        1           0
10          Multimedia           Multimedia.aspx          60               TopMenu        1           0
11          Video                Videos.aspx              10               SubMenu        1           10
12          Archive              Default.aspx             40               TopMenu        1           0

ParentID    CHILD_MENU           PageHandler              ArticlePostion CHILDID     ArticleCategoryID IssueID     LanguageID
----------- -------------------- ------------------------ -------------- ----------- ----------------- ----------- -----------
5           Politics             Articles.aspx            10             12          1                 1           1
5           Politics             Articles.aspx            10             13          1                 3           1
5           Politics             Articles.aspx            10             14          1                 4           1
5           Politics             Articles.aspx            1              15          1                 5           1
5           Politics             Articles.aspx            20             16          1                 5           1
5           Business             Articles.aspx            30             17          16                5           1
5           Group News           Articles.aspx            40             18          6                 5           1
5           Infrastructure       Articles.aspx            50             23          17                5           1
5           Group News           Articles.aspx            60             24          6                 5           1
5           Book Review          Articles.aspx            70             25          18                5           1


ParentID    CHILD_MENU  PageHandler              IssueCode   CHILDID     CurrentIssue IssueDate               LangID
----------- ----------- ------------------------ ----------- ----------- ------------ ----------------------- -----------
12          106         Default.aspx             106         1           0            2012-09-01 00:00:00.000 1
12          106         Default.aspx             106         2           1            2012-09-01 00:00:00.000 2
12          102         Default.aspx             102         3           1            2011-11-01 00:00:00.000 1
12          103         Default.aspx             103         4           1            2012-02-01 00:00:00.000 1
12          109         Default.aspx             109         5           1            2012-12-01 00:00:00.000 1


ParentID    CHILD_MENU           PageHandler              LangID      CHILDID
----------- -------------------- ------------------------ ----------- -----------
6           Politics             Article-Category.aspx    1           1
6           Economy              Article-Category.aspx    1           2
6           Culture              Article-Category.aspx    1           3
6           Sports               Article-Category.aspx    1           4
6           xxxxxxxxxxxxxxxxxxx  Article-Category.aspx    1           5
6           Group News           Article-Category.aspx    1           6
6           People               Article-Category.aspx    1           7
6           Editorial Message    Article-Category.aspx    1           8
6           Chairman's Message   Article-Category.aspx    1           9
6           Business             Article-Category.aspx    1           16
6           Infrastructure       Article-Category.aspx    1           17
6           Book Review          Article-Category.aspx    1           18
6           Finance              Article-Category.aspx    1           19
6           Lifestyle            Article-Category.aspx    1           20
6           Others               Article-Category.aspx    NULL        21

Answer 1

as per your question....

-create a sql query by with clause and make a one single table with coming your all records. after that choose any html table menu ...in that on ul tag set main menu item(for that you can use data reader etc. control).it's easy way....

after that if you are not getting thn let me know...i made this type of menus....i will provide sample code....

Answer 2

There are many different ways to handle building menus.

• Build a stored procedure to do all the hard work.
• Select the data from the database, and build the strings in application code.
• Build a file from the database that you can include at "make" time.
• Do everything in a SQL query.

My least favorite option for things like menus is to do everything in a SQL query. If I were in your shoes, I think my first choice would be to build a file that I could include at "make" time. My second choice would be to select data from the database, and build the strings in application code.

Building a file to include at make time will perform better. It's easier to code in SQL, because the number of simple queries you need doesn't really matter. (It's done at "make" time, not at run time.) And it handles menus of arbitrary complexity with no run-time penalty. (Again, because it's done at make time.) But it might create maintenance issues, depending on how often the menus need to change.