Saturday, September 26, 2015

Binding menu control to database table

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

In this video, we will discuss binding 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
Select * from tblMenuItemsLevel1
Select * from tblMenuItemsLevel2

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

4. Copy and paste the following code in the code-behind file.
protected void Page_Load(object sender, EventArgs e)

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

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

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,
        item.Selected = true;
    else if (item.ChildItems.Count > 0)
        foreach (MenuItem menuItem in item.ChildItems)

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

If you are searching life partner. your searching end with now offer free matrimonial website which offer free message, free chat, free view contact information. so register here : Free matrimonial website