無限分類 (Infinite Category) 是電商、CMS、OA 系統的核心結構。資料表僅需 `Id`、`ParentId`、`Name` 三欄,即可透過遞歸構建樹狀結構。本文提供兩種經典 ASP.NET WebForms 綁定實作:`TreeView` 遞歸建節點、 `DropDownList` 縮排顯示,並討論效能優化與非遞歸替代方案。
CREATE TABLE Categories (
Id INT PRIMARY KEY IDENTITY,
ParentId INT NOT NULL DEFAULT 0, -- 0 表根節點
Name NVARCHAR(100) NOT NULL,
SortOrder INT DEFAULT 0,
Depth AS ( -- 計算欄位 (SQL Server 2012+)
CASE WHEN ParentId = 0 THEN 0
ELSE (SELECT Depth + 1 FROM Categories c2 WHERE c2.Id = Categories.ParentId) END
) PERSISTED
);
public partial class CategoryTree : Page {
protected void Page_Load(object sender, EventArgs e) {
if (!IsPostBack) BindTree("0", null);
}
private void BindTree(string parentId, TreeNode parentNode) {
var dt = CategoryDAL.GetChildren(parentId); // SELECT * FROM Categories WHERE ParentId = @ParentId ORDER BY SortOrder
foreach (DataRow row in dt.Rows) {
var node = new TreeNode {
Text = row["Name"].ToString(),
Value = row["Id"].ToString(),
ToolTip = row["Id"].ToString(),
PopulateOnDemand = true // 延遲載入子節點 (大量資料必備)
};
if (parentNode == null) TreeView1.Nodes.Add(node);
else parentNode.ChildNodes.Add(node);
// 非延遲載入時遞歸 (小資料量)
if (!node.PopulateOnDemand) BindTree(node.Value, node);
}
}
// PopulateOnDemand 事件:使用者展開時才載入子節點
protected void TreeView1_TreeNodePopulate(object sender, TreeNodeEventArgs e) {
BindTree(e.Node.Value, e.Node);
e.Node.PopulateOnDemand = false; // 已載入,關閉延遲
}
}
private void BindDropDownList(string parentId, string prefix) {
var dt = CategoryDAL.GetChildren(parentId);
foreach (DataRow row in dt.Rows) {
string name = row["Name"].ToString();
string id = row["Id"].ToString();
ddlCategory.Items.Add(new ListItem(prefix + name, id));
BindDropDownList(id, prefix + "└ "); // 遞歸加深縮排
}
}
// 頁面載入
protected void Page_Load(object sender, EventArgs e) {
if (!IsPostBack) {
ddlCategory.Items.Clear();
ddlCategory.Items.Add(new ListItem("請選擇分類", "0"));
BindDropDownList("0", "");
}
}
上述遞歸每層查詢一次資料庫,深度為 D 時產生 D 次查詢。優化方案:
var all = CategoryDAL.GetAll(); // DataTable
var lookup = all.AsEnumerable().ToLookup(r => r["ParentId"].ToString());
Action build = null;
build = (pid, parent, prefix) => {
foreach (var row in lookup[pid]) {
var node = new TreeNode(row["Name"].ToString(), row["Id"].ToString());
if (parent == null) TreeView1.Nodes.Add(node); else parent.ChildNodes.Add(node);
build(row["Id"].ToString(), node, prefix + "└ ");
}
};
build("0", null, "");
WITH CategoryTree AS (
SELECT Id, ParentId, Name, 0 AS Level, CAST(Name AS NVARCHAR(MAX)) AS Path
FROM Categories WHERE ParentId = 0
UNION ALL
SELECT c.Id, c.ParentId, c.Name, ct.Level + 1, ct.Path + ' > ' + c.Name
FROM Categories c INNER JOIN CategoryTree ct ON c.ParentId = ct.Id
)
SELECT * FROM CategoryTree ORDER BY Path;
單次查詢取得完整層級與路徑,前端直接綁定 `Repeater`/`ListView` 配合 `Level` 渲染縮排。
public void BuildTreeIterative(string rootId, TreeNode rootNode) {
var stack = new Stack<(string ParentId, TreeNode ParentNode)>();
stack.Push((rootId, rootNode));
var all = CategoryDAL.GetAll().AsEnumerable().ToLookup(r => r["ParentId"].ToString());
while (stack.Count > 0) {
var (pid, parent) = stack.Pop();
foreach (var row in all[pid]) {
var node = new TreeNode(row["Name"].ToString(), row["Id"].ToString());
if (parent == null) TreeView1.Nodes.Add(node); else parent.ChildNodes.Add(node);
if (all.Contains(row["Id"].ToString())) stack.Push((row["Id"].ToString(), node));
}
}
}
現代專案建議:後端提供 **扁平化 JSON** (含 `id, parentId, text`),前端用 **jsTree** 或 **Ant Design Tree** 渲染,支援搜尋、拖曳、懶加載、複選、虛擬滾動,體驗遠勝 WebForms 控制項。
// WebMethod 回傳扁平 JSON
[WebMethod]
public static List GetCategoryTree() {
return CategoryDAL.GetAll().AsEnumerable()
.Select(r => new TreeNodeDto {
id = r["Id"].ToString(),
parent = r["ParentId"].ToString() == "0" ? "#" : r["ParentId"].ToString(),
text = r["Name"].ToString()
}).ToList();
}
理解遞歸與迭代、資料庫與記憶體建樹的權衡,能根據資料量與互動需求選擇最適合的無限分類實作策略。