SQL Server 上設定 Script
-- 在 SQL Server 2014 Express 上的 AdventrueWork2012 進行
USE [AdventureWorks2012]
GO
-- 建立 Table Schema
IF OBJECT_ID('MenuStrip') IS NOT NULL
DROP TABLE MenuStrip
CREATE TABLE [dbo].[MenuStrip](
[MenuNO] [char](2) NOT NULL CONSTRAINT [DF_MenuStrip_MenuNO] DEFAULT (''),
[ParentID] [char](20) NOT NULL CONSTRAINT [DF_MenuStrip_ParentID] DEFAULT (''),
[ParentOrder] [tinyint] NOT NULL CONSTRAINT [DF_MenuStrip_ParentOrder] DEFAULT ((0)),
[ChildID] [char](20) NOT NULL CONSTRAINT [DF_MenuStrip_ChildID] DEFAULT (''),
[ChildName] [nchar](40) NOT NULL CONSTRAINT [DF_MenuStrip_ChildName] DEFAULT (''),
[ChildOrder] [tinyint] NOT NULL CONSTRAINT [DF_MenuStrip_ChildOrder] DEFAULT ((0)),
CONSTRAINT [PK_MenuStrip] PRIMARY KEY CLUSTERED
(
[ChildID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- 建立測試資料，從 VS 2013 功能表中挑幾個選項出來建立資料
INSERT INTO MenuStrip (MenuNO , ParentID , ParentOrder , ChildID , ChildName , ChildOrder) VALUES
('01','R',1,'Files',N'檔案',0),
('01','Files',1,'NewFile',N'新增',1),
('01','NewFile',1,'Project',N'專案',1),
('01','Files',1,'OpenFile',N'開啟',2),
('01','Files',1,'End',N'結束',3),
('01','R',2,'Edit',N'編輯',0),
('01','Edit',2,'Cut',N'剪下',1),
('01','Edit',2,'Paste',N'貼上',2),
('01','Edit',2,'SearchReplace',N'尋找和取代',3),
('01','SearchReplace',2,'QuitSearch',N'快速尋找',1),
('01','SearchReplace',2,'QuitReplace',N'快速取代',2)
GO
-- 建立 Store Procedure
IF OBJECT_ID('uspDataFill') IS NOT NULL
DROP PROCEDURE uspDataFill
GO
-- 利用 SQL Server CTE 跑出樹狀圖（TreePath 欄位），只是為了方便了解資料路徑，在 C# 中並不會使用到它
CREATE PROCEDURE uspDataFill (@MenuNO char(2))
AS
BEGIN
;
WITH CTE AS
(
SELECT
MenuNO ,
ParentID ,
ParentOrder ,
ChildID ,
ChildName ,
ChildOrder ,
CAST(RTRIM(ParentID) + CAST(ParentOrder AS char(1)) + '_' + CAST(ChildOrder AS CHAR(1)) + ChildName AS nVARCHAR(200)) AS TreePath
FROM MenuStrip
WHERE MenuNO = @MenuNO
AND ParentID = 'R'
UNION ALL
SELECT
MS.MenuNO ,
MS.ParentID ,
MS.ParentOrder ,
MS.ChildID ,
MS.ChildName ,
MS.ChildOrder ,
CAST(RTRIM(T.TreePath) + '_' + CAST(MS.ChildOrder AS CHAR(1)) + MS.ChildName AS NVARCHAR(200))
FROM CTE AS T
JOIN MenuStrip AS MS ON T.MenuNO = MS.MenuNO
AND T.ChildID = MS.ParentID
)
SELECT *
FROM CTE
ORDER BY TreePath
END
GO
-- 觀察 CTE 產生的 TreePath 資料
EXEC uspDataFill '01'
![[C#] 動態建立 MenuStrip-1](https://farm6.staticflickr.com/5582/14698948358_d360f2f7e8_z.jpg)
C# 語法
// 引用該命名空間使用
using System.Data.SqlClient;
namespace DynamicMenuStrip
{
public partial class DynamicMenuStrip : Form
{
public DynamicMenuStrip()
{
InitializeComponent();
}
private void DynamicMenuStrip_Load(object sender, EventArgs e)
{
// 資料來源
DataTable dt = dataFill("01");
//DataGridView 的基礎設定
dgvData.DataSource = dt;
dgvData.AllowUserToAddRows = false;
dgvData.MultiSelect = true;
dgvData.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dgvData.ClearSelection();
// 建立 Menu
menuCreate(this, dt);
}
private void menuCreate(Form _form, DataTable _dt)
{
if (_dt.Rows.Count == 0) return;
MenuStrip ms = new MenuStrip();
ms.Dock = DockStyle.Top;
ms.BackColor = Color.LightGray;
_form.Controls.Add(ms);
// 判斷 ParentID == "R" 的資料
IEnumerable<datarow> drs = _dt.AsEnumerable().Where(data => data.Field<string>("ParentID").Trim() == "R").OrderBy(data => data.Field<byte>("ParentOrder"));
if (drs.Count<datarow>() == 0) return;
foreach (DataRow dr in drs)
{
ToolStripMenuItem ctlChild = new ToolStripMenuItem()
{
Name = dr["ChildID"].ToString().Trim(),
Text = dr["ChildName"].ToString().Trim()
};
ctlChild.Click += MenuClick;
ms.Items.Add(ctlChild);
// 判斷是否還有 subMenu
submenuCreate(ctlChild, _dt);
}
}
private void submenuCreate(ToolStripMenuItem _item, DataTable _dt)
{
// 判斷是否有資料的父結點為 _Item
IEnumerable<datarow> drs = _dt.AsEnumerable().Where(data => data.Field<string>("ParentID").Trim() == _item.Name.Trim()).OrderBy(data => data.Field<byte>("ChildOrder"));
if (drs.Count<datarow>() == 0) return;
foreach (DataRow dr in drs)
{
ToolStripMenuItem ctlChild = new ToolStripMenuItem()
{
Name = dr["ChildID"].ToString().Trim(),
Text = dr["ChildName"].ToString().Trim()
};
ctlChild.Click += MenuClick;
_item.DropDownItems.Add(ctlChild);
// 判斷是否還有 subMenu
submenuCreate(ctlChild, _dt);
}
}
void MenuClick(object sender, EventArgs e)
{
// 點選 Menu 上任一個選項，必須 Highlight DataGridView 上該筆資料
string name = (sender as ToolStripMenuItem).Name;
foreach (DataGridViewRow r in dgvData.Rows)
{
if (r.Cells["ChildID"].Value == null) continue;
// 利用 Primary Key - ChildID 判斷，是否和點選的 Menu 選項一致
r.Selected = (r.Cells["ChildID"].Value.ToString().Trim() == name) ? true : false;
}
}
private DataTable dataFill(string _MenuNO)
{
string connString = @"Data Source=NB\SQL2014;Initial Catalog=AdventureWorks2012;Integrated Security=True";
DataTable dt = new DataTable("MenuStrip");
using (SqlConnection conn = new SqlConnection(connString))
{
try
{
// 設定使用 Store Procedure
SqlCommand cmd = new SqlCommand("uspDataFill", conn);
// 下面兩種設定參數方式，擇一使用
cmd.Parameters.Add(new SqlParameter("@MenuNO", _MenuNO));
// cmd.Parameters.AddWithValue("@MenuNO", _MenuNO);
cmd.CommandType = CommandType.StoredProcedure;
// SqlDataAdapter 會自動把連線開啟和關閉
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
}
return dt;
}
}
}
![[C#] 動態建立 MenuStrip](https://farm4.staticflickr.com/3915/14698095907_16203fe595_z.jpg)
沒有留言:
張貼留言