ASP.NET无限级分类的非递归实现(存储过程版）

或多或少都做过树状目录、产品分类之类的二级或三级菜单，如果遇到更多级的分类，就一般使用递归了。在程序中使用递归或多或少会增加一些性能上的开销。

之前我用asp.net在程序中实现过非递归的无限级分类目录，但考虑到移植性不强，就改成了存储过程，发出来大家共同研究一下，到目前为止，测试过程中还没发现问题，另外，代码方面没经过什么优化。

通常情况下，我们更多的操作是读取目录，所以，在下面的实现中，读取我们只需要一select语句就够了,不使用递归，理论上无限级~！
===================================================
表结构：
表名：tb_column
表结构（所有字段非空）：
column_id     int 主键（注：非标识）
column_name   nvarchar(50)分类名称
parent_id     int 父分类id（默认值0）
column_path   nvarchar(1000) 分类路径
column_depth  int分类深度(默认值0）
column_order  int排序(默认值0)
column_intro  nvarchar(1000)分类说明

================================================
存储过程一：新建分类

create procedure sp_column_insert
(
@parent_id int,
@column_name nvarchar(50),
@column_intro nvarchar(1000)
)
as
declare @err as int
set @err=0

begin tran
--通过现有记录获取栏目id
declare @column_id as int
declare @column_depth as int
select @column_id = max(column_id) from tb_column
if @column_id is not null
set @column_id = @column_id+1
else
set @column_id = 1

--判断是否是顶级栏目，设置其column_path和column_order
declare @column_path as nvarchar(1000)
declare @column_order as int
if @parent_id = 0
begin
set @column_path =ltrim(str(@column_id))
select @column_order = max(column_order) from tb_column
if @column_order is not null
set @column_order = @column_order + 1
else --如果没有查询到记录，说明这是第一条记录
set @column_order = 1

--深度
set @column_depth = 1
end
else
begin
--获取父节点的路径和深度
select @column_path = column_path ,@column_depth = column_depth from tb_column where

[email protected]_id
if @column_path is null
begin
set @err = 1
goto theend
end

--获取同父节点下的最大序号
select @column_order = max(column_order) from tb_piccolumn where column_path like

''[email protected]_path+'|%'  or column_id = @parent_id
if @column_order is not null --如果序号存在，那么将该序号后的所有序号都加1
begin
--更新当前要插入节点后所有节点的序号
update tb_column set column_order = column_order +1 where column_order

>@column_order
--同父节点下的最大序号加上1，构成自己的序号
set @column_order = @column_order + 1
end
else
begin
set @err=1
goto theend
end

--父节点的路径加上自己的id号，构成自己的路径
set @column_path = @column_path + '|' + ltrim(str(@column_id))

--深度
set @column_depth = @column_depth+1

end

insert into tb_column(column_id,column_name,parent_id,column_path,column_depth,column_order,column_intro)

values(@column_id,@column_name,@parent_id,@column_path,@column_depth,@column_order,@column_intro)

if @@error<>0
begin
set @err=1
goto theend
end

--更新当前记录之后的记录的order
--update tb_column set column_order = column_order+1 where column_order  > @column_order

theend:
if @err=0
begin
commit tran
return @column_id
end
else
begin
    rollback tran
return 0
end
go

===================================================
存储过程二：删除分类
create procedure sp_column_delete
(
@column_id int
)
as
declare @err as int
set @err = 0
begin tran
--首先查询该节点下是否有子节点
select column_id from tb_column where parent_id = @column_id
if @@rowcount<>0
    begin
    set @err = 1
    goto theend
    end

--获取该节点的column_order,为了删除后整理其他记录的顺序
declare @column_order as int
select @column_order = column_order from tb_column where column_id = @column_id
if @column_order is null
    begin
      set @err =2
      goto theend
    end

--更新其他记录的column_order
update tb_column set column_order = column_order -1 where column_order >@column_order
if @@error<>0
    begin
      set @err =3
      goto theend
    end

--删除操作
delete from tb_column where [email protected]_id
if @@error<>0
    begin
      set @err =4
      goto theend
  end

--更新其他记录的column_id
--update tb_column set column_id= column_id - 1 where column_id >@column_id
--if @@error<>0
--    begin
--      set @err =5
--      goto theend
--    end

theend:
if @err = 0
    begin
      commit tran
      return 0 --删除成功
    end
else
    begin
      if @err=1
  begin
      rollback tran
      return 1 --有子节点
end
      else
begin
      rollback tran
      return 2--未知错误
end
    end
go
=============================================
存储过程三：编辑分类
create procedure sp_column_update
(
@column_id int,
@parent_id int,
@column_name nvarchar(50),
@column_intro nvarchar(1000)
)
as
declare @err as int
set @err=0

begin tran

--获取修改前的：parent_id,column_depth,column_order
declare @oparent_id as int
declare @ocolumn_depth as int
declare @ocolumn_order as int
declare @ocolumn_path as nvarchar(1000)

select @oparent_id = parent_id, @ocolumn_depth = column_depth,@ocolumn_order = column_order, @ocolumn_path = column_path  from tb_column where column_id = @column_id
if @oparent_id is null
    begin
    set @err = 1
    goto theend
    end

--如果父id没有改变，则直接修改栏目名和栏目简介
if @oparent_id = @parent_id
    begin
    update tb_column set column_name = @column_name,column_intro = @column_intro where column_id = @column_id
    if @@error <> 0
    set @err = 2
    goto theend
    end

declare @ncolumn_path as nvarchar(1000)
declare @ncolumn_depth as int
declare @ncolumn_order as int

--获取当前节点作为父节点所包含的节点数[包括自身] 注：如果返回 “1” 说明是单节点
declare @thecount as int
select @thecount = count(column_id) from tb_column where [email protected]_id or column_path like ''[email protected]_path+'|%'
if @thecount is null
begin
    set @err = 3
    goto theend
end

if @parent_id=0 --如果是设置为顶级节点，将节点设置为最后一个顶级节点
begin
--print '设置为顶级栏目'
set @ncolumn_path = ltrim(str(@column_id))
set @ncolumn_depth =1

select @ncolumn_order = max(column_order) from tb_column
if @ncolumn_order is null
                  begin
     set @err = 4
     goto theend
     end

set @ncolumn_order = @ncolumn_order - @thecount + 1

--更新三部分 1 节点本身 2 所有子节点 2 本树更改之前的后面记录的顺序
--print '更新本栏目之前位置后面的所有栏目[不包括本栏目下的子栏目]的：column_order'
update tb_column set column_order = [email protected] where (column_order >@ocolumn_order) and (column_path not like ''[email protected]_path+'|%')
if @@error <> 0
    begin
    set @err = 7
    goto theend
    end

--print '更新本栏目的：parent_id,column_path,column_depth,column_order,column_name,column_intro'
print 'order : '+ltrim(str(@ncolumn_order))
update tb_column set [email protected]_id,column_path = @ncolumn_path,column_depth = @ncolumn_depth,column_order = @ncolumn_order, column_name = @column_name,column_intro = @column_intro where column_id = @column_id
if @@error <> 0
    begin
    set @err = 5
    goto theend
    end

--print '更新本栏目下的所有子栏目的：column_path,column_depth,column_order'
             update tb_column set column_path = replace(column_path,@ocolumn_path,@ncolumn_path),column_depth = column_depth + (@[email protected]_depth),column_order = column_order+( @[email protected]_order) where column_path like ''[email protected]_path+'|%'
if @@error <> 0
    begin
    set @err = 6
    goto theend
    end

end
else
begin
--获取未来父节点的相关信息,并设置本节点的相关值
select @ncolumn_depth = column_depth,@ncolumn_path = column_path from tb_column where column_id = @parent_id
if @ncolumn_depth is  null or @ncolumn_path is null
      begin
      set @err = 8
      goto theend
      end
set @ncolumn_depth = @ncolumn_depth +1
select @ncolumn_order =max(column_order) from tb_column where column_id = @parent_id or  column_path like ''[email protected]_path+'|%'
if @ncolumn_order is  null
      begin
      set @err = 9
      goto theend
      end

set @ncolumn_path = @ncolumn_path +'|'+ ltrim(str(@column_id))

if @ncolumn_order = @ocolumn_order+1 --如果新的父节点是原来位置上端最近一个兄弟,则所有节点的顺序都不改变
                    begin
update tb_column set [email protected]_id,column_path = @ncolumn_path,column_depth = @ncolumn_depth, column_name = @column_name,column_intro = @column_intro where column_id = @column_id
if @@error <> 0
    begin
    set @err = 10
    goto theend
    end
       end

set  @ncolumn_order = @ncolumn_order + 1

--更新三部分 1 本树更改之前的后面（或前面）记录的顺序 1 节点本身  3 所有子节点
--分为向上移或象下移
--print '更新本栏目之前位置后面的所有栏目[或者本栏目之后位置]  [不包括本栏目下的子栏目]的：column_order'
if @ncolumn_order < @ocolumn_order
begin
update tb_column set column_order = [email protected] where column_order<@ocolumn_order  and column_order >[email protected]_order and (column_path not like ''[email protected]_path+'|%') and column_id<>@column_id
if @@error <> 0
        begin
        set @err = 12
        goto theend
        end
end
else
begin
update tb_column set column_order = [email protected] where column_order >@ocolumn_order and column_order<@ncolumn_order  and (column_path not like ''[email protected]_path+'|%') and column_id<>@column_id
if @@error <> 0
        begin
        set @err = 13
        goto theend
        end
end

--print '更新本栏目的：parent_id,column_path,column_depth,column_order,column_name,column_intro'
print 'order : '+ltrim(str(@ncolumn_order))
if @ncolumn_order > @ocolumn_order
set @ncolumn_order = @ncolumn_order - @thecount
update tb_column set [email protected]_id,column_path = @ncolumn_path,column_depth = @ncolumn_depth,column_order = @ncolumn_order, column_name = @column_name,column_intro = @column_intro where column_id = @column_id
if @@error <> 0
    begin
    set @err = 10
    goto theend
    end

--print '更新本栏目下的所有子栏目的：column_paht,column_depth,column_order'
             update tb_column set column_path = replace(column_path,@ocolumn_path,@ncolumn_path),column_depth = column_depth + (@[email protected]_depth),column_order = column_order+(@[email protected]_order) where column_path like ''[email protected]_path+'|%'
if @@error <> 0
    begin
    set @err = 11
    goto theend
    end
end

theend:
if @err<>0 --如果有错误则返回错误号
   begin
   rollback tran
   return @err
   end
else     --如果没有错误就返回0
   begin
   commit tran
   return 0
   end
go
=========================================
存储过程四：显示分类（只是一条select语句）
分类列表：
create procedure sp_column_list
 as
select column_id, column_name, parent_id, column_path, column_depth,
      column_order, column_intro
from tb_column
order by column_order
go

=======================================