Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to bind Menu control with database in ASP.NET

Author: Nikki Smith
Download Source Code : 1402_BindMenuControlWithDatabase.zip

In this article, I will show how we can bind Menu control with SQL Server database data in ASP.NET.

 

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

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to return generic list from Web Service and handle it using jQuery in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net