自己写的一个无限级分类,完成了添加和删除,更新有空再写吧 数据库脚本 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; } }