Populate MainMenu Dynamically in VB.NET Using Recursion

Introduction

I have written the following code to demonstrate populating a MainMenu with a VB.Net application. The program uses a recursion technique to populate the mainmenu regardless of the number of child menuitems required for a particular parent menuitem.

Background

The purpose of writing the following code is due to the application which I am developing requires varying permission levels to user accounts, so I decided rather than setting a defined permission code for various areas. I decided that it would be better that the users can gain multiple permutations to different areas of the application by building up the mainmenu from an SQL. This will allow me to define each user individually to different menuitems, thus allowing different menuitems to be enabled dependent on the vary real life factors which may occur.

I was looking online to find such code, but unfortunately couldn't find anything which would suit my requirements, hence my post.

Hopefully users will find this helpful.

Using the code

I have tried to lay this code out in a number of stages as there is a fair bit to carry out to get the code functioning.

First Step:

Firstly you will require a SQL service connection and 4 Tables

1. MenuItems (All MenuItems are defined here)
2. MenuItems_Master (All High-Level MenuItems are defined)
3. MenuItems_Parent (All Parent MenuItems are defined here)
4. MenuItems_Child (All Parent MenuItems are defined here)

MenuItem:

CREATE TABLE [dbo].[MenuItems] ( 

    [MenuID]   INT           IDENTITY (1, 1) NOT NULL, 

    [MenuText] NVARCHAR (50) NULL,

    [MenuName] NVARCHAR (50) NULL,

    [FormName] NVARCHAR (50) NULL, 

    PRIMARY KEY CLUSTERED ([MenuID] ASC)

);

MenuItems_Master:

CREATE TABLE [dbo].[MenuItems_Master] ( 

    [MasterMenuItemID] INT IDENTITY (1, 1) NOT NULL,

    [MenuID]           INT NULL, 

    PRIMARY KEY CLUSTERED ([MasterMenuItemID] ASC),

    CONSTRAINT [FK_MenuItems_Master_To_MenuItems] FOREIGN KEY ([MenuID]) REFERENCES [dbo].[MenuItems] ([MenuID])

); 

MenuItems_Parent:

CREATE TABLE [dbo].[MenuItems_Parent] ( 

    [ParentMenuItemID] INT IDENTITY (1, 1) NOT NULL, 

    [MenuID]           INT NULL, 

    PRIMARY KEY CLUSTERED ([ParentMenuItemID] ASC),

    CONSTRAINT [FK_MenuItems_Parent_To_MenuItems] FOREIGN KEY ([MenuID]) REFERENCES [dbo].[MenuItems] ([MenuID])

);

MenuItems_Child:

CREATE TABLE [dbo].[MenuItems_Child] ( 

    [ChildMenuItemID]  INT IDENTITY (1, 1) NOT NULL, 

    [ParentMenuItemID] INT NULL,

    [MenuID]           INT NULL, 

    PRIMARY KEY CLUSTERED ([ChildMenuItemID] ASC),

    CONSTRAINT [FK_MenuItems_Child_To_MenuItems_Parent] FOREIGN KEY ([MenuID]) REFERENCES [dbo].[MenuItems_Parent] ([ParentMenuItemID]), 

    CONSTRAINT [FK_MenuItems_Child_To_MenuItems] FOREIGN KEY ([MenuID]) REFERENCES [dbo].[MenuItems] ([MenuID])
); 

Second Step:

You now require data in each of these tables.

• Table MenuItem - Add all MenuItems which you require to add
• Table MenuItem_Master - Add all MenuItems by MenuID into this table (i.e. file, edit, view, tools, help etc)
• Table MenuItem_Parent - Add All MenuItems by MenuID which are acting like parents (i.e. file, edit, view, tools, help) these would still be deemed as parent MenuItems . I would also recommend adding parent items within the child items of file, edit, view or tools.
• Table MenuItem_Child - Add in all child MenuItems by MenuID and define its parentID

Third Step:

Once you have completed the above steps you should have 4 tables within your SQL service with all menuitems and their relationships to one another.

Firstly setup the SQL references required to execute SQL commands, methods etc.

Select Project>myProject Properties>References>

Enable: System.Data.Sql | System.Data.SqlClient | System.Data.SqlTypes

Now you need to create a connection to your database

Module SQL_Connections

    Public m_cn As New SqlConnection 
    Public m_com As New SqlCommand
    Public sdr As SqlDataReader 

    Public Sub open_connection() 
        '***********************************
        'Opens connection to SQL server 
        '***********************************
        Dim Attempts As Integer
        m_cn.ConnectionString = My.Settings.ConnectionString
Line1:
        If Attempts < 1 Then 
            Try 'Attempt to open connection
                m_cn.Open()
           Catch ex As Exception 

                If MessageBox.Show("Unable to connect to Integrated Engineering Tool service. Do you want to retry?", "SQL Server Error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error) = DialogResult.Retry Then
                    Attempts += 1
                    GoTo Line1
                End If 
            End Try
        Else 
            MessageBox.Show("Unable to connect to Integrated Engineering Tool service, please contact your systems administrator.", "SQL Server Error", MessageBoxButtons.OK, MessageBoxIcon.Error) 
        End If
    End Sub



    Public Sub close_connection()

        '*********************************** 
        'Closes connection to SQL server 
        '***********************************
        m_cn.Close()
    End Sub 


End Module

Now you need to create the function that operates recursively until all MenuItems have been added according to their relationship created in the 4 datatables previously.

Module SQL_MainMenu


    Public Sub MainMenu_Items_Child(ByVal MenuName As String, ByVal parentmenuitem As MenuItem)
       '*********************************** 
        'Gets all child menu items of parent 
        '*********************************** 

        Dim datatable_child As DataTable 
        Dim m_datarow_child As DataRow

        open_connection() 'opens SQL Connection

        Dim m_adapter As New SqlDataAdapter("SELECT MenuItems_1.MenuText AS ChildText, MenuItems_1.MenuName AS ChildName" & _ 
            " FROM MenuItems_Child LEFT OUTER JOIN MenuItems AS MenuItems_1 ON MenuItems_Child.MenuID = MenuItems_1.MenuID LEFT OUTER JOIN" & _ 
            " MenuItems_Parent ON MenuItems_Child.ParentMenuItemID = MenuItems_Parent.ParentMenuItemID LEFT OUTER JOIN" & _
            " MenuItems ON MenuItems_Parent.MenuID = MenuItems.MenuID" & _
            " WHERE MenuItems.MenuName = '" & MenuName & "' ORDER BY ChildName", m_cn) 

        Dim m_dataset_child As New DataSet("ChildMenuItem") 

        m_adapter.Fill(m_dataset_child, "ChildItems") 

        datatable_child = m_dataset_child.Tables("ChildItems")

        close_connection()

        If datatable_child.Rows.Count > 0 Then 

            For Each m_datarow_child In datatable_child.Rows 

                Dim newchildmenuitem As New MenuItem
                newchildmenuitem.Text = m_datarow_child.Item(0)
                newchildmenuitem.Name = m_datarow_child.Item(1)
                parentmenuitem.MenuItems.Add(newchildmenuitem)
                AddHandler newchildmenuitem.Click, AddressOf formMain.OnFile 

                MainMenu_Items_Child(Convert.ToString(m_datarow_child.Item(1)), newchildmenuitem) 'sub routine calls itself to execute to retrieve child items 

            Next


        End If

   End Sub




End Module

Forth Step:

You now need to create a MDIParent container form, in my application I call this formMain. Using the Load Event, you simply create each master item in your MenuItem_Master Table.

Private Sub formMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Dim menu As New MainMenu 
        Dim datatable_parent As DataTable
        Dim m_datarow_parent As DataRow

        open_connection()

        Dim m_adapter As New SqlDataAdapter("SELECT MenuItems.MenuText, MenuItems.MenuName FROM MenuItems RIGHT OUTER JOIN MenuItems_Master ON" & _ 
            " MenuItems.MenuID = MenuItems_Master.MenuID", m_cn) 

        Dim m_dataset As New DataSet("MasterMenuItem") 

        m_adapter.Fill(m_dataset, "ParentItems")

        datatable_parent = m_dataset.Tables("ParentItems") 

        m_adapter = Nothing 

        m_dataset = Nothing
        close_connection()

        For Each m_datarow_parent In datatable_parent.Rows 

            Dim newmastermenuitem As New MenuItem
            newmastermenuitem.Text = m_datarow_parent.Item(0)
            newmastermenuitem.Name = m_datarow_parent.Item(1)
            menu.MenuItems.Add(newmastermenuitem)

            AddHandler newmastermenuitem.Click, AddressOf Me.OnFile 

            MainMenu_Items_Child(Convert.ToString(m_datarow_parent.Item(1)), newmastermenuitem) 

        Next 

        Me.Menu = menu 
    End Sub  

Next step would be to then select a menuitem and open a form, due to the menu being dynamically populated I found that it would be easier to store a string within MenuItems Table and call upon this using the sender name from the below code. This eventhandler was generated each time a new menuitem was created. So using just one event handler for the whole MainMenu.

Public  Sub OnFile(ByVal sender As Object, e As EventArgs)

Dim menu As MenuItem = DirectCast(sender, MenuItem)


End Sub       

To make the above work you will require a new function to return the form name for the selected sender (menu.name).

I have created the below function to return the formstring value from MenuItems table which was previous created.

Public Function GetformName(ByVal MenuItemName As String) As String

       '***********************************
        'Retrieves the formname
        '***********************************

        open_connection()

        m_com.CommandType = CommandType.Text
        m_com.CommandText = "SELECT FormName From MenuItems WHERE MenuName = '" & MenuItemName & "'"
        m_com.Connection = m_cn

        sdr = m_com.ExecuteReader

        Do While sdr.Read = True 
            GetformName = sdr.Item("FormName")
            close_connection()
            Exit Function
        Loop
        GetformName = Nothing
    End Function 

You can now apply the above function into the event handler OnFile

Public  Sub OnFile(ByVal sender As Object, e As EventArgs)

Dim menu As MenuItem = DirectCast(sender, MenuItem) 

Dim str As String
        GetformName(menu.Name)

        Dim objType As Type 
        str = "Integrated_Engineering_Tool" & "." & GetformName(menu.Name) 
        objType = Type.GetType(str) 
        Dim objForm As Control = DirectCast(Activator.CreateInstance(objType), Control)
        objForm.Show()

End Sub       

The "Integrated_Engineering_Tool" was the root namespace of my application this can be found in

Project>myProject Properties>Application

Please feel free to provide any feedback on this post.