（无限级、递归）DropDownList显示树形分类
简单无限级表结构：id，name，pid。
本篇将展示如何在DropDownList控件显示一个漂亮的树结构。
1.以下我们建立无限级树的表结构
--星辰变的修真等级表
CREATE TABLE XianMO
(
XianMoID INT NOT NULL ,
XianMoLevel VARCHAR(50) NOT NULL DEFAULT(''),
XianMoParentID INT NOT NULL DEFAULT(0)
)
GO
--索引
CREATE NONCLUSTERED INDEX IX_XianMo ON XianMo(XianMoID)
GO
CREATE TABLE XianMO
(
XianMoID INT NOT NULL ,
XianMoLevel VARCHAR(50) NOT NULL DEFAULT(''),
XianMoParentID INT NOT NULL DEFAULT(0)
)
GO
--索引
CREATE NONCLUSTERED INDEX IX_XianMo ON XianMo(XianMoID)
GO
2.有了表结构，我们将添加一些数据。^_^
INSERT INTO XianMo(XianMoID,XianMoLevel,XianMoParentID)
SELECT 1,'凡人',0
UNION ALL SELECT 2,'修真者',0
UNION ALL SELECT 3,'后天境界',2
UNION ALL SELECT 4,'先天境界(四九小天劫)',2
UNION ALL SELECT 5,'金丹期',2
UNION ALL SELECT 6,'元婴期(六九大天劫)',2
UNION ALL SELECT 7,'洞虚期',2
UNION ALL SELECT 8,'空冥期',2
UNION ALL SELECT 9,'渡劫期(九九重劫)',2
UNION ALL SELECT 10,'大乘期',2
UNION ALL SELECT 11,'仙人',0
UNION ALL SELECT 12,'散仙',11
UNION ALL SELECT 13,'天仙',11
UNION ALL SELECT 14,'金仙',11
UNION ALL SELECT 15,'玄仙',11
UNION ALL SELECT 16,'仙帝',11
UNION ALL SELECT 17,'神人',0
UNION ALL SELECT 18,'下级神人',17
UNION ALL SELECT 19,'中级神人',17
UNION ALL SELECT 20,'上级神人',17
UNION ALL SELECT 21,'天神',0
UNION ALL SELECT 22,'下部天神',21
UNION ALL SELECT 23,'中部天神',21
UNION ALL SELECT 24,'上部天神',21
UNION ALL SELECT 25,'神王',0
UNION ALL SELECT 26,'天尊',0
UNION ALL SELECT 27,'鸿蒙',0
GO
UNION ALL SELECT 2,'修真者',0
UNION ALL SELECT 3,'后天境界',2
UNION ALL SELECT 4,'先天境界(四九小天劫)',2
UNION ALL SELECT 5,'金丹期',2
UNION ALL SELECT 6,'元婴期(六九大天劫)',2
UNION ALL SELECT 7,'洞虚期',2
UNION ALL SELECT 8,'空冥期',2
UNION ALL SELECT 9,'渡劫期(九九重劫)',2
UNION ALL SELECT 10,'大乘期',2
UNION ALL SELECT 11,'仙人',0
UNION ALL SELECT 12,'散仙',11
UNION ALL SELECT 13,'天仙',11
UNION ALL SELECT 14,'金仙',11
UNION ALL SELECT 15,'玄仙',11
UNION ALL SELECT 16,'仙帝',11
UNION ALL SELECT 17,'神人',0
UNION ALL SELECT 18,'下级神人',17
UNION ALL SELECT 19,'中级神人',17
UNION ALL SELECT 20,'上级神人',17
UNION ALL SELECT 21,'天神',0
UNION ALL SELECT 22,'下部天神',21
UNION ALL SELECT 23,'中部天神',21
UNION ALL SELECT 24,'上部天神',21
UNION ALL SELECT 25,'神王',0
UNION ALL SELECT 26,'天尊',0
UNION ALL SELECT 27,'鸿蒙',0
GO
3.创建一个存储过程，我们在这里就已经把树结构完整的展示出来了。
-- =============================================
-- Author: Star Studio
-- Create date: 2010-09-10
-- Description: Tree In DropDownList
-- =============================================
ALTER PROCEDURE[dbo].[GetTree]
@tableName VARCHAR(100),--表名
@idName VARCHAR(100),--id字段名
@className VARCHAR(100),--class字段名
@pidName VARCHAR(100),--父id字段名
@pidValue INT,--指定此次构造树的根节点
@idExcepted INT--此次构造的树，将排除显示的节点及其子节点。这在修改节点的父关系时很有用。你不可以指定某节点的老爸是自己或自己的儿子吧。所以这时就要屏蔽掉自己及子节点
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Level INT
DECLARE @strTree VARCHAR(8000)
DECLARE @t TABLE(id INT,class VARCHAR(100), pid INT)
DECLARE @ttemp TABLE(id INT,class VARCHAR(100),pid INT,lev INT,codeid VARCHAR(8000))
SET @Level=0
SET @strTree='┊┈┈'
INSERT INTO @t EXEC('SELECT '+@idName+' AS id,'+@className+' AS class,'+@pidName+' AS pid FROM '+@tableName+'')
-- Author: Star Studio
-- Create date: 2010-09-10
-- Description: Tree In DropDownList
-- =============================================
ALTER PROCEDURE[dbo].[GetTree]
@tableName VARCHAR(100),--表名
@idName VARCHAR(100),--id字段名
@className VARCHAR(100),--class字段名
@pidName VARCHAR(100),--父id字段名
@pidValue INT,--指定此次构造树的根节点
@idExcepted INT--此次构造的树，将排除显示的节点及其子节点。这在修改节点的父关系时很有用。你不可以指定某节点的老爸是自己或自己的儿子吧。所以这时就要屏蔽掉自己及子节点
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Level INT
DECLARE @strTree VARCHAR(8000)
DECLARE @t TABLE(id INT,class VARCHAR(100), pid INT)
DECLARE @ttemp TABLE(id INT,class VARCHAR(100),pid INT,lev INT,codeid VARCHAR(8000))
SET @Level=0
SET @strTree='┊┈┈'
INSERT INTO @t EXEC('SELECT '+@idName+' AS id,'+@className+' AS class,'+@pidName+' AS pid FROM '+@tableName+'')
--排除的节点有可能是根节点
INSERT INTO @ttemp SELECT id,@strTree+class,pid,@level,right(10000+id,4) FROM@tWHERE pid=@pidValueAND id<>@idExcepted
WHILE @@rowcount>0--一层层递归构造排序字段codeid
BEGIN
SET @strTree=replace(@strTree,'┈','')
SET @strTree=@strTree+'┊┈┈'
SET @level=@level+1
IF(@idExcepted=0)
BEGIN
INSERT @ttemp SELECT a.id,@strTree+a.class,a.pid,@level,b.codeid+right(10000+a.id,4) FROM@t a,@ttemp b
WHERE a.pid=b.id AND b.lev=@level-1
END
ELSE
BEGIN
INSERT INTO @ttemp SELECT id,@strTree+class,pid,@level,right(10000+id,4) FROM@tWHERE pid=@pidValueAND id<>@idExcepted
WHILE @@rowcount>0--一层层递归构造排序字段codeid
BEGIN
SET @strTree=replace(@strTree,'┈','')
SET @strTree=@strTree+'┊┈┈'
SET @level=@level+1
IF(@idExcepted=0)
BEGIN
INSERT @ttemp SELECT a.id,@strTree+a.class,a.pid,@level,b.codeid+right(10000+a.id,4) FROM@t a,@ttemp b
WHERE a.pid=b.id AND b.lev=@level-1
END
ELSE
BEGIN
--排除的节点是某个子节点
INSERT @ttemp SELECT a.id,@strTree+a.class,a.pid,@level,b.codeid+right(10000+a.id,4) FROM@t a,@ttemp b
WHERE a.pid=b.id AND b.lev=@level-1AND b.id<>@idExcepted
END
END
INSERT @ttemp SELECT a.id,@strTree+a.class,a.pid,@level,b.codeid+right(10000+a.id,4) FROM@t a,@ttemp b
WHERE a.pid=b.id AND b.lev=@level-1AND b.id<>@idExcepted
END
END
--只要按codeid排列结果集，就能得到正确完整的树结构
SELECT a.id,a.pid,b.class,b.lev,b.codeid FROM @t a,@ttemp b WHERE a.id=b.id ORDERBY b.codeid
END
GO
--事例演示
exec gettree 'XianMo','xianmoid','xianmolevel','xianmoparentid',0,11
SELECT a.id,a.pid,b.class,b.lev,b.codeid FROM @t a,@ttemp b WHERE a.id=b.id ORDERBY b.codeid
END
GO
--事例演示
exec gettree 'XianMo','xianmoid','xianmolevel','xianmoparentid',0,11
演示事例一及其结果集：
--不排除任何一个节点
exec gettree 'XianMo','xianmoid','xianmolevel','xianmoparentid',0,0
exec gettree 'XianMo','xianmoid','xianmolevel','xianmoparentid',0,0
演示事例二及其结果集：
--排除仙人节点
exec gettree 'XianMo','xianmoid','xianmolevel','xianmoparentid',0,11
exec gettree 'XianMo','xianmoid','xianmolevel','xianmoparentid',0,11
看了以上两图，你应该知道，该存储过程的原理是通过递归构造一个排序字段codeid,然后根据codeid排序就得到完整的属性排列了。
4.接下来只要把数据放入DropDownList，就能显示一颗漂亮的树了。
<!--DropDownListTree.aspx-->
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DropDownListTree.aspx.cs" Inherits="_Default"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>DropDownList显示树结构</title>
<style type="text/css">
#TreeBlock{width:200px;}
#TreeBlock #ddlXianMoTree{background-color:#FFAA2B; }
</style>
</head>
<body>
<form id="form1" runat="server">
<div id="TreeBlock">
<asp:DropDownList ID="ddlXianMoTree" runat="server"></asp:DropDownList>
</div>
</form>
</body>
</html>
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DropDownListTree.aspx.cs" Inherits="_Default"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>DropDownList显示树结构</title>
<style type="text/css">
#TreeBlock{width:200px;}
#TreeBlock #ddlXianMoTree{background-color:#FFAA2B; }
</style>
</head>
<body>
<form id="form1" runat="server">
<div id="TreeBlock">
<asp:DropDownList ID="ddlXianMoTree" runat="server"></asp:DropDownList>
</div>
</form>
</body>
</html>
//DropDownListTree.aspx.cs
using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using WnetSoft.Common.DBHelper;
publicpartialclass _Default : System.Web.UI.Page
{
protectedvoid Page_Load(object sender, EventArgs e)
{
SqlParameter[] SqlParamList ={
SqlHelper.MakeInParam("@tableName",SqlDbType.VarChar,100,"XianMo"),
SqlHelper.MakeInParam("@idName",SqlDbType.VarChar,100,"XianMoID"),
SqlHelper.MakeInParam("@className",SqlDbType.VarChar,100,"XianMoLevel"),
SqlHelper.MakeInParam("@pidName",SqlDbType.VarChar,100,"XianMoParentID"),
SqlHelper.MakeInParam("@pidValue",SqlDbType.Int,10,0),
SqlHelper.MakeInParam("@idExcepted",SqlDbType.Int,10,0)
};
ddlXianMoTree.DataSource = SqlHelper.ExecuteReader(SqlHelper.connString, CommandType.StoredProcedure, "GetTree", SqlParamList);
ddlXianMoTree.DataTextField ="class";
ddlXianMoTree.DataValueField ="id";
ddlXianMoTree.DataBind();
ddlXianMoTree.Items.Insert(0, new ListItem("--根节点--", "0"));
}
}
using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using WnetSoft.Common.DBHelper;
publicpartialclass _Default : System.Web.UI.Page
{
protectedvoid Page_Load(object sender, EventArgs e)
{
SqlParameter[] SqlParamList ={
SqlHelper.MakeInParam("@tableName",SqlDbType.VarChar,100,"XianMo"),
SqlHelper.MakeInParam("@idName",SqlDbType.VarChar,100,"XianMoID"),
SqlHelper.MakeInParam("@className",SqlDbType.VarChar,100,"XianMoLevel"),
SqlHelper.MakeInParam("@pidName",SqlDbType.VarChar,100,"XianMoParentID"),
SqlHelper.MakeInParam("@pidValue",SqlDbType.Int,10,0),
SqlHelper.MakeInParam("@idExcepted",SqlDbType.Int,10,0)
};
ddlXianMoTree.DataSource = SqlHelper.ExecuteReader(SqlHelper.connString, CommandType.StoredProcedure, "GetTree", SqlParamList);
ddlXianMoTree.DataTextField ="class";
ddlXianMoTree.DataValueField ="id";
ddlXianMoTree.DataBind();
ddlXianMoTree.Items.Insert(0, new ListItem("--根节点--", "0"));
}
}
有点遗憾是，我传入XianMoID，XianMoLevel字段，返回的结果是id,class字段。
以上就是本篇内容。
