Populating the Menu using the Database

Suppose you have developed web application with multiple pages and want to give access of pages through web application then you will like to store selected pages and options into database  which you want to populate the Menu.  The code below shows how you can use database as a data source:


Private Sub populateMenu()


Dim ds As DataSet
ds = GetDataSetForMenu()
Dim mmenu As New Menu
mmenu.Font.Name = "Arial"
mmenu.Font.Size = 11
mmenu.Height = 25
mmenu.Orientation = Orientation.Horizontal
mmenu.BackColor = Drawing.Color.DarkBlue
mmenu.ForeColor = Drawing.Color.LightBlue
mmenu.DynamicVerticalOffset = 8
mmenu.DynamicMenuStyle.BackColor = Drawing.Color.DarkBlue
mmenu.DynamicMenuStyle.ForeColor = Drawing.Color.White
mmenu.DynamicMenuStyle.HorizontalPadding = 5
mmenu.DynamicHoverStyle.BackColor = Drawing.Color.Blue
mmenu.DynamicHoverStyle.ForeColor = Drawing.Color.LightSteelBlue
mmenu.DynamicHoverStyle.Height = 20
mmenu.DynamicMenuItemStyle.ItemSpacing = 5


For Each parentItem As DataRow In ds.Tables("mdesc").Rows

   Dim categoryItem As New MenuItem(DirectCast(parentItem("modulecat"), String))
   mmenu.Items.Add(categoryItem)

   For Each childItem As DataRow In parentItem.GetChildRows("Children")

      Dim childrenItem As New MenuItem(DirectCast(childItem("moduledesc"), String))
      Dim childrenNavigateItem As New MenuItem(DirectCast(childItem("modulefilename"), String))
      categoryItem.ChildItems.Add(childrenItem)
      childrenItem.NavigateUrl = childItem("modulefilename").ToString


   Next


Next



Dim cItem As New MenuItem(DirectCast("Logout", String))

mmenu.Items.Add(cItem)


cItem.NavigateUrl = "logout.aspx"


Panel1.Controls.Add(mmenu)


Panel1.DataBind()

End Sub


Private Function GetDataSetForMenu() As DataSet



Dim myConnection As New SqlConnection(SqlDataSource1.ConnectionString)
Dim adCat As New SqlDataAdapter("SELECT distinct modulecat, modulecatseq FROM UserProfileQueryNew where muserid='" & useridlbl.Text & "' order by modulecatseq", myConnection)
Dim adProd As New SqlDataAdapter("SELECT * FROM userprofilequerynew where muserid='" & useridlbl.Text & "'", myConnection)
Dim ds As New DataSet()
adCat.Fill(ds, "mdesc")
adProd.Fill(ds, "mchild")
ds.Relations.Add("Children", ds.Tables("mdesc").Columns("modulecat"), ds.Tables("mchild").Columns("modulecat"))


Return ds

End Function