santosh asp.net tutorial

In this article, we will discuss binding asp.net menu control to database table.

1. Create the required database tables using the script below.
Create Table tblMenuItemsLevel1
(
ID int identity primary key,
MenuText nvarchar(50),
NavigateURL nvarchar(50)
)

Insert into tblMenuItemsLevel1 values('Home','~/Home.aspx')
Insert into tblMenuItemsLevel1 values('Employee','~/Employee.aspx')
Insert into tblMenuItemsLevel1 values('Employer','~/Employer.aspx')
Insert into tblMenuItemsLevel1 values('Admin','~/Admin.aspx')

Create Table tblMenuItemsLevel2
(
ID int identity primary key,
MenuText nvarchar(50),
NavigateURL nvarchar(50),
ParentId int foreign key references tblMenuItemsLevel1(ID)
)

Insert into tblMenuItemsLevel2 values ('Upload Resume','~/UploadResume.aspx',2)
Insert into tblMenuItemsLevel2 values ('Edit Resume','~/EditResume.aspx',2)
Insert into tblMenuItemsLevel2 values ('View Resume','~/ViewResume.aspx',2)

Insert into tblMenuItemsLevel2 values ('Upload Job','~/UploadJob.aspx',3)
Insert into tblMenuItemsLevel2 values ('Edit Job','~/EditJob.aspx',3)
Insert into tblMenuItemsLevel2 values ('View Job','~/ViewJob.aspx',3)

Insert into tblMenuItemsLevel2 values ('Add User','~/AddUser.aspx',4)
Insert into tblMenuItemsLevel2 values ('Edit User','~/EditUser.aspx',4)
Insert into tblMenuItemsLevel2 values ('View User','~/ViewUser.aspx',4)

2. Create a stored procedure that returns data from both the tables.
Create Proc spGetMenuData
as
Begin
Select * from tblMenuItemsLevel1
Select * from tblMenuItemsLevel2
End

3. Drag and drop a menu control on the webform
<asp:Menu ID="Menu1" runat="server">
</asp:Menu>

4. Copy and paste the following ado.net code in the code-behind file.
protected void Page_Load(object sender, EventArgs e)
{
    GetMenuItems();
}

private void GetMenuItems()
{
    string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    SqlConnection con = new SqlConnection(cs);
    SqlDataAdapter da = new SqlDataAdapter("spGetMenuData", con);
    da.SelectCommand.CommandType = CommandType.StoredProcedure;
    DataSet ds = new DataSet();
    da.Fill(ds);

    ds.Relations.Add("ChildRows", ds.Tables[0].Columns["ID"], ds.Tables[1].Columns["ParentId"]);

    foreach (DataRow level1DataRow in ds.Tables[0].Rows)
    {
        MenuItem item = new MenuItem();
        item.Text = level1DataRow["MenuText"].ToString();
        item.NavigateUrl = level1DataRow["NavigateURL"].ToString();

        DataRow[] level2DataRows = level1DataRow.GetChildRows("ChildRows");
        foreach (DataRow level2DataRow in level2DataRows)
        {
            MenuItem childItem = new MenuItem();
            childItem.Text = level2DataRow["MenuText"].ToString();
            childItem.NavigateUrl = level2DataRow["NavigateURL"].ToString();
            item.ChildItems.Add(childItem);
        }
        Menu1.Items.Add(item);
    }
}

Note: Please include the following using declarations.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

If you want the menu control to apply selected menu item styles.
1. Include OnPreRender attribute in the aspx page
2. Copy and paste the following code in the code-behind file.
private void Check(MenuItem item)
{
    if (item.NavigateUrl.Equals(Request.AppRelativeCurrentExecutionFilePath,
        StringComparison.InvariantCultureIgnoreCase))
    {
        item.Selected = true;
    }
    else if (item.ChildItems.Count > 0)
    {
        foreach (MenuItem menuItem in item.ChildItems)
        {
            Check(menuItem);
        }
    }
}

protected void Menu1_PreRender(object sender, EventArgs e)
{
    foreach (MenuItem item in Menu1.Items)
    {
        Check(item);
    }
}