Dynamic Menu Creation Using Oracle Database

In this article, I am going to share with you how to create a dynamic menu in #Asp.Net using the Oracle database. I will guide you step by step process of creation.

Database Structure and Table Fields

First, create one table which holds the list of menus to be displayed in the navigation.

Sharing the structure here.

Database Table Script.

CREATE TABLE SAMPLE_MAS_MENU_TBL 
(    
 MENU_ID NUMBER,   -- Unique Menu ID 
 TITLE VARCHAR2(50 BYTE),  -- Menu Title
 PARENT_ID NUMBER,  -- Child Menu Parent ID
 SEQ NUMBER,  -- Ordering Sequence
 URL VARCHAR2(100 BYTE),  -- Navigation Url
 ACTIVE CHAR(1 BYTE)    -- Active/Inactive
)

Once you created the table, put entries in the table as below.

MENU_ID	TITLE	PARENT_ID	SEQ	URL	ACTIVE
1	Home	0	1	Home.aspx	Y
2	Master	0	2	Home.aspx	Y
3	User Management	0	3	Home.aspx	Y
4	Master 1	2	1	Master1.aspx	Y
5	Master 2	2	2	Master2.aspx	Y
6	User Management 1	3	1	UserMgmt1.aspx	Y
7	User Management 2	3	2	UserMgmt2.aspx	Y

So this is what your database entries look like. I have just added three menus, Home, master, and User Management, and two child menus of master and User Management.

As your menu increases, you add entries in the table and assign parent id to the same. Also, if you want to hide any menu, then update the active flag to N.

So that is from the database part now will move to master page creation and code behind part.

Master Page Code Snippets

Here sharing the Menu code. Once you created the master page, add this section to your page main content.

<div class="navbar navbar-expand-lg navbar-light bg-light">
    <div class="collapse navbar-collapse" style="background-color: #0B6121;">
       <asp:Menu ID="NavigationMenu" runat="server" StaticMenuStyle-CssClass="nav navbar-nav" DynamicMenuStyle-CssClass="dropdown-menu" EnableViewState="false" IncludeStyleBlock="false" Orientation="Horizontal"></asp:Menu>
    </div>
</div>

So after this, we need to assign the code behind for menu to work. So let's work on that part.

string strConnection = "Provider=MSDAORA.1;User ID=user123;password=aBCD1234;Data Source=NameofDatabase;Persist Security Info=False";

Note: pass the connection string as per your database credentials.

Code Behind of Master Page

protected void Page_Load(object sender, EventArgs e)
{
    try
    {           
        if (!IsPostBack)
        {             
    }
        GetMenu(NavigationMenu);
    }
    catch (Exception ex)
    {
        Response.Redirect("ExceptionPage.aspx");         
    }
}

GetMenu Function

private void GetMenu(Menu CntMenu, string UtCode)
{
    ClsCommand _dbContext = new ClsCommand(); // This is class file used for getting the dataset                 
    SqlQuery = "SELECT MENU_ID, TITLE, PARENT_ID, URL FROM SAMPLE_MAS_MENU_TBL WHERE ACTIVE = 'Y'  ORDER BY MENU_ID";
    DataSet dbTbl = _dbContext.GetDataSet(SqlQuery, Obj._ConnIND);

    DataColumn dbColAccess = new DataColumn("Access", typeof(System.String));
    dbColAccess.DefaultValue = "0";
    dbTbl.Tables[0].Columns.Add(dbColAccess);

    int MenuIndex = 0;
    foreach (DataRow tblRow in dbTbl.Tables[0].Rows)
    {
        tblRow.SetField("Access", AccessRight.Substring(MenuIndex, 1));
        MenuIndex++;
    }

    DataTable dbTblMenu = new DataTable();
    dbTbl.Tables[0].DefaultView.RowFilter = "Access = 1 AND PARENT_ID=0";
    dbTblMenu = dbTbl.Tables[0].DefaultView.ToTable();

    foreach (DataRow dbRowId in dbTblMenu.Rows)
    {

        MenuItem menuItem = new MenuItem(dbRowId["TITLE"].ToString(), dbRowId["MENU_ID"].ToString(), "", dbRowId["URL"].ToString());
        CntMenu.Items.Add(menuItem);
        AddChild(menuItem, dbTbl);
    }
}

Child Menu Code

private void AddChild(MenuItem ParentMenuItem, DataSet dbTbl)
{
    DataTable dbTblSubMenu = new DataTable();
    dbTbl.Tables[0].DefaultView.RowFilter = "Access = 1 AND PARENT_ID=" + ParentMenuItem.Value;
    dbTblSubMenu = dbTbl.Tables[0].DefaultView.ToTable();
    foreach (DataRow row in dbTblSubMenu.Rows)
    {
        MenuItem menuItem = new MenuItem(row["TITLE"].ToString(), row["MENU_ID"].ToString(), "", row["URL"].ToString());
        ParentMenuItem.ChildItems.Add(menuItem);
        AddChild(menuItem, dbTbl);
    }
}

Now create the Class file name ClsCommand and add the following code

public string _ConnIND = ConfigurationManager.ConnectionStrings["DbConnWebconfig"].ConnectionString;
OleDbConnection ObjOleDBConn = new OleDbConnection();
OleDbCommand ObjOleDBComd = new OleDbCommand();
DataSet ObjDTSet = null;
OleDbDataAdapter ObjOleDBAdapter = null;
string SQL = "";

public string GetNonScalar_Ret(string strQuery, string Conn)
{

    string RetVal = "FALSE"; ;
    try
    {
        OleDbConnection ObjOleDBConn = new OleDbConnection(Conn);
        ObjOleDBConn.Open();

        ObjOleDBComd.Connection = ObjOleDBConn;
        ObjOleDBComd.CommandType = CommandType.Text;
        ObjOleDBComd.CommandText = strQuery;

        ObjOleDBComd.ExecuteNonQuery();

        RetVal = "TRUE";
    }
    catch (Exception ex)
    {
        ex.Message.ToString();          
    }
    finally
    {
        ObjOleDBComd.Dispose();
        ObjOleDBConn.Close();
    }

    return RetVal;
}

public DataSet GetDataSet(string str_SQL, string Conn)
{       
    ObjOleDBComd = new OleDbCommand();
    ObjDTSet = new DataSet();

    try
    {
        OleDbConnection ObjOleDBConn = new OleDbConnection(Conn);
        ObjOleDBConn.Open();
        ObjOleDBComd.Connection = ObjOleDBConn;
        ObjOleDBComd.CommandType = CommandType.Text;
        ObjOleDBComd.CommandText = str_SQL;
        ObjOleDBAdapter = new OleDbDataAdapter(ObjOleDBComd);

        ObjDTSet.Clear();
        ObjOleDBAdapter.Fill(ObjDTSet);
    }
    catch (Exception Exp)
    {
        Exp.Message.ToString(); ObjOleDBConn.Close();
    }
    finally
    {
        ObjOleDBComd.Dispose();
        ObjOleDBConn.Close();
    }
    return ObjDTSet;
}

Add this connection string to Web.config

<connectionStrings>
    <add name="DbConnWebconfig" connectionString="Provider=MSDAORA.1;Data Source=NameofDatabase;Password=aBCD1234;User ID=user123" providerName="System.Data.Oledb"/>    
</connectionStrings>

All is good. Now run the project. Your Dynamic Menu is ready to operate. please feel free to share if any help is required and faced any problems.

Output

HomePage

Master Page

User Management