[問題] 從SQL欄位判來產生Menu
請教各位大大
目前在畫面上做了寫死的動態menu
web:https://imgur.com/D2ceHQW
SQL table:https://imgur.com/lX9J3W1
希望能改用從SQL來的資料做menu能依照table裡欄位(單位編號=001 & 執行狀態=1 顯示)
目前想到作法是這樣，沒寫過VB不知道可不可行，又或者有其他建議?
1.對SQL做逐步select 有效狀態的table(單位編號=001 & 執行狀態=1 顯示)
2.取出有效資料代入子表中
3.子表跟母表進行合併
4.返回GetDate()
請問該如何改asp.net下面的code?
Imports System.Data.SqlClient
Imports System.Data
Partial Class VBASPNETMenu
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
If Not IsPostBack Then
GenerateMenuItem()
End If
End Sub
Public Sub GenerateMenuItem()
' 從數據庫中獲取數據。
Dim ds As DataSet = GetData()
For Each mainRow As DataRow In ds.Tables(0).Rows
' 將主表中的記錄加載到菜單控件。
Dim masterItem As New MenuItem(mainRow("mainName").ToString())
masterItem.NavigateUrl = mainRow("mainUrl").ToString()
Menu1.Items.Add(masterItem)
Menu1.Visible = True
For Each childRow As DataRow In mainRow.GetChildRows("Child")
' 根據主表和子表的關係，從子表中加載數據。
Dim childItem As New
MenuItem(DirectCast(childRow("childName"), String))
childItem.NavigateUrl = childRow("childUrl").ToString()
masterItem.ChildItems.Add(childItem)
Next
Next
End Sub
Public Function GetData() As DataSet
'SQL???迴圈跑子表???
' 為了測試，使用內存表作為數據源。
Dim mainTB As New DataTable()
Dim mainIdCol As New DataColumn("mainId")
Dim mainNameCol As New DataColumn("mainName")
Dim mainUrlCol As New DataColumn("mainUrl")
mainTB.Columns.Add(mainIdCol)
mainTB.Columns.Add(mainNameCol)
mainTB.Columns.Add(mainUrlCol)
Dim childTB As New DataTable()
Dim childIdCol As New DataColumn("childId")
Dim childNameCol As New DataColumn("childName")
' 子表的MainId列是主表的外鍵。
Dim childMainIdCol As New DataColumn("MainId")
Dim childUrlCol As New DataColumn("childUrl")
childTB.Columns.Add(childIdCol)
childTB.Columns.Add(childNameCol)
childTB.Columns.Add(childMainIdCol)
childTB.Columns.Add(childUrlCol)
' 將一些測試記錄插入到主表中。
Dim dr As DataRow = mainTB.NewRow()
dr(0) = "1"
dr(1) = "菜單管理"
dr(2) = "Test.aspx"
mainTB.Rows.Add(dr)
Dim dr1 As DataRow = mainTB.NewRow()
dr1(0) = "2"
dr1(1) = "新人訓練"
dr1(2) = "Test.aspx"
mainTB.Rows.Add(dr1)
Dim dr2 As DataRow = mainTB.NewRow()
dr2(0) = "3"
dr2(1) = "薪俸發放"
dr2(2) = "Test.aspx"
mainTB.Rows.Add(dr2)
Dim dr3 As DataRow = mainTB.NewRow()
dr3(0) = "4"
dr3(1) = "食材管理"
dr3(2) = "Test.aspx"
mainTB.Rows.Add(dr3)
Dim dr4 As DataRow = mainTB.NewRow()
dr4(0) = "5"
dr4(1) = "人事管理"
dr4(2) = "Test.aspx"
mainTB.Rows.Add(dr4)
'將一些測試記錄插入到子表中
Dim dr_child1 As DataRow = childTB.NewRow()
dr_child1(0) = "0"
dr_child1(1) = "1.T0101"
dr_child1(2) = "1"
dr_child1(3) = "Test.aspx"
childTB.Rows.Add(dr_child1)
Dim dr_child2 As DataRow = childTB.NewRow()
dr_child2(0) = "1"
dr_child2(1) = "2.T0103"
dr_child2(2) = "1"
dr_child2(3) = "Test.aspx"
childTB.Rows.Add(dr_child2)
Dim dr6 As DataRow = childTB.NewRow()
dr6(0) = "0"
dr6(1) = "T0201"
dr6(2) = "2"
dr6(3) = "Test.aspx"
childTB.Rows.Add(dr6)
Dim dr7 As DataRow = childTB.NewRow()
dr7(0) = "1"
dr7(1) = "T0202"
dr7(2) = "2"
dr7(3) = "Test.aspx"
childTB.Rows.Add(dr7)
' 使用DataSet來包含這兩個表
Dim ds As New DataSet()
ds.Tables.Add(mainTB)
ds.Tables.Add(childTB)
' 建立主表和子表之間的關係
ds.Relations.Add("Child", ds.Tables(0).Columns("mainId"),
ds.Tables(1).Columns("MainId"))
Return ds
End Function
End Class
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 106.107.130.237
※ 文章網址: https://www.ptt.cc/bbs/Web_Design/M.1527507286.A.903.html
→
05/29 11:44,
6年前
, 1F
05/29 11:44, 1F
推
05/30 22:09,
6年前
, 2F
05/30 22:09, 2F
→
05/30 22:11,
6年前
, 3F
05/30 22:11, 3F
→
05/30 22:13,
6年前
, 4F
05/30 22:13, 4F