Click here to Skip to main content
15,868,141 members
Articles / Programming Languages / Visual Basic

Dynamic Menu Based on Database

Rate me:
Please Sign up or sign in to vote.
4.48/5 (20 votes)
10 Nov 20053 min read 222.1K   5.4K   92   62
A dynamic personal menu for applications based on the information in database.

Image 1

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.

Image 2

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.

VB
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 build the menu, I would be happy to hear from you.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below.

A list of licenses authors might use can be found here.


Written By
Web Developer
Brazil Brazil
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionCode in C# version Pin
AndyLPJr29-Feb-12 22:09
AndyLPJr29-Feb-12 22:09 
Generalsomething is missing ! Pin
ss_hellhound5-Feb-11 8:35
ss_hellhound5-Feb-11 8:35 
GeneralC# version Pin
Member 420593923-Dec-10 20:31
Member 420593923-Dec-10 20:31 
GeneralAnother examples Pin
melch211313-Aug-09 6:56
melch211313-Aug-09 6:56 
GeneralRe: Another examples Pin
Daniel Presman2-Sep-09 7:37
Daniel Presman2-Sep-09 7:37 
GeneralThanks for such a good post! Pin
kmshelke10-Dec-08 23:00
kmshelke10-Dec-08 23:00 
QuestionHow to run code from funcToCall 2 Pin
evilson11-Nov-07 3:39
evilson11-Nov-07 3:39 
GeneralRe: How to run code from funcToCall 2 Pin
Daniel Presman29-Jan-08 3:45
Daniel Presman29-Jan-08 3:45 
QuestionCalling Form From Dynamic Menu Pin
abuzy27-Jul-07 0:20
abuzy27-Jul-07 0:20 
Generala note from Siras Pin
Sean Ewington10-Jul-07 10:30
staffSean Ewington10-Jul-07 10:30 
GeneralRe: a note from Siras Pin
Daniel Presman14-Jul-07 16:28
Daniel Presman14-Jul-07 16:28 
GeneralDynamic menus... Pin
Member 38798818-Mar-07 19:22
Member 38798818-Mar-07 19:22 
GeneralRe: Dynamic menus... Pin
Daniel Presman14-Jul-07 16:31
Daniel Presman14-Jul-07 16:31 
QuestionHow to run code from funcToCall Pin
qsheeraz10-Feb-07 4:36
qsheeraz10-Feb-07 4:36 
AnswerRe: How to run code from funcToCall Pin
Daniel Presman14-Jul-07 16:36
Daniel Presman14-Jul-07 16:36 
Questioni got error when running this code Pin
Member 38116888-Feb-07 20:10
Member 38116888-Feb-07 20:10 
QuestionHi Pin
hava717111-Jan-07 5:23
hava717111-Jan-07 5:23 
GeneralI have download the sample and I didnt see the database Pin
hava717121-Aug-06 5:20
hava717121-Aug-06 5:20 
AnswerRe: I have download the sample and I didnt see the database Pin
Daniel Presman2-Sep-06 13:08
Daniel Presman2-Sep-06 13:08 
GeneralI have a got problem in Menu Dynamic Pin
tnquang18-Jul-06 23:37
tnquang18-Jul-06 23:37 
GeneralRe: I have a got problem in Menu Dynamic Pin
Daniel Presman20-Jul-06 3:50
Daniel Presman20-Jul-06 3:50 
GeneralRe: I have a got problem in Menu Dynamic Pin
tnquang20-Jul-06 23:07
tnquang20-Jul-06 23:07 
GeneralRe: I have a got problem in Menu Dynamic Pin
Daniel Presman21-Jul-06 3:25
Daniel Presman21-Jul-06 3:25 
GeneralRe: I have a got problem in Menu Dynamic Pin
tnquang23-Jul-06 16:16
tnquang23-Jul-06 16:16 
Generalindex on menu text Pin
luxeanus23-Apr-06 17:54
luxeanus23-Apr-06 17:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.