~楓花雪岳~: [C#] TreeView for WebForm

星期二, 10月 28, 2014

[C#] TreeView for WebForm

利用組織圖來練習 ASP.NET TreeView 控件，主要是應用 SQL Server CTE 語法組成名稱路徑配合 ValuePath 屬性來完成 TreeView

SQL Server 資料建立

原始資料截圖

USE AdventureWorks2012
GO

IF OBJECT_ID('Department') IS NOT NULL
  DROP TABLE Department

CREATE TABLE Department (DepID char(2),ParentID char(2),DepName nchar(50))

INSERT INTO Department VALUES
  ('01',NULL,N'公司'),
      ('02','01',N'財務'),
      ('03','01',N'行政'),
          ('04','03',N'採購'),
          ('05','03',N'人資'),
          ('06','03',N'業務'),
          ('07','03',N'技術服務'),
      ('08','01',N'開發'),
          ('09','08',N'企劃'),
          ('10','08',N'品管'),
      ('11','01',N'廠務'),
          ('12','11',N'生產技術'),
          ('13','11',N'製程保全'),
          ('14','11',N'生產管理'),
          ('15','11',N'廠務室 A'),
              ('16','15',N'倉庫A'),
              ('17','15',N'板噴生產課'),
                  ('18','17',N'端板成型組'),
                  ('19','17',N'箱體塗裝組'),
              ('20','15',N'熱交生產課'),
                  ('21','20',N'管件組'),
                  ('22','20',N'沖片組'),
                  ('23','20',N'回管組'),
                  ('24','20',N'氣焊組'),
              ('25','15',N'裝配生產課'),
                  ('26','25',N'裝配一組'),
                  ('27','25',N'裝配二組'),
          ('28','11',N'廠務室 B'),
                  ('29','28',N'倉庫 B'),
                  ('30','28',N'資材課'),
                  ('31','28',N'板金生產課'),
                      ('32','31',N'CNC 組')
GO

SELECT * FROM Department
GO

IF OBJECT_ID('uspTreeViewData') IS NOT NULL
  DROP PROCEDURE uspTreeViewData
GO

CREATE PROCEDURE uspTreeViewData
AS
    BEGIN

      ; 
      WITH CTE AS
      (
          SELECT 
              DepID , 
              ParentID , 
              DepName,
              CAST(DepID AS NVARCHAR(200)) AS NamePath,
              0 AS lvl ,
              CAST(REPLICATE(space(10),0) + DepName AS NVARCHAR(200)) AS NameTree
          FROM Department
          WHERE ParentID IS NULL
          UNION ALL
          SELECT
              D.DepID , 
              D.ParentID , 
              D.DepName,
              CAST(RTRIM(T.NamePath) + '_' + RTRIM(D.DepID) AS NVARCHAR(200)),
              T.lvl + 1 ,
              CAST(REPLICATE(SPACE(10) , T.lvl + 1)  + D.DepName AS NVARCHAR(200))
          FROM CTE AS T
              JOIN Department AS D ON T.DepID = D.ParentID
      )
      SELECT * 
      FROM CTE
      ORDER BY NamePath

    END
GO

EXEC uspTreeViewData

跑完 CTE 後截圖

ASP.NET C# 語法

using System.Data;
using System.Data.SqlClient;

namespace ASPTreeView
{
    public partial class TreeView_CTE : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                // 設定 TreeView Node 的 ValuePath 分隔符號，預設是 /，
                // 故意設成跟 SQL Server CTE 路經一樣的分隔符號 _
                TreeView1.PathSeparator = Convert.ToChar("_");
                // 顯示父 Node 和 子 Node 的關聯線
                TreeView1.ShowLines = true;

                getTreeview();
            }
        }

        private void getTreeview()
        {
            DataTable dt = getData();

            string NamePath = string.Empty;
            int index = -1;
            string ParentPath = string.Empty;

            foreach (DataRow dr in dt.Rows)
            {
                // node 相關資訊
                TreeNode node = new TreeNode();
                node.Text = dr["DepName"].ToString();
                node.Value = dr["DepID"].ToString();
                node.Expanded = true;

                // 找出該 Node 的父 Node 的 ValuePath
                NamePath = dr["NamePath"].ToString();
                index = NamePath.LastIndexOf("_") == -1 ? 0 : NamePath.LastIndexOf("_");
                ParentPath = NamePath.Substring(0, index).Trim();

                // ParentID 為 NULL 代表根結點
                if (dr.IsNull("ParentID"))
                {
                    TreeView1.Nodes.Add(node);
                }
                else
                {
                    // 非根結點，則利用 FindNode 方法找出該筆資料的父 Node，並新增至該父 Node 下
                    TreeNode parentNode = TreeView1.FindNode(ParentPath);
                    if (parentNode == null) continue;
                    parentNode.ChildNodes.Add(node);
                }
            }
        }

        // 利用 ADO.NET 抓取資料
        private DataTable getData()
        {

            string connString = @"Data Source=ServerName\SQL2012;Initial Catalog=AdventureWorks2012;Integrated Security=True";

            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "uspTreeViewData";

                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;

                DataTable dt = new DataTable();

                da.Fill(dt);
                return dt;
            }
        }
    }
}