Dynamic Menu Based on Database

• Download demo files - 23.2 Kb
• Download source code - 2.96 Kb

Introduction

I searched around for a menu based on a database, where each user in the database would "see" a different menu in the application depending on his permissions and privileges. Since I couldn't find one, I came up with a simple idea. I built tables for users and privileges in an Access database and loaded them to the menu application when the user logged in.

In my application, I made all the functions as UserControls. So in the database, each function had the name of the DLL and the name of the UserControl to call for each specific menu item. Then with the activator class, I loaded the UserControl to a panel in the main form of the application.

In this simple example, I just made a message box for each menu, that says what to do with each menu item based on the information in the database.

The database

The database is very simple: it contains three tables:

1. USERS
   • Contains username, user ID and password.
2. ITEMS
   • Contains all menu items - text is the text displayed in the menu and func is the function to call when the item is clicked (it can also be the name of the UserControl, so the Activator can call it to the main panel in the form).
3. PERMS
   • Contains all permissions for the user.

The code

When the user logs in, the application gets all the information about the user from the database. The code builds two Hashtables to map the items with the respective functions to call when the menu item is clicked. The first Hashtable is called HashMenu and it contains all the information about the menu items that the user can access. Since it is mandatory for the user to also have the parent items the function LoadMenuItems() gets from the database all the items that must be seen, so the user can access the ones he has permission to.

Function LoadMenuItems()
'Load all menu items and sub-items to menu

Dim dsItems As New DataSet
Dim i As Integer

'create a dataset with all items possible
Dim adap As New OleDb.OleDbDataAdapter("", conn)
adap.SelectCommand.CommandText = "SELECT * FROM ITEMS"
adap.Fill(dsItems)

'set the hierarchy as primary key to find 
'the menu item "parents"
dsItems.Tables(0).PrimaryKey = New DataColumn() 
    {dsItems.Tables(0).Columns("HIERAR")}
'go one by one all user items
For i = 0 To dsUser.Tables(0).Rows.Count - 1  
    Dim id_hierar As String 'hierarchy for the item (parents 
                            'and parents parents and so on)
    Dim parent As String 'temp var to store parent index
    
    id_hierar = dsUser.Tables(0).Rows(i)("HIERAR")
    Dim menu_it As New DinamicMenu
    menu_it.Text = dsUser.Tables(0).Rows(i)("TEXT")
    menu_it.Func = dsUser.Tables(0).Rows(i)("FUNC")
    menu_it.MenuItemobj = New MenuItem
    menu_it.MenuItemobj.Text = dsUser.Tables(0).Rows(i)("TEXT")
    AddHandler menu_it.MenuItemobj.Click, AddressOf CallForMenuItem
    If Not HashMenu.ContainsKey(dsUser.Tables(0).Rows(i)("HIERAR")) Then
        HashMenu.Add(dsUser.Tables(0).Rows(i)("HIERAR"), menu_it)
    End If
    While id_hierar.IndexOf(".") > 0
        parent = id_hierar.Substring(0, id_hierar.LastIndexOf("."))
        id_hierar = parent
        Dim dRow As DataRow 'dataRow to store the item row from db
        
        dRow = dsItems.Tables(0).Rows.Find(parent)
        If Not dRow Is Nothing Then
            If Not HashMenu.ContainsKey(parent) Then
                Dim it_dinamic As New DinamicMenu
                it_dinamic.Text = dRow("TEXT")
                it_dinamic.Func = dRow("FUNC")
                it_dinamic.MenuItemobj = New MenuItem
                it_dinamic.MenuItemobj.Text = dRow("TEXT")
                AddHandler it_dinamic.MenuItemobj.Click, _
                                  AddressOf CallForMenuItem
                HashMenu.Add(parent, it_dinamic)
            End If
        End If
    End While
Next

Dim d As DictionaryEntry
Dim parentMenu As New MenuItem
Dim sonMenu As New MenuItem
Dim parentItem As New DinamicMenu
Dim sonItem As New DinamicMenu
Dim sel_key, sel_KeyParent As String

For Each d In HashMenu
    sonItem = d.Value
    sel_key = d.Key
    sonMenu = sonItem.MenuItemobj
    If sel_key.LastIndexOf(".") >= 0 Then
        sel_KeyParent = sel_key.Substring(0, _
                         sel_key.LastIndexOf("."))
        parentItem = HashMenu(sel_KeyParent)
        parentMenu = parentItem.MenuItemobj
        parentMenu.MenuItems.Add(sonMenu)
    Else
        Me.MainMenu.MenuItems.Add(sonMenu)
    End If
Next
CreateHashFunctions()

For each user, the order is different, because they have different permissions, so we have to build a hashtable, called HashFunctions, to create a relation between the place of the item and the item function. There is another function called CallForMenuItem() that executes the function based on the place the menu item was called (since the relation between the place and the function is stored in the hashFunctions Hashtable).

Example

In this simple example, the user logs in as presmad@hotmail.com or presmad@uol.com.br, the password is 1234 for both. These different emails stand for different users. Note that for each user who logs in, the menu is different. If you change the permissions in the database without changing anything in the code, you will see that the new menu from the database applies for the application when you login. The same is valid for creating new users with new menus, of course. Look at the image at the top of this article; note that each user has a different menu when he logs in.

I hope this idea is useful to you, and if you have better ideas about how to built the menu I would be happy to hear from you.