ASP.NET无限级分类的实现

数据表:
表名：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 

Column_ID=@Parent_ID 
IF @Column_Path Is Null 
Begin 
Set @Err = 1 
Goto theEnd 
End 

--获取同父节点下的最大序号 
Select @Column_Order = Max(Column_Order) From Tb_Column Where Column_Path like 

''+@Column_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 Column_ID=@Column_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 Column_ID=@Column_ID Or 
Column_Path like ''+@oColumn_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 = Column_Order-@theCount Where (Column_Order >@oColumn_Order) 
And (Column_Path Not like ''+@oColumn_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 Parent_ID=@Parent_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 + (@nColumn_Depth-@oColumn_Depth),
Column_Order = Column_Order+( @nColumn_Order-@oColumn_Order) Where Column_Path 
like ''+@oColumn_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 ''+@nColumn_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 Parent_ID=@Parent_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 = Column_Order+@theCount Where Column_Order<@oColumn_Order 
And Column_Order >=@nColumn_Order And (Column_Path Not like ''+@oColumn_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 = Column_Order-@theCount Where Column_Order >@oColumn_Order
And Column_Order<@nColumn_Order And (Column_Path Not like ''+@oColumn_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 Parent_ID=@Parent_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 + (@nColumn_Depth-@oColumn_Depth),
Column_Order = Column_Order+(@nColumn_Order-@oColumn_Order) 
Where Column_Path like ''+@oColumn_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

修改存储过程一：新建分类

ALTER 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 ='0|'+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

Column_ID=@Parent_ID
IF @Column_Path Is Null
Begin
Set @Err = 1
Goto theEnd
End

--获取同父节点下的最大序号
Select @Column_Order = Max(Column_Order) From Tb_Column Where Column_Path like

''+@Column_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

遍历所有分类

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
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 Microsoft.ApplicationBlocks.Data;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        DataTable dt=new DataTable();
        dt = SqlHelper.ExecuteDataset(connString, CommandType.StoredProcedure, "sp_Column_List").Tables[0];
        GridView1.DataSource = dt;
        GridView1.DataBind();

        //一级分类
        DataSet dsParentName = new DataSet();
        string parentNameSql = "select Column_ID,Column_Name from Tb_Column where Column_Depth=1 order by Column_Order";
        dsParentName = SqlHelper.ExecuteDataset(connString, CommandType.Text, parentNameSql);
        if (dsParentName.Tables[0].Rows.Count!=0)
        {
            Response.Write("<ul>");
            foreach (DataRow dr in dsParentName.Tables[0].Rows) 
            {   
                Response.Write("<li><span>" + dr[1] + "</span>");

                NextParentName(connString,Convert.ToInt32(dr[0]));

                Response.Write("</li>");
            }
            Response.Write("</ul>");
        }
    }

    private void NextParentName(string connString,int Column_ID) 
    {
        DataSet dsNextParentName = new DataSet();
        string nextParentNameSql = "select Column_ID,Column_Name from Tb_Column where Parent_ID=" + Column_ID + " order by Column_Order";
        dsNextParentName = SqlHelper.ExecuteDataset(connString, CommandType.Text, nextParentNameSql);

        if (dsNextParentName.Tables[0].Rows.Count != 0)
        {
            Response.Write("<ul>");
            foreach (DataRow drNext in dsNextParentName.Tables[0].Rows)
            {
                Response.Write("<li><span>" + drNext[1] + "</span>");

                NextParentName(connString, Convert.ToInt32(drNext[0]));

                Response.Write("</li>");
            }
            Response.Write("</ul>");
        }
    }
}

posted @ 2014-08-18 15:53 linyongqin 阅读(225) 评论(0) 收藏举报

刷新页面返回顶部

linyongqin

ASP.NET无限级分类的实现

公告