create dynamically menu and submenu from database in asp .net

Question

I want to display menu dynamically from database. But My menu items are displaying but when i used to click on menu,submenu is not displaying.please tell me where is the error

My code is

protected void Page_Load(object sender, EventArgs e)
{
    if(!IsPostBack)
    { GetMenuData(); }
}
private void GetMenuData()
{
    DataTable table = new DataTable();
    SqlConnection conn = new SqlConnection(@"Data Source=ST015\SQLEXPRESS;Initial Catalog=MyData;Integrated Security=True");
    string sql = "select menuId,Menuname,MenuDescription,ParentMenuid,MenuUrl from Categories";
    SqlCommand cmd = new SqlCommand(sql, conn);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(table);
    DataView view = new DataView(table);
    view.RowFilter = "ParentMenuId is NULL";
    foreach (DataRowView row in view)
    {
        MenuItem menuItem = new MenuItem(row["MenuName"].ToString(),
            row["MenuId"].ToString());
        menuItem.NavigateUrl = row["MenuUrl"].ToString();
        Menu1.Items.Add(menuItem);
        AddChildItems(table, menuItem);
    }
}
private void AddChildItems(DataTable table, MenuItem menuItem)
{
    DataView viewItem = new DataView(table);
    viewItem.RowFilter = "ParentMenuId=" + menuItem.Value;
    foreach (DataRowView childView in viewItem)
    {
        MenuItem childItem = new MenuItem(childView["MenuName"].ToString(),
            childView["MenuId"].ToString());
        childItem.NavigateUrl = childView["MenuUrl"].ToString();
        menuItem.ChildItems.Add(childItem);
        AddChildItems(table, childItem);
    }
}

Answer 1

Create a table for menu's

CREATE TABLE [dbo].[tblMenuMaster](
[MenuID] [int] IDENTITY(1,1) NOT NULL,
[MenuName] [varchar](100) NULL,
[DisplayOrder] [int] NULL,
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]
GO

Create another table for sub menu's

CREATE TABLE [dbo].[tblSubMenuMaster](
[SubMenuID] [int] IDENTITY(1,1) NOT NULL,
[MenuID] [int] NULL,
[SubMenuName] [varchar](100) NULL,
[MainMenuDisplayOrder] [int] NULL,
[DisplayOrder] [int] NULL,
[SubMenuUrl] [varchar](500) NULL,
[VisibleInMenu] [bit] NULL,
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]
GO

Now go to master page .. HTML code is:

   <div class="menubar">
        <%--<ul class="tabs">--%>
        <asp:Literal ID="ltMenus" runat="server"></asp:Literal>
        <%--</ul>--%>
    </div>

Code behind code is :

private void GenerateMenus()
{
    clsMenu obj = new clsMenu();
    System.Data.DataSet ds = new System.Data.DataSet();
    String PageName = "";
    PageName = Path.GetFileName(Page.AppRelativeVirtualPath);
    ds = obj.GetMenusByRole(GetRoleId(), PageName);

    StringBuilder sb = new StringBuilder("<ul class='tabs'>");

    foreach (System.Data.DataRow row in ds.Tables[0].Rows)
    {
        sb.Append(String.Format("<li class='{0}'><a rel='{1}' href='{1}' > {2} </a> ", Convert.ToString(row["css"]), ResolveUrl(Convert.ToString(row["PagePath"])), Convert.ToString(row["MenuName"])));
        //sb.Append(String.Format("<li '><a rel='{0}' href='{0}' > {1} </a> ", ResolveUrl(Convert.ToString(row["PagePath"])), Convert.ToString(row["MenuName"])));

        System.Data.DataTable t = CCMMUtility.GetFilterDataforIntColumn("MenuID", Convert.ToString(row["MenuID"]), ds.Tables[1]);
        if (t.Rows.Count > 0)
        {
            sb.Append("<ul>");

            for (int i = 0; i < t.Rows.Count; i++)
            {
                sb.Append(String.Format("<li><a href='{0}' class='dir' style='cursor: pointer;'>{1}</a></li>", ResolveUrl(Convert.ToString(t.Rows[i]["PagePath"])), Convert.ToString(t.Rows[i]["PageAliasName"])));
            }

            sb.Append("</ul>");
        }
        sb.Append("</li>");
    }

    sb.Append("</ul>");


    ltMenus.Text = sb.ToString();

}

it needs stored proc to call menu's dynamic according to Role id like below

CREATE PROCEDURE [dbo].[proc_GetMenusByRole]
(
@RoleId int,  
@PageName varchar(100)
)
AS
SET NOCOUNT ON;
SELECT mm.MenuID, mm.MenuName,dbo.Extract_CssNameForMenuByMenuIDAndPageName(mm.MenuID, @PageName) as css
,dbo.proc_Extract_MenuPageByRoleIDAndMenuID(@RoleId, mm.MenuID)
as PagePath , mm.DisplayOrder   FROM tblMenuMaster mm WHERE mm.MenuID IN (SELECT s.MenuID from tblSiteRolePermissions p INNER JOIN
tblSitePages s ON p.fkSitePageId = s.pkSitePageId
WHERE (p.fkRoleId = @RoleId and p.ViewOnly=1))   
Union All   
select 0 as menuid ,'Change Password' as MenuName,  
case @pagename   
when 'ChangePassword.aspx' then 'active'  
else ''  
end  as css,'~/User/ChangePassword.aspx' as PagePath, 10000 as Displayorder  
ORDER BY DisplayOrder     
SELECT s.MenuID, s.pkSitePageId, s.PageAliasName, s.SitePageName,s.pagepath from tblSiteRolePermissions p 
INNER JOIN tblSitePages s ON p.fkSitePageId = s.pkSitePageId  WHERE (p.fkRoleId =@RoleId and p.ViewOnly=1) ORDER BY s.pkSitePageId  

//new sp starts here

CREATE function [dbo].[Extract_CssNameForMenuByMenuIDAndPageName](@MenuID int, 
PageName varchar(100))
returns nvarchar(50)
as begin      
declare @result nvarchar(50) 
set @result = ''    
IF EXISTS (SELECT pkSitePageId FROM tblsitepages WHERE (MenuID = @MenuID) AND (UPPER(SitePageName) = @PageName)) 
 BEGIN    
  SET @result = 'active'    
 END    
return @result    
end  

// another sp used is

CREATE function [dbo].[proc_Extract_MenuPageByRoleIDAndMenuID]
(@RoleId int, @MenuID int)
returns nvarchar(500) 
as begin          
declare @result nvarchar(500)
SELECT top 1 @result = s.pagepath FROM tblSitePages AS s INNER JOIN tblSiteRolePermissions AS p ON s.pkSitePageId = p.fkSitePageId
WHERE (p.fkRoleId = @RoleId) AND (s.MenuID = @MenuID)  and p.ViewOnly=1
ORDER BY s.pkSitePageId 
return  @result
end  

Its just a way to do this you can modify this according to your requirement.........

proc_Extract_MenuPageByRoleIDAndMenuID sp is used to get Page name and its path,

Extract_CssNameForMenuByMenuIDAndPageName sp is used to set active class to the first li means first menu. Hop this will help you..... Its the working code..

Answer 2

I think when you are clicking on the menu items , it is causing the page to PostBack and you are creating the menus when the page is loading for the first time.Hence you are unable to see the menu on click of menu items.Just call the GetMenuData(); method on page load without checking for IsPostBack property.

protected void Page_Load(object sender, EventArgs e)
{
   GetMenuData(); 
}

Answer 3

There is no error in your code it is working well may be possible there is error in your data which you are adding to menu.May be "ParentMenuid" is missing or may be wrong