无限分类树--Treeview

 我最近在搞一个内部资料系统开发.用到Treeview,而且要需要无限分类,还要得到每个类包含的结果集!其实最重要的是比普通树表增加一列searchid,通过这列来进行查询!废话少讲,效果图如下:

其中要用到三个表:purtree,pur,purcom:设计图如下:purtree设计图

pur设计图:

purcom设计图:

表设计好之后就简单了.编写存储过程.触发器.还有视图:

我给purtree增加了一个触发器.用来增加searchid这列.这样我们就可以不用在程序中增加许多程序了,触发器代码:

CREATE TRIGGER searid_insert ON [dbo].[purtree]
FOR INSERT
AS
  declare @parentid char(10)
  declare @searchid char(50)
  set @parentid=(select parentid from purtree where treeid=(select @@identity))
  if @parentid=0
     begin
       set @searchid=(select '0'+ convert(char,max(searchid) +1,120) from purtree where parentid=0)
        update purtree set searchid=@searchid where treeid=(select @@identity)
     end
  else
      begin
         set @searchid=(select searchid from purtree where treeid=@parentid)
         update purtree set searchid=(RTRIM(CONVERT(char(50), @searchid, 120)) + LTRIM(CONVERT(char(50),(select @@identity),
         120)))
         where treeid=(select @@identity)
       end

这里注意.我的searchid=父节点的searchid+节点ID(也就是treeid),如果父节点是0,也就是根节点的话:searchid要判断.我这里是用01+1,也就是02.03这样!下面就是最重要的了.存储过程:

增加新树节点的存储过程:

CREATE PROCEDURE purtree_insert(@treename char(50),@parentid int)
as
insert into purtree(treename,parentid)values(@treename,@parentid)
exec purtree_update_searchid
GO

这里还用到了purtree_update_searchid这个存储过程.我们也把贴出来:

CREATE PROCEDURE purtree_update_searchid
as
declare @treeid int
set @treeid=(select @@identity)
update pur set searchid=(select searchid from purtree where treeid=@treeid)
where mname=(select treename from purtree where treeid=@treeid) and
          searchid=(select searchid from purtree where treeid=(select parentid from purtree where treeid=@treeid))
GO
这个存储过程主要利用了插入记录的时候.SQL会产生一个@@identity的标识！如果 pur表中存在添加的节点：如：网站，这时候就需要更新他的searchid,要不然，我的树节点下面就没有这一项!

下面是pur表中插入记录,最需要注意的就是.插入记录的:如我的效果图中,物料名称在树中有相应的节点的话,这时候searchid就要更新,存储过程如下:

CREATE PROCEDURE pur_insert
(
@reid char(50),@cname char(50),@ename char(50),@ctype char(50),@address char(200),@web char(50),
@mtype char(50),@mname char(50),@createdate datetime,@createuser char(50),@other char(200),
@searchid char(50),@csearchid char(50)
)
as
declare @comsearchid char(50)
set @comsearchid=(select searchid from purtree where treeid=@csearchid)
insert into pur(cname,mtype,mname,createdate,createuser,other,searchid)
         values(@cname,@mtype,@mname,@createdate,@createuser,@other,@searchid)
exec pur_update_searchid @searchid
insert into purcom(reid,cname,ename,type,address,web,searchid)
         values(@reid,@cname,@ename,@ctype,@address,@web,@comsearchid)
GO
这里又用到了pur_update_searchid @searchid这个存储过程:

CREATE PROCEDURE pur_update_searchid(@searchid char(50))
as
declare @id int
set @id=(select @@identity)
declare @newsearchid char(50)
set @newsearchid=(select searchid from purtree where treename=(select mname from pur where [id]=@id)
         and rtrim(searchid) like '%'+rtrim(@searchid)+'%'
         )
if @newsearchid is not null
  begin
  update pur set searchid=@newsearchid
  where [id]=@id
end
GO

需要注意一点.就是要判断为null值的时候才更新.不然他就会更新所有,到时候pur表中相应行没了searchid,那就没得玩了!

还有最后一步.就是更新pur表,存储过程:

CREATE PROCEDURE pur_update
(
@reid char(50),@cname char(50),@ename char(50),@ctype char(50),@address char(200),@web char(50),
@mtype char(50),@mname char(50),@modifydate datetime,@modifyuser char(50),@other char(200),
@searchid char(50),@csearchid char(50),@pid int
)
as
declare @cid int
set @cid=(select cid from purview where [id]=@pid)
declare @comsearchid char(50)
set @comsearchid=(select searchid from purtree where treeid=@csearchid)
update pur set cname=@cname,mname=@mname,mtype=@mtype,modifyuser=@modifyuser,
           modifydate=@modifydate,other=@other,searchid=@searchid
     where [id]=@pid

exec  pur_update_searchid_id @pid,@searchid

update purcom set reid=@reid,cname=@cname,ename=@ename,address=@address,web=@web,
            searchid=@comsearchid,type=@ctype
    where [id]=@cid
GO

用到 pur_update_searchid_id @pid,@searchid 存储过程:

CREATE PROCEDURE pur_update_searchid_id(@pid int,@searchid char(50))
as
declare @newsearchid char(50)
set @newsearchid=(select searchid from purtree where treename=(select mname from pur where [id]=@pid)
         and rtrim(searchid) like '%'+rtrim(@searchid)+'%'
         )
if @newsearchid is not null
  begin
  update pur set searchid=@newsearchid
  where [id]=@pid
end
GO

效果跟插入差不多.只不过update不产生@@identity我才重新写了一个!

最后就是我效果图上.树节点后面还有(N家)这个统计.就是统计pur表中的searchid.因为是唯一的.所以很好统计,我是写了一个自定义函数:

CREATE FUNCTION get_pur_count(@searchid as char(50)) 
RETURNS int
 AS 
BEGIN
declare @count as int
set @count=(select count(*) from purview
where searchid like rtrim((select searchid from purtree where treeid=@searchid))+'%')
return @count
END

加上绑定树上面就行了!Treeview绑定数据库我再单独写点东西!其实就是一个总结,没什么技术!!大家一起学习吧!