数据库里的表如下:
科目编码 名称 上级科目编码
a1 aa R
a2 bb R
a3 cc a1
a4 dd a1
a5 ee a3
a6 ff a5
......
也许子节点下面还有子节点,也许没有,怎么样用treeview控件循环显示出正确数据?
科目编码 名称 上级科目编码
a1 aa R
a2 bb R
a3 cc a1
a4 dd a1
a5 ee a3
a6 ff a5
......
也许子节点下面还有子节点,也许没有,怎么样用treeview控件循环显示出正确数据?
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
树形数据深度排序处理示例(递归法)
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--广度搜索排序函数
CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OR(@ID IS NULL AND PID IS NULL)
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ID,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
insert into test values('a1','aa','R' )
insert into test values('a2','bb','R' )
insert into test values('a3','cc','a1')
insert into test values('a4','dd','a1')
insert into test values('a5','ee','a3')
insert into test values('a6','ff','a5')
gocreate procedure sp_test
as
begin
declare @t table(code varchar(8),name varchar(8),Pcode varchar(8),path varchar(200))
insert into @t select code,name,Pcode,right('---'+code,4) from test where pcode='R'
while @@rowcount<>0
begin
insert into @t
select b.code,b.name,b.Pcode,a.path+right('---'+b.code,4)
from @t a,test b
where a.code=b.pcode and not exists(select 1 from @t where code=b.code)
end
select code,name,Pcode from @t order by path
end
go exec sp_test
/*
code name Pcode
-------- -------- --------
a1 aa R
a3 cc a1
a5 ee a3
a6 ff a5
a4 dd a1
a2 bb R
*/
godrop procedure sp_test
drop table test
go
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindTreeView();
} } /// <summary>
/// Bind data to the treeview control tvTreeView
/// </summary>
private void BindTreeView()
{
ProductService productService = new ProductService();
this.tvCatalogs.Nodes.Clear();
CCatalog root = productService.GetCatalogByID(strRootID);
TreeNode troot = new TreeNode(root.CatalogName, root.CatalogID + "|" + root.CatalogName + "|" + root.ParentID + "|" + root.TheOrder.ToString());
this.tvCatalogs.Nodes.Add(troot); this.BindSubTreeNodes(strRootID, troot); this.tvCatalogs.ExpandAll();
} /// <summary>
/// Bind data to the treeview control tvTreeView
/// </summary>
private void BindSubTreeNodes(string strParentID, TreeNode root)
{
ProductService productService = new ProductService();
CCatalog[] cCatalogs = productService.GetCatalogsByParentID(strParentID);
for (int i = 0; i < cCatalogs.Length; i++ )
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("{0}|{1}|{2}|{3}", cCatalogs[i].CatalogID, cCatalogs[i].CatalogName, cCatalogs[i].ParentID, cCatalogs[i].TheOrder);
TreeNode node = new TreeNode(cCatalogs[i].CatalogName, sb.ToString());
root.ChildNodes.Add(node);
this.BindSubTreeNodes(cCatalogs[i].CatalogID, node);
}
}
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
你虽然好像排了一下序,便怎么样显示呢?
{
if(!IsPostBack)
{
bind();
}
} private void bind()
{
string sql="select * from template a where not exists(select 1 from template where 科目编码=a.上级科目编码) ";
DataSet ds=databind(sql);
if(ds!=null && ds.Tables["tree"].Rows.Count>0)
{
foreach(DataRow dr in ds.Tables["tree"].Rows)
{
TreeNode tn=new TreeNode();
tn.Text=dr["名称"].ToString();
tn.Target=dr["科目编码"].ToString();
TreeView1.Nodes.Add(tn);
addtree(tn.Target,tn);
}
}
} private DataSet databind(string sql)
{
SqlConnection conn=new SqlConnection("server=.;database=master;uid=sa;pwd=sa");
SqlDataAdapter da=new SqlDataAdapter(sql,conn);
DataSet ds=new DataSet();
da.Fill(ds,"tree");
return ds;
} private void addtree(string ParentID,TreeNode pNode)
{
string sql="select * from template where 上级科目编码='"+ ParentID +"'";
DataSet ds=databind(sql);
if(ds==null || ds.Tables["tree"].Rows.Count<=0)
return;
else
{
foreach(DataRow dr in ds.Tables["tree"].Rows)
{
TreeNode tn=new TreeNode();
tn.Text=dr["名称"].ToString();
tn.Target=dr["科目编码"].ToString();
pNode.Nodes.Add(tn);
addtree(tn.Target,tn);
}
}
}
http://blog.csdn.net/roy_88/archive/2006/12/24/1458449.aspx