Database Driven Dynamic Menu Control

Introduction

This is another way for making database driven menus in ASP.NET with parent sub parent and sub parent child tables in database.

Background

Before going through this tip, you should have a basic understanding of how foreach loop works and how data can be retrieved from database.

Using the Code

First make tables as many as you want to make hierarchy, like Home->Item1->SubItem1 SubItem2, etc.

CREATE TABLE [dbo].[tblMenu](
[MenuId] [int] IDENTITY(1,1) NOT NULL,
[MenuName] [varchar](40) NOT NULL,
[Url] [varchar](100) NULL,
[ParentID] [int] NULL,
 CONSTRAINT [PK_tblMenu] PRIMARY KEY CLUSTERED 
(
[MenuId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, _
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] 

CREATE TABLE [dbo].[tblSubMenu](
[SubMenuId] [int] IDENTITY(1,1) NOT NULL,
[MenuId] [int] NULL,
[SubMenuName] [varchar](40) NULL,
[MenuUrl] [varchar](100) NULL,

 CONSTRAINT [PK_tblSubMenu] PRIMARY KEY CLUSTERED 
(
[SubMenuId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, _
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 
) ON [PRIMARY]

CREATE TABLE [dbo].[tblChildSubMenu](
[MenuId] [int] NULL,
[SubMenuId] [int] NULL,
[SubChildMenuId] [int] IDENTITY(1,1) NOT NULL,
[ChildMenuName] [varchar](40) NULL,
[MenuUrl] [varchar](100) NULL,
 CONSTRAINT [PK_tblChildSubMenu] PRIMARY KEY CLUSTERED 
(
[SubChildMenuId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, _
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 insert into tblMenu(MenuName,Url,ParentID) Values ('Home','http://www.google.com.pk',0)
insert into tblMenu(MenuName,Url,ParentID) Values ('MoreMenu','http://www.google.com.pk',0)
 
insert into tblSubMenu(MenuId,SubMenuName,MenuUrl) Values (2,'FB','http://www.facebook.com')
insert into tblSubMenu(MenuId,SubMenuName,MenuUrl) Values (2,'GMAIL','http://www.gmail.com')
insert into tblSubMenu(MenuId,SubMenuName,MenuUrl) Values (1,'NewSubMenuItem','http://www.hotmail.com')
 
insert into tblChildSubMenu(MenuID,SubMenuId,ChildMenuName,MenuURl) _
Values (2,2,'ChildSub2','http://www.google.com.pk') 

Copy and paste the above code step by step as it is.

Now get data of all tables like that.

select * from tblMenu
select * from tblSubMenu
select * from tblChildSubMenu  

As I mentioned above, you should have a strong understanding of data retrieval from database.

Suppose you have data in DataView like that:

DataView mm = new DataView(tblMenu);
DataView sm = new DataView(tblSubMenu;
DataView csm = new DataView(tblSubChildMenu);

On Designer drag and drop ASP.NET menu control, here mainMenu is the id of MenuControl.

foreach (DataRowView item in mainMen)
{
    MenuItem Mmnu = new MenuItem(item["MenuName"].ToString(),
    item["MenuId"].ToString(), "", item["Url"].ToString());
    mainMenu.Items.Add(Mmnu);
    subMenu.RowFilter = "MenuId = " + Mmnu.Value;
    foreach (DataRowView subItem in subMenu)
    {
        MenuItem sMnu = new MenuItem(subItem["SubMenuName"].ToString(), 
        subItem["SubMenuId"].ToString(), "", 
        subItem["MenuUrl"].ToString());
        mainMenu.Items[Convert.ToInt32(Mmnu.Value)-1].ChildItems.Add(sMnu);
        subChildMenu.RowFilter = "MenuId = " + Mmnu.Value + " 
        And SubMenuID = " + sMnu.Value;
        foreach (DataRowView childItem in subChildMenu)
        {
            MenuItem chldMnu = new MenuItem(childItem["ChildMenuName"].ToString(), 
            "","", childItem["MenuUrl"].ToString());
            mainMenu.Items[Convert.ToInt32(Mmnu.Value) - 1].ChildItems
            [Convert.ToInt32(sMnu.Value) - 1].ChildItems.Add(chldMnu);
        }
    }    
}