SQL实现N级目录,以前开发一网络E盘需求多级目录。对性能要求也比较高.所以就没有直接在代码里实现了.把一些不必要的处理都放在储存过程里.效率更高~~因为此项目已经完成很久了.所以保留的SQL储存过程可能不是很全面。嘿嘿。好咯。我废话少说。不全面的将补上去.........
----------------SQL表------------------
USE [QuPanStorData]
GO
/****** 对象: Table [dbo].[Folder] 脚本日期: 12/06/2008 23:05:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Folder](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Email] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[FolderName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DirTime] [datetime] NULL,
[ColumnParentId] [bigint] NULL,
[ColumnChild] [int] NULL CONSTRAINT [DF_Folder_ColumnChild] DEFAULT ((0)),
[ColumnPath] [varchar](900) COLLATE Chinese_PRC_CI_AS NULL,
[ColumnFileCount] [int] NULL,
CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件夹ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Folder', @level2type=N'COLUMN', @level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户Email映射' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Folder', @level2type=N'COLUMN', @level2name=N'Email'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件夹名称' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Folder', @level2type=N'COLUMN', @level2name=N'FolderName'
显示结果:
其中，ColumnParenId是记录父级ID,若值为0,则代表是根目录.ColumnChild是本目录下有多少子目录.ColumnPath,记录此目录的路径(感觉不是很有用)
ColumnFileCount记录当前目录下文件的总数.(避免了select count(*) from 表)优化思想
---------------------------------储存过程------------------------------
--插入数据
ALTER PROCEDURE [dbo].[insert_Dir]
(
@email varchar(200), --用户
@foldername varchar(50), --文件夹名
@ColumnParentId int --文件夹父级ID,若是根目录则为0,
)
AS
Declare @NewColumnId int
Begin Tran start
--判断是否是顶级栏目，设置ColumnPath
Declare @ColumnPath varchar(1000)
Declare @ColumnId int
select @ColumnId = ColumnParentId from Folder where Id=@ColumnParentId
--插入信息
Insert Into [Folder](Email,FolderName,DirTime,ColumnParentId,ColumnPath,ColumnFileCount)
Values(@email,@foldername,getdate(),@ColumnParentId,'','0')
declare @N int;
set @N = @@IDENTITY
If @ColumnId=0 --根目录
Set @ColumnPath='|'+Ltrim(Str(@N))+'|'
Else
Begin
Select @ColumnPath=ColumnPath from Folder where Id=@ColumnParentId
If @ColumnPath is null
Begin
Rollback tran start
return 0
End
Else
Set @ColumnPath=@ColumnPath+Ltrim(Str(@N))+'|'
End
update Folder set ColumnPath = @ColumnPath where Id = @N
--根据父栏目的id更新父栏目的ColumnChild
If @@RowCount=1
Begin
Set @NewColumnId=@@identity
If @ColumnParentId >0
Begin
Update Folder Set ColumnChild=ColumnChild+1 where Id=@ColumnParentId
If @@Rowcount =1
Begin
Commit Tran Start
Return 1
End
Else
Begin
Rollback Tran Start
Commit Tran Start
Return 0
End
End
Else
Begin
Commit Tran Start
Return 1
End
End
Else
Begin
Rollback Tran Start
Commit Tran Start
Return 0
End
---删除表
ALTER PROCEDURE [dbo].[Proc_FolderColumnDel]
(
@Columnid int, --要删除目录行的ID
@ColumnParentId int --要删除目录行的父级Id
)
AS
BEGIN
Declare @ColumnPath varchar(1000)
--查看输入的@Columnparentid和@Columnid的父Columnparentid是否匹配
Declare @ParentId int
Select @ParentId=ColumnParentId,@ColumnPath=ColumnPath from Folder where Id=@Columnid
if @ParentId <> @ColumnParentId
return ;
else
--删除指定Id的数据
Delete from Folder where Id=@Columnid
--更新父栏目的子栏目个数
update Folder Set ColumnChild=ColumnChild-1 where Id=@ColumnParentId
--删除指定@Columnid的子栏目
Delete from Folder where ColumnPath like [email=]'%'+@ColumnPath+'%'[/email]
END
-------根据ID获取父级所有名称以及Id
ALTER PROCEDURE [dbo].[Proc_FolderTopName]
(
@id int
)
AS
--根据ID获取父级所有名称以及Id
SET NOCOUNT ON
Declare @FolderName varchar(50); --目录名
Declare @FolderId int --目录ID
Declare @ColumnParentId int --父级id
Declare @loction int
Declare @start int; --开始
Declare @length int; --返回个数
Declare @ColumnPath varchar(900) --文件夹路
--根据ID查询ColumnPath
select @FolderName =FolderName,@ColumnParentId =ColumnParentId,@ColumnPath=ColumnPath from Folder where Id=@id
set @loction = charindex('|',@ColumnPath)
set @length = 0
while @loction <> 0
begin
set @start = @loction + 1
set @loction = charindex('|',@ColumnPath,@start)
set @length = @length +1 --查询结果需要-1
end
declare @i int --级别循环变量
set @i=(@length-1);
create table #tmptable --创建临时表，存放各个级别层id
(
iid int IDENTITY(1,1), --临时自动编号,用于排序
FolderId int, --文件ID
itemname varchar(30)
)
insert into #tmptable(FolderId,itemname)values(@id,@FolderName)
while(@i>0) --循环读取上级ID
begin
select @FolderName=FolderName,@ColumnParentId =ColumnParentId ,@FolderId = Id from Folder where id =@ColumnParentId
insert into #tmptable(FolderId,itemname)values(@FolderId,@FolderName)
set @i=@i -1
end
--按级别从高到低列出上级名称
select FolderId,itemname from #tmptable order by iid desc
drop table #tmptable
---------修改文件夹与文件
ALTER PROCEDURE [dbo].[Proc_UpdateFileFolder]
(
@email varchar(200), --用户
@id int, --文件List表的Id
@folderid int, --移动到的文件夹id
@currId int --当前文件夹
)
AS
BEGIN tran moveinfo
if @folderid ='0'
begin
update List set Folder=@folderid where Id=@id and Email=@email --修改list表的Folder
if @@error <>0 goto errinfo
update Folder set ColumnFileCount = ColumnFileCount -1 where Id=@currId --移开的-1
if @@error <>0 goto errinfo
update Folder set ColumnFileCount = ColumnFileCount + 1 where Email= @email and ColumnParentId='-1' --修改根目录用户文件的个数
if @@error <>0 goto errinfo
end
else
begin
update List set Folder=@folderid where Id=@id
if @@error <>0 goto errinfo
if @currId ='0'
update Folder set ColumnFileCount = ColumnFileCount -1 where Email= @email and ColumnParentId='-1'
else
update Folder set ColumnFileCount = ColumnFileCount -1 where Email= @email and Id=@currId
if @@error <>0 goto errinfo
update Folder set ColumnFileCount = ColumnFileCount + 1 where Id =@folderid --移动到的+1
if @@error <>0 goto errinfo
end
commit tran moveinfo
return 0
errinfo:
Rollback TRAN moveinfo
return -1
好咯。这些是全部的储存过程.很多都有注释.把自己的东西分享出来.希望大家也能分享更多的好东东...以便一起提高...本论坛QQ群:29123371
----------------SQL表------------------
USE [QuPanStorData]
GO
/****** 对象: Table [dbo].[Folder] 脚本日期: 12/06/2008 23:05:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Folder](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Email] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[FolderName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[DirTime] [datetime] NULL,
[ColumnParentId] [bigint] NULL,
[ColumnChild] [int] NULL CONSTRAINT [DF_Folder_ColumnChild] DEFAULT ((0)),
[ColumnPath] [varchar](900) COLLATE Chinese_PRC_CI_AS NULL,
[ColumnFileCount] [int] NULL,
CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件夹ID' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Folder', @level2type=N'COLUMN', @level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户Email映射' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Folder', @level2type=N'COLUMN', @level2name=N'Email'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'文件夹名称' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Folder', @level2type=N'COLUMN', @level2name=N'FolderName'
显示结果:
其中，ColumnParenId是记录父级ID,若值为0,则代表是根目录.ColumnChild是本目录下有多少子目录.ColumnPath,记录此目录的路径(感觉不是很有用)ColumnFileCount记录当前目录下文件的总数.(避免了select count(*) from 表)优化思想
---------------------------------储存过程------------------------------
--插入数据
ALTER PROCEDURE [dbo].[insert_Dir]
(
@email varchar(200), --用户
@foldername varchar(50), --文件夹名
@ColumnParentId int --文件夹父级ID,若是根目录则为0,
)
AS
Declare @NewColumnId int
Begin Tran start
--判断是否是顶级栏目，设置ColumnPath
Declare @ColumnPath varchar(1000)
Declare @ColumnId int
select @ColumnId = ColumnParentId from Folder where Id=@ColumnParentId
--插入信息
Insert Into [Folder](Email,FolderName,DirTime,ColumnParentId,ColumnPath,ColumnFileCount)
Values(@email,@foldername,getdate(),@ColumnParentId,'','0')
declare @N int;
set @N = @@IDENTITY
If @ColumnId=0 --根目录
Set @ColumnPath='|'+Ltrim(Str(@N))+'|'
Else
Begin
Select @ColumnPath=ColumnPath from Folder where Id=@ColumnParentId
If @ColumnPath is null
Begin
Rollback tran start
return 0
End
Else
Set @ColumnPath=@ColumnPath+Ltrim(Str(@N))+'|'
End
update Folder set ColumnPath = @ColumnPath where Id = @N
--根据父栏目的id更新父栏目的ColumnChild
If @@RowCount=1
Begin
Set @NewColumnId=@@identity
If @ColumnParentId >0
Begin
Update Folder Set ColumnChild=ColumnChild+1 where Id=@ColumnParentId
If @@Rowcount =1
Begin
Commit Tran Start
Return 1
End
Else
Begin
Rollback Tran Start
Commit Tran Start
Return 0
End
End
Else
Begin
Commit Tran Start
Return 1
End
End
Else
Begin
Rollback Tran Start
Commit Tran Start
Return 0
End
---删除表
ALTER PROCEDURE [dbo].[Proc_FolderColumnDel]
(
@Columnid int, --要删除目录行的ID
@ColumnParentId int --要删除目录行的父级Id
)
AS
BEGIN
Declare @ColumnPath varchar(1000)
--查看输入的@Columnparentid和@Columnid的父Columnparentid是否匹配
Declare @ParentId int
Select @ParentId=ColumnParentId,@ColumnPath=ColumnPath from Folder where Id=@Columnid
if @ParentId <> @ColumnParentId
return ;
else
--删除指定Id的数据
Delete from Folder where Id=@Columnid
--更新父栏目的子栏目个数
update Folder Set ColumnChild=ColumnChild-1 where Id=@ColumnParentId
--删除指定@Columnid的子栏目
Delete from Folder where ColumnPath like [email=]'%'+@ColumnPath+'%'[/email]
END
-------根据ID获取父级所有名称以及Id
ALTER PROCEDURE [dbo].[Proc_FolderTopName]
(
@id int
)
AS
--根据ID获取父级所有名称以及Id
SET NOCOUNT ON
Declare @FolderName varchar(50); --目录名
Declare @FolderId int --目录ID
Declare @ColumnParentId int --父级id
Declare @loction int
Declare @start int; --开始
Declare @length int; --返回个数
Declare @ColumnPath varchar(900) --文件夹路
--根据ID查询ColumnPath
select @FolderName =FolderName,@ColumnParentId =ColumnParentId,@ColumnPath=ColumnPath from Folder where Id=@id
set @loction = charindex('|',@ColumnPath)
set @length = 0
while @loction <> 0
begin
set @start = @loction + 1
set @loction = charindex('|',@ColumnPath,@start)
set @length = @length +1 --查询结果需要-1
end
declare @i int --级别循环变量
set @i=(@length-1);
create table #tmptable --创建临时表，存放各个级别层id
(
iid int IDENTITY(1,1), --临时自动编号,用于排序
FolderId int, --文件ID
itemname varchar(30)
)
insert into #tmptable(FolderId,itemname)values(@id,@FolderName)
while(@i>0) --循环读取上级ID
begin
select @FolderName=FolderName,@ColumnParentId =ColumnParentId ,@FolderId = Id from Folder where id =@ColumnParentId
insert into #tmptable(FolderId,itemname)values(@FolderId,@FolderName)
set @i=@i -1
end
--按级别从高到低列出上级名称
select FolderId,itemname from #tmptable order by iid desc
drop table #tmptable
---------修改文件夹与文件
ALTER PROCEDURE [dbo].[Proc_UpdateFileFolder]
(
@email varchar(200), --用户
@id int, --文件List表的Id
@folderid int, --移动到的文件夹id
@currId int --当前文件夹
)
AS
BEGIN tran moveinfo
if @folderid ='0'
begin
update List set Folder=@folderid where Id=@id and Email=@email --修改list表的Folder
if @@error <>0 goto errinfo
update Folder set ColumnFileCount = ColumnFileCount -1 where Id=@currId --移开的-1
if @@error <>0 goto errinfo
update Folder set ColumnFileCount = ColumnFileCount + 1 where Email= @email and ColumnParentId='-1' --修改根目录用户文件的个数
if @@error <>0 goto errinfo
end
else
begin
update List set Folder=@folderid where Id=@id
if @@error <>0 goto errinfo
if @currId ='0'
update Folder set ColumnFileCount = ColumnFileCount -1 where Email= @email and ColumnParentId='-1'
else
update Folder set ColumnFileCount = ColumnFileCount -1 where Email= @email and Id=@currId
if @@error <>0 goto errinfo
update Folder set ColumnFileCount = ColumnFileCount + 1 where Id =@folderid --移动到的+1
if @@error <>0 goto errinfo
end
commit tran moveinfo
return 0
errinfo:
Rollback TRAN moveinfo
return -1
好咯。这些是全部的储存过程.很多都有注释.把自己的东西分享出来.希望大家也能分享更多的好东东...以便一起提高...本论坛QQ群:29123371
