Part 156 - Binding asp.net menu control to database table

Suggested Videos
Part 153 - Menu control in asp.net
Part 154 - Using styles with asp.net menu control
Part 155 - Binding menu control to xml file



In this video, we will discuss binding asp.net menu control to database table. This is continuation to Part 155, please watch Part 155 before proceeding.



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);
    }
}