PTT數位生活區 / Visual_Basic

[.NET] 從SQL欄位判斷產生Menu

看板Visual_Basic作者qwerty147852 (小城主)時間6年前 (2018/05/28 19:50)推噓0(0推 0噓 4→)

留言4則, 1人參與討論串1/1

請教各位大大目前在畫面上做了寫死的動態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://www.ptt.cc/bbs/Visual_Basic/M.1527508213.A.CFB.html ※ 編輯: qwerty147852 (106.107.130.237), 05/28/2018 19:52:54