数据生成树新增

数据生成树

使用存储过程在数据表中生成树

数据库 Category1表结构

________________________________________

CREATE TABLE [dbo].[Category1](
[id] [int] IDENTITY(1,1) NOT NULL,
[pid] [int] NULL,
[Name] [varchar](100) NULL,
[path] [varchar](255) NULL,
[sort] [varchar](255) NULL,
[isHot] [bit] NULL,
[keywords] [text] NULL,
[description] [text] NULL,
CONSTRAINT [PK_Category_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

__________________________________________

1.使用存储过程生成顶级类

Create proc AddTopClass
(
@ID int,
@name varchar(100),
@keyword varchar(255),
@description varchar(255)
)
as
declare @dd int
declare @sort varchar(255)
declare @sort1 int
insert into Category1([name],pid,keywords,[description])values (@name,0,@keyword,@description)--新增顶级类记录
if @@ERROR<>0--判断是否执行
print 100
else
begin
select top 1 @dd=id from Category1 order by id desc --将新生成的顶级类记录ID赋值给@dd参数
if @@rowcount=0
   print 100
else
  begin
         select @sort=MAX(isnull(sort,'')) from Category1 where pid=0 --顶级类最大的sort
         if @@rowcount=0
              print 100
         else
              begin
              set @sort1=0
              if @sort=''
                   set @sort='01'
              else
                   begin
                        set @sort1=@sort+1
                        if LEN(@sort1)=1
                             set @sort='0'+CONVERT(varchar(255),@sort1)
                        else if LEN(@sort1)=2
                             set @sort=@sort1
                   end
              update Category1 set path=@dd,sort=@sort where id=@dd
              end
    end
end

2 使用存储过程生成子类

Create proc subclass
(
@ID int,
@name varchar(100),
@keyword varchar(255),
@description varchar(255)
)
as
declare @dd int
declare @sort varchar(255)
declare @sort1 int
declare @sort2 varchar(255)
declare @path varchar(255)
insert into Category1(Name,pid,keywords,[description]) values(@name,@ID,@keyword,@description)
if @@ERROR<>0
   print 100
else
   begin
      select top 1 @dd=id from Category1 order by id desc
      if @@rowcount=0
         print 100
      else
         begin
         select @path=[path]+','+ CONVERT(varchar(50),@dd) ,@sort=sort+',' from Category1 where id=@ID
         select @sort2=MAX(isnull(sort,'')) from Category1 where pid=@ID
         if @@rowcount=0
            print 100
         else
         begin
            set @sort1=0
            if @sort2=''
               set @sort +='01'
            else
               begin
                  set @sort2=Right(@sort2,2)
                  set @sort1=CONVERT(int, @sort2)
                  set @sort1 += 1
                  if LEN(@sort1)=1
                     set @sort+='0'+CONVERT(varchar(255),@sort1)
                  else if LEN(@sort1)=2
                        set @sort+=@sort1
               end
            update Category1 set path=@path,sort=@sort where id=@dd
      end
   end
end

删除节点

create proc deleteCatagory
(@id int)
as
declare @path varchar(500)
if exists (select * from Category1 where id=@id)
begin
select @path=[path] from Category1 where id=@id
delete from Category1 where [path]like ''+@path+'%'
if @@ERROR=0
print 101
else
print 100
end
else
print 100

数据生成树 新增

数据生成树新增