无限级分类(使用TreeView显示)

自己写的一个无限级分类，完成了添加和删除，更新有空再写吧

数据库脚本

view plaincopy to clipboardprint?
IF EXISTS(SELECT 1 FROM sys.databases WHERE NAME='Demo')  
 DROP DATABASE [Demo]  
GO  
CREATE DATABASE [Demo]   
GO  
 
USE [Demo]  
GO  
IF EXISTS(SELECT 1 FROM sys.all_objects WHERE NAME='User' AND TYPE = 'U')  
 DROP TABLE [User]  
GO  
CREATE TABLE [User] (  
    [Id] [int] IDENTITY NOT NULL PRIMARY KEY  CLUSTERED ,  
    [Name] [nvarchar] (50) UNIQUE NOT NULL ,  
    [PId] [int] NULL REFERENCES [User] ([Id]),  
    [Path][varchar] (8000) NOT NULL DEFAULT '',  
    [Depth] [int] NOT NULL DEFAULT 0 ,   
    [Children] [VARCHAR](8000) DEFAULT '' ,  
    [ChildrenCount] [int] NOT NULL DEFAULT (0)  
) ON [PRIMARY]  
GO  
 
--添加记录  
IF EXISTS(SELECT 1 FROM sys.all_objects WHERE NAME='InsertUser' AND TYPE = 'P')  
 DROP PROCEDURE InsertUser  
GO  
CREATE PROCEDURE InsertUser  
(  
    @Name nvarchar(50),  
    @PId INT = null 
)  
AS 
SET NOCOUNT OFF 
BEGIN TRY  
 BEGIN TRAN  
  DECLARE @Id int 
  Insert INTO [User](Name, PId)VALUES(@Name, @PId)  
  SET @Id = SCOPE_IDENTITY()  
  IF @PId IS NOT NULL 
   BEGIN 
    Declare @ParentDepth int,  @ParentPath varchar(8000)  
    --得到父节点深度  
    select @ParentDepth = depth, @ParentPath = [Path] from [User] WHERE Id = @PId  
 
    --更新当前节点深度  
    UPDATE [User]  
    SET depth = @ParentDepth + 1, [path] = @ParentPath + Convert(varchar(10), @Id) + ',' 
    WHERE Id = @Id  
      
    --更新父节点及以上深度(方法1)  
    set @ParentPath = SUBSTRING(@ParentPath, 0, len(@ParentPath))  
      
    DECLARE @sql VARCHAR(8000)  
    set @sql = 'Update [User] set [Children] = [Children] + Convert(varchar(10), ' +  Convert(varchar(10), @PId) + ') + ''-'' + Convert(varchar(10), ' + Convert(varchar(10), @Id) + ') + '','', ChildrenCount = ChildrenCount + 1 where id in (' + @ParentPath + ')' 
    exec (@sql)  
    --exec sp_executesql @sql  
      
    --(方法2)  
    --更新爷爷辈及以上节点深度  
    --Update [User]  
    --set [Children] = [Children] + Convert(varchar(10), @PId) + '-' + Convert(varchar(10), @Id) + ',', ChildrenCount = ChildrenCount + 1  
    --where Children like '%-' + Convert(varchar(10), @PId) +',%'     
      
    --更新父节点  
    --UPDATE [User]  
    --SET [Children] = [Children] + Convert(varchar(10), @PId) + '-' + Convert(varchar(10), @Id) + ',', ChildrenCount = ChildrenCount + 1  
    --WHERE Id = @PId  
   END 
  ELSE 
   UPDATE [User]  
   SET [path] = Convert(varchar(10), @Id) + ',' 
   WHERE Id = @Id  
 COMMIT TRAN  
END TRY  
BEGIN CATCH  
 ROLLBACK TRAN  
END CATCH  
SET NOCOUNT ON 
GO  
 
--插入测试数据  
Create Proc InsertTestData  
 @count int 
as 
declare @id int 
declare @pid int 
declare @name varchar(6)  
declare @char1 varchar(1)  
declare @char2 varchar(1)  
declare @char3 varchar(1)  
declare @char4 varchar(1)  
declare @char5 varchar(1)  
declare @char6 varchar(1)  
set @id = 1  
while(@id <= @count)  
begin 
 set @pid = floor(rand() * @id) + 1  
 set @char1 = CHAR(floor(rand() * 26) + ASCII('A'))  
 set @char2 = CHAR(floor(rand() * 26) + ASCII('A'))  
 set @char3 = CHAR(floor(rand() * 26) + ASCII('A'))  
 set @char4 = CHAR(floor(rand() * 26) + ASCII('A'))  
 set @char5 = CHAR(floor(rand() * 26) + ASCII('A'))  
 set @char6 = CHAR(floor(rand() * 26) + ASCII('A'))  
 set @name = @char1 + @char2 + @char3 + @char4 + @char5 + @char6  
   
 if(@id = @pid)  
  set @pid = null 
    
 EXEC InsertUser @name, @pid  
 set @id = @id + 1  
end 
go  
 
EXEC InsertTestData 100  
 
--查询  
SELECT * FROM [User]  
 
--删除记录  
IF EXISTS(SELECT 1 FROM sys.all_objects WHERE NAME='DeleteUser' AND TYPE = 'P')  
 DROP PROCEDURE DeleteUser  
GO  
CREATE PROCEDURE DeleteUser  
(  
    @Id INT 
)  
AS 
SET NOCOUNT OFF 
 DECLARE @Children VARCHAR(8000)  
 SELECT @Children = Children FROM [User] WHERE id = @Id  
 SET @Children = SUBSTRING(@children, 0, len(@Children))  
 create table #temp   
 (   
  id int not null   
 )   
 declare @result VARCHAR(8000)   
 set @result =' insert into #temp(id) select ' + replace(replace(@Children, ',', ' union select '), '-', ' union select ')  
 exec(@result)   
 
 declare cur cursor 
 for 
 select id from #temp order by id desc 
 open cur  
 declare @tempid int 
 declare @whereClause varchar(8000)  
 set @whereClause = '' 
 fetch next from cur into @tempid  
 while @@fetch_status = 0  
 begin 
  set @whereClause = @whereClause + Convert(varchar(10), @tempid) + ',' 
  fetch next from cur into @tempid  
 end 
 CLOSE cur  
 DEALLOCATE cur  
   
 set @whereClause = SUBSTRING(@whereClause, 0, len(@whereClause))  
   
 exec ('delete from [User] where id in (' + @whereClause + ')')  
SET NOCOUNT ON 
GO  
 
EXEC DeleteUser 5 
IF EXISTS(SELECT 1 FROM sys.databases WHERE NAME='Demo')
 DROP DATABASE [Demo]
GO
CREATE DATABASE [Demo]
GO

USE [Demo]
GO
IF EXISTS(SELECT 1 FROM sys.all_objects WHERE NAME='User' AND TYPE = 'U')
 DROP TABLE [User]
GO
CREATE TABLE [User] (
    [Id] [int] IDENTITY NOT NULL PRIMARY KEY  CLUSTERED ,
    [Name] [nvarchar] (50) UNIQUE NOT NULL ,
    [PId] [int] NULL REFERENCES [User] ([Id]),
    [Path][varchar] (8000) NOT NULL DEFAULT '',
    [Depth] [int] NOT NULL DEFAULT 0 ,
    [Children] [VARCHAR](8000) DEFAULT '' ,
    [ChildrenCount] [int] NOT NULL DEFAULT (0)
) ON [PRIMARY]
GO

--添加记录
IF EXISTS(SELECT 1 FROM sys.all_objects WHERE NAME='InsertUser' AND TYPE = 'P')
 DROP PROCEDURE InsertUser
GO
CREATE PROCEDURE InsertUser
(
    @Name nvarchar(50),
    @PId INT = null
)
AS
SET NOCOUNT OFF
BEGIN TRY
 BEGIN TRAN
  DECLARE @Id int
  Insert INTO [User](Name, PId)VALUES(@Name, @PId)
  SET @Id = SCOPE_IDENTITY()
  IF @PId IS NOT NULL
   BEGIN
    Declare @ParentDepth int,  @ParentPath varchar(8000)
    --得到父节点深度
    select @ParentDepth = depth, @ParentPath = [Path] from [User] WHERE Id = @PId

    --更新当前节点深度
    UPDATE [User]
    SET depth = @ParentDepth + 1, [path] = @ParentPath + Convert(varchar(10), @Id) + ','
    WHERE Id = @Id
   
    --更新父节点及以上深度(方法1)
    set @ParentPath = SUBSTRING(@ParentPath, 0, len(@ParentPath))
   
    DECLARE @sql VARCHAR(8000)
    set @sql = 'Update [User] set [Children] = [Children] + Convert(varchar(10), ' +  Convert(varchar(10), @PId) + ') + ''-'' + Convert(varchar(10), ' + Convert(varchar(10), @Id) + ') + '','', ChildrenCount = ChildrenCount + 1 where id in (' + @ParentPath + ')'
    exec (@sql)
    --exec sp_executesql @sql
   
    --(方法2)
    --更新爷爷辈及以上节点深度
    --Update [User]
    --set [Children] = [Children] + Convert(varchar(10), @PId) + '-' + Convert(varchar(10), @Id) + ',', ChildrenCount = ChildrenCount + 1
    --where Children like '%-' + Convert(varchar(10), @PId) +',%'  
   
    --更新父节点
    --UPDATE [User]
    --SET [Children] = [Children] + Convert(varchar(10), @PId) + '-' + Convert(varchar(10), @Id) + ',', ChildrenCount = ChildrenCount + 1
    --WHERE Id = @PId
   END
  ELSE
   UPDATE [User]
   SET [path] = Convert(varchar(10), @Id) + ','
   WHERE Id = @Id
 COMMIT TRAN
END TRY
BEGIN CATCH
 ROLLBACK TRAN
END CATCH
SET NOCOUNT ON
GO

--插入测试数据
Create Proc InsertTestData
 @count int
as
declare @id int
declare @pid int
declare @name varchar(6)
declare @char1 varchar(1)
declare @char2 varchar(1)
declare @char3 varchar(1)
declare @char4 varchar(1)
declare @char5 varchar(1)
declare @char6 varchar(1)
set @id = 1
while(@id <= @count)
begin
 set @pid = floor(rand() * @id) + 1
 set @char1 = CHAR(floor(rand() * 26) + ASCII('A'))
 set @char2 = CHAR(floor(rand() * 26) + ASCII('A'))
 set @char3 = CHAR(floor(rand() * 26) + ASCII('A'))
 set @char4 = CHAR(floor(rand() * 26) + ASCII('A'))
 set @char5 = CHAR(floor(rand() * 26) + ASCII('A'))
 set @char6 = CHAR(floor(rand() * 26) + ASCII('A'))
 set @name = @char1 + @char2 + @char3 + @char4 + @char5 + @char6
 
 if(@id = @pid)
  set @pid = null
 
 EXEC InsertUser @name, @pid
 set @id = @id + 1
end
go

EXEC InsertTestData 100

--查询
SELECT * FROM [User]

--删除记录
IF EXISTS(SELECT 1 FROM sys.all_objects WHERE NAME='DeleteUser' AND TYPE = 'P')
 DROP PROCEDURE DeleteUser
GO
CREATE PROCEDURE DeleteUser
(
    @Id INT
)
AS
SET NOCOUNT OFF
 DECLARE @Children VARCHAR(8000)
 SELECT @Children = Children FROM [User] WHERE id = @Id
 SET @Children = SUBSTRING(@children, 0, len(@Children))
 create table #temp
 (
  id int not null
 )
 declare @result VARCHAR(8000)
 set @result =' insert into #temp(id) select ' + replace(replace(@Children, ',', ' union select '), '-', ' union select ')
 exec(@result)

 declare cur cursor
 for
 select id from #temp order by id desc
 open cur
 declare @tempid int
 declare @whereClause varchar(8000)
 set @whereClause = ''
 fetch next from cur into @tempid
 while @@fetch_status = 0
 begin
  set @whereClause = @whereClause + Convert(varchar(10), @tempid) + ','
  fetch next from cur into @tempid
 end
 CLOSE cur
 DEALLOCATE cur
 
 set @whereClause = SUBSTRING(@whereClause, 0, len(@whereClause))
 
 exec ('delete from [User] where id in (' + @whereClause + ')')
SET NOCOUNT ON
GO

EXEC DeleteUser 5

 

 

 

 

 

 

页面显示代码

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %> 
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head runat="server"> 
    <title></title> 
</head> 
<body> 
    <form id="form1" runat="server"> 
    <div> 
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> 
        <asp:Button ID="Button1" runat="server" Text="查询" οnclick="Button1_Click" /> 
        <br /> 
        <asp:Label ID="Label1" runat="server"></asp:Label> 
        <asp:TreeView ID="TreeView1" runat="server"> 
        </asp:TreeView> 
    </div> 
    </form> 
</body> 
</html> 

 

 

后台代码

 

using System;  
using System.Collections.Generic;  
using System.Web;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
using System.Data;  
using System.Data.SqlClient;  
 
public partial class _Default : System.Web.UI.Page   
{  
    private DataSet ds;  
 
    protected void Page_Load(object sender, EventArgs e)  
    {  
 
    }  
 
    protected void Button1_Click(object sender, EventArgs e)  
    {  
        int id = 0;  
 
        if (int.TryParse(TextBox1.Text,out id))  
        {  
            SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString);  
            string sql = "select [Children], [ChildrenCount] from [User] where id = @id";  
            SqlCommand cmd = new SqlCommand(sql, cn);  
            cmd.Parameters.AddWithValue("@id", id);  
            string path = string.Empty;  
 
            try 
            {  
                cn.Open();  
                SqlDataReader dr = cmd.ExecuteReader();  
                if (dr.Read())  
                {  
                    path = dr.GetString(dr.GetOrdinal("Children"));  
                    Label1.Text = "共有孩子:" + dr.GetInt32(dr.GetOrdinal("ChildrenCount")).ToString();  
                }  
            }  
            catch (SqlException ex)  
            {  
                throw;  
            }  
            finally 
            {  
                cn.Close();  
            }  
            MakeTreeView(path);  
            TreeView1.ExpandAll();  
        }  
    }  
 
    private void MakeTreeView(string path)  
    {  
        TreeView1.Nodes.Clear();  
        string[] children = path.TrimEnd(',').Split(',');  
        Dictionary<string, TreeNode> dictionary = new Dictionary<string, TreeNode>();  
 
        TreeNode node1 = null;  
        TreeNode node2 = null;  
        foreach (string item in children)  
        {  
            string[] s = item.Split('-');  
            node1 = new TreeNode(GetName(s[0]), s[0]);  
            node2 = new TreeNode(GetName(s[1]), s[1]);  
              
            if (dictionary.ContainsKey(s[0]))  
            {  
                dictionary[s[0]].ChildNodes.Add(node2);  
            }  
            else 
            {  
                dictionary[s[0]] = node1;  
                node1.ChildNodes.Add(node2);  
            }  
            dictionary[s[1]] = node2;  
        }  
        TreeView1.Nodes.Add(dictionary[TextBox1.Text]);  
    }  
 
    private string GetName(string id)  
    {  
        string name = string.Empty;  
    //实际应用ds可以放到缓存里  
        if (ds == null)  
        {  
            SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString);  
            string sql = "select id, name from [User]";  
            SqlDataAdapter da = new SqlDataAdapter(sql, cn);  
            ds = new DataSet();  
            cn.Open();  
            da.Fill(ds);  
            cn.Close();  
        }  
 
        foreach (DataRow row in ds.Tables[0].Rows)  
        {  
            if (row["id"].ToString() == id)  
            {  
                name = row["name"].ToString();  
                break;  
            }  
        }  
 
        return name;  
    }  
}  
 

 

出处：http://blog.csdn.net/amandag/archive/2009/12/16/5021126.aspx