How to bind Menu control with database in ASP.NET?

Answer 1

Menu control is used to display menus in ASP.NET web sites. We can display horizontal or vertical menus by using this menu control. Menu control has multiple other properties that we can set with different values to change the appearance and layout of the menu control. Static and Dynamic display can be set using StaticDisplayLevel and MaximumDynamicDisplayLevel properties. Menu items can be added manually or we can use data source to add menu items in Menu control.

In this article, I will bind menu control with NORTHWIND database data. I will define a relation between Categories and Products tables and add categories as menu items from Categories table and products as child items for categories from Products table.
1.Create a new Empty Web Site in Visual Studio 2010 either in Visual Basic or Visual C#.
2.Add a Web Form in the Web Site. No Need to change name of the Page
3.Add a Menu control in Web Form and set properties of Menu Control.

<asp:Menu ID="Menu1" runat="server" BackColor="#00FF40" 
    Orientation="Horizontal">
    <DynamicHoverStyle BackColor="Red" Font-Bold="True" />
    <StaticHoverStyle BackColor="#FF8000" Font-Bold="True" />
</asp:Menu>

4.Namespaces used in the code

Visual Basic

Imports System.Data
Imports System.Data.SqlClient

Visual C#

using System.Data;
using System.Data.SqlClient;

5.Write code below in code behind file

GetData() function is written to get data from Categories and Products table in a DataSet object and this function returns DataSet. In Page Load event, a DataRelation is created between Categories and Products table on the basis of CategoryID column. Each category is added to Menu control item from Categories table. Then each product is added to each category as child item by using relation between these tables.

Visual Basic

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    Dim ds As DataSet = GetData()
    Dim relation As New DataRelation("CatProdRelation", ds.Tables(0).Columns("CategoryID"), ds.Tables(1).Columns("CategoryID"))
    ds.Relations.Add(relation)

    For Each catRow As DataRow In ds.Tables("Categories").Rows
        Dim category As New MenuItem(catRow("CategoryName").ToString())
        Menu1.Items.Add(category)

        For Each prodRow As DataRow In catRow.GetChildRows(relation)
            Dim products As New MenuItem(prodRow("ProductName").ToString())
            category.ChildItems.Add(products)
        Next
    Next
End Sub

Private Function GetData() As DataSet
    Dim text1 As String = "SELECT CategoryID, CategoryName FROM Categories"
    Dim text2 As String = "SELECT ProductID, CategoryID, ProductName FROM Products"
    Dim connString As String = "Data Source=YourServer;Initial Catalog=NORTHWND;Integrated Security=True"
    Dim conn As New SqlConnection(connString)
    Dim cmd1 As New SqlCommand(text1, conn)
    Dim cmd2 As New SqlCommand(text2, conn)
    conn.Open()
    Dim da1 As New SqlDataAdapter(cmd1)
    Dim da2 As New SqlDataAdapter(cmd2)
    Dim ds As New DataSet()
    da1.Fill(ds, "Categories")
    da2.Fill(ds, "Products")
    conn.Close()
    Return ds
End Function

Visual C#

protected void Page_Load(object sender, EventArgs e)
{
    DataSet ds = GetData();
    DataRelation relation = new DataRelation("CatProdRelation", ds.Tables[0].Columns["CategoryID"], ds.Tables[1].Columns["CategoryID"]);
    ds.Relations.Add(relation);

    foreach (DataRow catRow in ds.Tables["Categories"].Rows)
    {
        MenuItem category = new MenuItem(catRow["CategoryName"].ToString());
        Menu1.Items.Add(category);

        foreach (DataRow prodRow in catRow.GetChildRows(relation))
        {
            MenuItem products = new MenuItem(prodRow["ProductName"].ToString());
            category.ChildItems.Add(products);
        }
    }
}

private DataSet GetData()
{
    string text1 = "SELECT CategoryID, CategoryName FROM Categories";
    string text2 = "SELECT ProductID, CategoryID, ProductName FROM Products";
    string connString = "Data Source=YourServer;Initial Catalog=NORTHWND;Integrated Security=True";
    SqlConnection conn = new SqlConnection(connString);
    SqlCommand cmd1 = new SqlCommand(text1, conn);
    SqlCommand cmd2 = new SqlCommand(text2, conn);
    conn.Open();
    SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
    SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
    DataSet ds = new DataSet();
    da1.Fill(ds, "Categories");
    da2.Fill(ds, "Products");
    conn.Close();
    return ds;
}

6.Now see website in your browser