asp menu

Table structure for creating the Menu

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MainMenu](
 [MenuID] [int] NOT NULL,
 [ParentMenuID] [int] NULL,
 [LabelMenu] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [LinkMenu] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [TargetMenu] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [HighlightMenu] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_MainMenu] PRIMARY KEY CLUSTERED 
(
 [MenuID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Identity Column' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'MenuID'

GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of the menu.' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'LabelMenu'

GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Url for the label' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'LinkMenu'

GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'Main menu' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MainMenu', @level2type=N'COLUMN', @level2name=N'HighlightMenu'

Query to insert records

INSERT INTO dbo.MainMenu
SELECT 1,NULL,'Home,'#,'_self','1' UNION ALL
SELECT 2,NULL,'Accessories,'#,'_self','2' UNION ALL
SELECT 3,NULL,'Products,'#,'_self','3' UNION ALL
SELECT 4,NULL,'Contact,'#,'_self','4' UNION ALL
SELECT 5,2,'Accessory1,'#,'_self',NULL UNION ALL
SELECT 6,3,'Product1,'#,'_self',NULL UNION ALL
SELECT 7,3,'Product2,'#,'_self',NULL UNION ALL
SELECT 8,5,'Accessory2,'#,'_self',NULL UNION ALL
SELECT 9,5,'Accessory3,'#,'_self',NULL UNION ALL
SELECT 10,8,'Acc,'#,'_self',NULL

Query to generate Insert statements

SELECT 'SELECT ' + CAST(MenuID AS VARCHAR) +',' + COALESCE(CAST(ParentMenuID AS VARCHAR), 'NULL')+ ',''' + LabelMenu + ','''+ LinkMenu + ','''+ TargetMenu + ''','+ (CASE WHEN HighlightMenu IS NULL OR HighlightMenu = '' THEN 'NULL' ELSE +''''+ HighlightMenu +'''' END) + ' UNION ALL' FROM dbo.MainMenu

C# Code

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            PopulateMenu();
        }
    }

    private void PopulateMenu()
    {
        //Get the menu structure from the table
        DataSet dsMenu = GetDataSetForMenu();

        foreach (DataRow drDataRow in dsMenu.Tables["Menu"].Rows)
        {
            //If the ParentMenuID is NullOrEmpty then it is the top most menu
            if (string.IsNullOrEmpty(Convert.ToString(drDataRow["ParentMenuID"])))
            {
                MenuItem mainMenuItem = new MenuItem((string)drDataRow["LabelMenu"], Convert.ToString(drDataRow["MenuID"]));
                Menu1.Items.Add(mainMenuItem);
                //CreateSubMenu(dsMenu, drDataRow, mainMenuItem);
            }
            else //Otherwise it is the submenu
            {
                CreateSubMenu(dsMenu, drDataRow, new MenuItem());
            }
        }
    }

    private void CreateSubMenu(DataSet dsSubMenu, DataRow drDataRow, MenuItem mainMenuItem)
    {
        //MenuID is the subMenu of the ParentMenuID
        //string MenuID = Convert.ToString(drDataRow["MenuID"]);
        string ParentMenuID = Convert.ToString(drDataRow["ParentMenuID"]);

        //Which is always true
        if (mainMenuItem.Text == string.Empty)
        {
            //If it is the firstChild of the parent find it here itself
            mainMenuItem = Menu1.FindItem(Convert.ToString(drDataRow["ParentMenuID"]));
            //If we can't able to find it in the main menu we have to look for sub-menu
            if (mainMenuItem == null)
                foreach (MenuItem mi in Menu1.Items)
                {
                    //Find the ParentMenu by ParentMenuID
                    mainMenuItem = GetParentMenuItem(mi, Convert.ToString(drDataRow["ParentMenuID"]));
                    if (mainMenuItem == null)
                        continue;
                    if (!mainMenuItem.Text.Trim().Equals(string.Empty))
                        break;
                }
        }

        if (mainMenuItem == null)
            return;
        //This the child element
        if (!string.IsNullOrEmpty(ParentMenuID))
        {
            MenuItem childrenItem = new MenuItem((string)drDataRow["LabelMenu"], Convert.ToString(drDataRow["MenuID"]));
            childrenItem.Target = (string)drDataRow["LinkMenu"];
            mainMenuItem.ChildItems.Add(childrenItem);
        }
    }

    // Recursive function   
    private MenuItem GetParentMenuItem(MenuItem root, string value)
    {
        MenuItem menuItem;
        //Is MenuItem has ChildItems
        if (root.ChildItems.Count > 0)
        {
            //Check which ChildItems is the Parent
            for (int i = 0; i < root.ChildItems.Count; i++)
            {
                menuItem = GetParentMenuItem(root.ChildItems[i], value);
                if (menuItem != null)
                    return menuItem;
            }
        }
        if (root.Value == value)
        {
            return root;
        }
        else
            return null;
    }

    private DataSet GetDataSetForMenu()
    {
        String constr = "Server=10.16.105.171;Database=test;Uid=test;Pwd=test123;Connect Timeout=0; pooling='true'; Max Pool Size=200";
        String query = @"SELECT MenuID, ParentMenuID, LabelMenu, LinkMenu FROM dbo.MainMenu ORDER BY MenuID ASC";
        DataSet dsMenu = new DataSet();

        using (SqlConnection conn = new SqlConnection(constr))
        {
            SqlDataAdapter da = new SqlDataAdapter(query, conn);
            da.Fill(dsMenu);
            da.Dispose();
        }

        dsMenu.DataSetName = "Menus";
        dsMenu.Tables[0].TableName = "Menu";
        DataRelation relation = new DataRelation("ParentChild",
                                dsMenu.Tables["Menu"].Columns["MenuID"],
                                dsMenu.Tables["Menu"].Columns["ParentMenuID"], true);

        relation.Nested = true;
        dsMenu.Relations.Add(relation);

        //This constraint cannot be enabled as not all values have corresponding parent values.

        return dsMenu;
    }

Reference Exception: This constraint cannot be enabled as not all values have corresponding parent values. We will get this exception if we set 0 for ParentMenuID. Where the LabelMenu is parent. Since there is no MenuID with value 0