简单无限级表结构: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 @t WHERE pid = @pidValue AND 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 @t WHERE pid = @pidValue AND 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 - 1 AND 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 - 1 AND 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 ORDER BY 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 ORDER BY 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;
public partial class _Default : System.Web.UI.Page
{
protected void 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;
public partial class _Default : System.Web.UI.Page
{
protected void 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字段。
以上就是本篇内容。