作者qwerty147852 (小城主)
看板Visual_Basic
標題[.NET] 從SQL欄位判斷產生Menu
時間Mon May 28 19:50:08 2018
請教各位大大
目前在畫面上做了寫死的動態menu
web:
https://imgur.com/D2ceHQW
code:
https://imgur.com/mikTb4r
SQL table:
https://imgur.com/lX9J3W1
SQL:"select 表單編號 from menu_1 where 單位編號='1' and 執行狀態='1'"
篩選出有效資料
希望能改用從SQL來的資料做menu能依照SQL table裡欄位(單位編號=001 & 執行狀態=1 顯示)
EX:menu 導覽列有五大項目
●菜單管理
-T1001
-T1002
●新人訓練
-T2001
-T2003
目前想到作法是這樣，沒寫過VB不知道可不可行，又或者有其他建議?
1.對SQL做逐步select 有效狀態的table(單位編號=001 & 執行狀態=1 顯示)
SQL:"select 表單編號 from menu_1 where 單位編號='1' and 執行狀態='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://webptt.com/m.aspx?n=bbs/Visual_Basic/M.1527508213.A.CFB.html
※ 編輯: qwerty147852 (106.107.130.237), 05/28/2018 19:52:54
1F：→ MOONRAKER: 為什麼你的imgur要開這麼久 05/28 22:26
2F：→ MOONRAKER: 你選的範例太難了 05/28 22:30
3F：→ MOONRAKER: 動態生子資料表出來 不要說你不會改 實務上沒人這樣弄 05/28 22:30
4F：→ MOONRAKER: 先找一個比較適合你的範例 不然教你太累 05/28 22:31