Binding Menu Control With Database

• Download source - 4.83 KB

Introduction

In this article, I will show how to bind the menu control with the database. Suppose you have a situation to display the different Category and its Sub-Categories. When we click the Category/Sub Category (See Figure 1) to view the detail information about Category/Sub Category (See Figure 2), then this bit of code will be useful. This code prevents a post back to the server while selecting Sub Category.

Using the Code

Database Detail

Table Name - Category

Table Name - SubCategory

There are two tables (Category, SubCategory) which are related to each other.
Here’s the method called BindMenu which binds the menu control during the page load.

1. Create the connection string to establish connection with the database:
   VB.NET

   Dim connectionString As String =
   WebConfigurationManager.ConnectionStrings_
   	("DatabaseConnectionString1").ConnectionString
   Dim con As New SqlConnection(connectionString)

2. Create two DataAdapters for both the tables:
   VB.NET

   Dim dadCategories As New SqlDataAdapter("SELECT CatId,CatName FROM Category
   order by CatName", con)
   Dim dadSubCat As New SqlDataAdapter("SELECT SubCatId,CatId,SubCatName FROM
   SubCategory order by SubCatName", con)

3. Create a DataSet and fill the dataset with both the tables:
   VB.NET

   Dim dsCat As New DataSet()
   Using con
   con.Open()
   dadCategories.Fill(dsCat, "Category")
   dadSubCat.Fill(dsCat, "SubCategory")
   End Using

4. Relate both the tables and name the relation as Children:
   VB.NET

   dsCat.Relations.Add("Children", dsCat.Tables("Category").Columns("CatId"),
   dsCat.Tables("SubCategory").Columns("CatId")) 

5. Loop through each category of data and its related Sub category of data. At each loop, we create menu items and associate with the menu control.
   VB.NET

   For Each categoryRow As DataRow In dsCat.Tables("Category").Rows
   Dim mNode As New MenuItem(CType(categoryRow("CatName"), String), "", "",
   "~/DetailView.aspx?CatID=" + CType(categoryRow("CatId"), String), "_parent")
   Menu1.Items.Add(mNode)
   
   Dim subCatRows() As DataRow = categoryRow.GetChildRows("Children")
   For Each row As DataRow In subCatRows
   Dim subCatName As String = CType(row("SubCatName"), String)
   Dim subCatItems As New MenuItem(subCatName, "", "",
   "~/DetailView.aspx?CatID=" + CType(row("CatId"), String) + "&SubCatID=" +
   CType(row("SubCatId"), String), "_parent")
   Menu1.Items(count).ChildItems.Add(subCatItems)
   Next
   count = count + 1
   Next

History

• 28^th June, 2009: Initial post