ASP 多级分类（支持多频道，多用户，大数据量）
来源：互联网 发布：oracle 表添加字段sql 编辑：程序博客网 时间：2024/06/05 10:04
数据库部分
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_del]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_del]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_return_childs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_return_childs]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_brer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_select_brer]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_child]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_select_child]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_parents]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_select_parents]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_root]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_select_root]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_tree]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_select_tree]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sort_kernel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[cwl_sort_kernel]
GO
CREATE TABLE [dbo].[cwl_sort_kernel] (
[guid] [int] IDENTITY (1, 1) NOT NULL ,
[s_id] [int] NOT NULL ,
[s_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[s_parents] [varchar] (250) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[s_childs] [text] COLLATE Chinese_PRC_CI_AS NOT NULL ,
[s_fid] [int] NOT NULL ,
[s_star] [int] NOT NULL ,
[channelid] [int] NOT NULL ,
[userid] [int] NOT NULL ,
[s_order] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[s_ordertree] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[cwl_sort_kernel] WITH NOCHECK ADD
CONSTRAINT [PK_CWL_SORT_KERNEL_guid] PRIMARY KEY CLUSTERED
(
[guid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[cwl_sort_kernel] ADD
CONSTRAINT [DF_CWL_SORT_KERNEL_c_id] DEFAULT (0) FOR [s_id],
CONSTRAINT [DF_CWL_SORT_KERNEL_c_name] DEFAULT (N'') FOR [s_name],
CONSTRAINT [DF_cwl_sort_kernel_s_parents] DEFAULT (N'') FOR [s_parents],
CONSTRAINT [DF_cwl_sort_kernel_s_childs] DEFAULT (N'') FOR [s_childs],
CONSTRAINT [DF_cwl_sort_kernel_c_fid] DEFAULT (0) FOR [s_fid],
CONSTRAINT [DF_cwl_sort_kernel_c_star] DEFAULT (1) FOR [s_star],
CONSTRAINT [DF_cwl_sort_kernel_channelid] DEFAULT (0) FOR [channelid],
CONSTRAINT [DF_cwl_sort_kernel_userid] DEFAULT (0) FOR [userid]
GO
CREATE INDEX [IX_CWL_SORT_KERNEL_parents] ON [dbo].[cwl_sort_kernel]([s_parents]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_fid] ON [dbo].[cwl_sort_kernel]([s_fid]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_star] ON [dbo].[cwl_sort_kernel]([s_star]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_channelid] ON [dbo].[cwl_sort_kernel]([channelid]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_userid] ON [dbo].[cwl_sort_kernel]([userid]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_id] ON [dbo].[cwl_sort_kernel]([s_id]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_order] ON [dbo].[cwl_sort_kernel]([s_order]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_ordertree] ON [dbo].[cwl_sort_kernel]([s_ordertree]) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--1、删除分类
CREATE PROCEDURE cwl_sp_sort_del
@userid int, @channelid int, @s_id int
AS
DECLARE @s_childs varchar(7896), @s_parents varchar(250), @SQL varchar(8000)
BEGIN
SELECT @s_childs = s_childs,@s_parents = s_parents FROM dbo.cwl_sort_kernel WHERE s_id = @s_id AND channelid = @channelid AND userid = @userid
SET @SQL = 'DELETE FROM dbo.cwl_sort_kernel WHERE channelid = '+cast(@channelid as varchar(8))+' AND userid = '+cast(@userid as varchar(8))+' AND s_id IN ('+CAST(@s_childs AS varchar(7896))+')'
EXECute (@SQL)
SET @SQL = 'UPDATE dbo.cwl_sort_kernel SET s_childs = REPLACE(s_childs,'',''+s_id,'') WHERE channelid = '+cast(@channelid as varchar(8))+' AND userid = '+cast(@userid as varchar(8))+' AND s_id IN ('+CAST(@s_parents AS varchar(250))+')'
EXECute (@SQL)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--5、该大类的所有后代 取内容列表
CREATE PROCEDURE cwl_sp_sort_return_childs
@userid int, @channelid int,
@s_id int,
@s_childs varchar(7896) output
AS
--set nocount on
SELECT @s_childs = s_childs FROM dbo.cwl_sort_kernel WHERE s_id = @s_id AND channelid = @channelid AND userid = @userid
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--1、兄弟类
CREATE PROCEDURE cwl_sp_sort_select_brer
@userid int =0, @channelid int = 0, @s_id int
AS
DECLARE @s_fid int, @SQL varchar(300)
SELECT @s_fid = s_fid FROM dbo.cwl_sort_kernel WHERE s_id = @s_id AND channelid = @channelid AND userid = @userid
SET @SQL = 'SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = '+CAST(@channelid AS varchar(8))+' AND userid = '+CAST(@userid AS varchar(8))+' AND s_fid = '+CAST(@s_fid AS varchar(8))+' ORDER BY s_order'
exec (@SQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--1、子类
CREATE PROCEDURE cwl_sp_sort_select_child
@userid int, @channelid int, @s_id int
AS
SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE s_fid = @s_id AND channelid = @channelid AND userid = @userid ORDER BY s_order
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--1、继承
CREATE PROCEDURE cwl_sp_sort_select_parents
@userid int =0, @channelid int = 0, @s_id int
AS
DECLARE @s_parents varchar(250),@SQL varchar(1000)
BEGIN
SELECT @s_parents = s_parents FROM dbo.cwl_sort_kernel WHERE s_id = @s_id AND userid = @userid AND channelid = @channelid
SET @SQL = 'SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = '+CAST(@channelid AS varchar(8))+' AND userid = '+CAST(@userid AS varchar(8))+' AND s_id in ('+@s_parents+') ORDER BY s_parents'
EXEC (@SQL)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--1、根类
CREATE PROCEDURE cwl_sp_sort_select_root
@userid int =0, @channelid int = 0
AS
SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = @channelid AND userid = @userid AND s_star = 1 ORDER BY s_order
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--1、分类树
CREATE PROCEDURE cwl_sp_sort_select_tree
@userid int =0, @channelid int = 0, @s_star int = 0
AS
if @s_star = 0
SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = @channelid AND userid = @userid ORDER BY s_ordertree
else
SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = @channelid AND userid = @userid AND s_star <= @s_star ORDER BY s_ordertree
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
exec sp_addextendedproperty N'MS_Description', N'分类归属频道', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N'channelid'
GO
exec sp_addextendedproperty N'MS_Description', N'子，包括自己和所有延伸', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_childs'
GO
exec sp_addextendedproperty N'MS_Description', N'上级id', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_fid'
GO
exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_id'
GO
exec sp_addextendedproperty N'MS_Description', N'分类名称', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_name'
GO
exec sp_addextendedproperty N'MS_Description', N'继承关系，由根到自己本身', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_parents'
GO
exec sp_addextendedproperty N'MS_Description', N'当前级数', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_star'
GO
exec sp_addextendedproperty N'MS_Description', N'分类归属用户', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N'userid'
GO
drop procedure [dbo].[cwl_sp_sort_del]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_return_childs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_return_childs]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_brer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_select_brer]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_child]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_select_child]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_parents]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_select_parents]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_root]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_select_root]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_tree]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cwl_sp_sort_select_tree]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sort_kernel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[cwl_sort_kernel]
GO
CREATE TABLE [dbo].[cwl_sort_kernel] (
[guid] [int] IDENTITY (1, 1) NOT NULL ,
[s_id] [int] NOT NULL ,
[s_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[s_parents] [varchar] (250) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[s_childs] [text] COLLATE Chinese_PRC_CI_AS NOT NULL ,
[s_fid] [int] NOT NULL ,
[s_star] [int] NOT NULL ,
[channelid] [int] NOT NULL ,
[userid] [int] NOT NULL ,
[s_order] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[s_ordertree] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[cwl_sort_kernel] WITH NOCHECK ADD
CONSTRAINT [PK_CWL_SORT_KERNEL_guid] PRIMARY KEY CLUSTERED
(
[guid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[cwl_sort_kernel] ADD
CONSTRAINT [DF_CWL_SORT_KERNEL_c_id] DEFAULT (0) FOR [s_id],
CONSTRAINT [DF_CWL_SORT_KERNEL_c_name] DEFAULT (N'') FOR [s_name],
CONSTRAINT [DF_cwl_sort_kernel_s_parents] DEFAULT (N'') FOR [s_parents],
CONSTRAINT [DF_cwl_sort_kernel_s_childs] DEFAULT (N'') FOR [s_childs],
CONSTRAINT [DF_cwl_sort_kernel_c_fid] DEFAULT (0) FOR [s_fid],
CONSTRAINT [DF_cwl_sort_kernel_c_star] DEFAULT (1) FOR [s_star],
CONSTRAINT [DF_cwl_sort_kernel_channelid] DEFAULT (0) FOR [channelid],
CONSTRAINT [DF_cwl_sort_kernel_userid] DEFAULT (0) FOR [userid]
GO
CREATE INDEX [IX_CWL_SORT_KERNEL_parents] ON [dbo].[cwl_sort_kernel]([s_parents]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_fid] ON [dbo].[cwl_sort_kernel]([s_fid]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_star] ON [dbo].[cwl_sort_kernel]([s_star]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_channelid] ON [dbo].[cwl_sort_kernel]([channelid]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_userid] ON [dbo].[cwl_sort_kernel]([userid]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_id] ON [dbo].[cwl_sort_kernel]([s_id]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_order] ON [dbo].[cwl_sort_kernel]([s_order]) ON [PRIMARY]
GO
CREATE INDEX [IX_cwl_sort_kernel_ordertree] ON [dbo].[cwl_sort_kernel]([s_ordertree]) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--1、删除分类
CREATE PROCEDURE cwl_sp_sort_del
@userid int, @channelid int, @s_id int
AS
DECLARE @s_childs varchar(7896), @s_parents varchar(250), @SQL varchar(8000)
BEGIN
SELECT @s_childs = s_childs,@s_parents = s_parents FROM dbo.cwl_sort_kernel WHERE s_id = @s_id AND channelid = @channelid AND userid = @userid
SET @SQL = 'DELETE FROM dbo.cwl_sort_kernel WHERE channelid = '+cast(@channelid as varchar(8))+' AND userid = '+cast(@userid as varchar(8))+' AND s_id IN ('+CAST(@s_childs AS varchar(7896))+')'
EXECute (@SQL)
SET @SQL = 'UPDATE dbo.cwl_sort_kernel SET s_childs = REPLACE(s_childs,'',''+s_id,'') WHERE channelid = '+cast(@channelid as varchar(8))+' AND userid = '+cast(@userid as varchar(8))+' AND s_id IN ('+CAST(@s_parents AS varchar(250))+')'
EXECute (@SQL)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--5、该大类的所有后代 取内容列表
CREATE PROCEDURE cwl_sp_sort_return_childs
@userid int, @channelid int,
@s_id int,
@s_childs varchar(7896) output
AS
--set nocount on
SELECT @s_childs = s_childs FROM dbo.cwl_sort_kernel WHERE s_id = @s_id AND channelid = @channelid AND userid = @userid
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--1、兄弟类
CREATE PROCEDURE cwl_sp_sort_select_brer
@userid int =0, @channelid int = 0, @s_id int
AS
DECLARE @s_fid int, @SQL varchar(300)
SELECT @s_fid = s_fid FROM dbo.cwl_sort_kernel WHERE s_id = @s_id AND channelid = @channelid AND userid = @userid
SET @SQL = 'SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = '+CAST(@channelid AS varchar(8))+' AND userid = '+CAST(@userid AS varchar(8))+' AND s_fid = '+CAST(@s_fid AS varchar(8))+' ORDER BY s_order'
exec (@SQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--1、子类
CREATE PROCEDURE cwl_sp_sort_select_child
@userid int, @channelid int, @s_id int
AS
SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE s_fid = @s_id AND channelid = @channelid AND userid = @userid ORDER BY s_order
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--1、继承
CREATE PROCEDURE cwl_sp_sort_select_parents
@userid int =0, @channelid int = 0, @s_id int
AS
DECLARE @s_parents varchar(250),@SQL varchar(1000)
BEGIN
SELECT @s_parents = s_parents FROM dbo.cwl_sort_kernel WHERE s_id = @s_id AND userid = @userid AND channelid = @channelid
SET @SQL = 'SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = '+CAST(@channelid AS varchar(8))+' AND userid = '+CAST(@userid AS varchar(8))+' AND s_id in ('+@s_parents+') ORDER BY s_parents'
EXEC (@SQL)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--1、根类
CREATE PROCEDURE cwl_sp_sort_select_root
@userid int =0, @channelid int = 0
AS
SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = @channelid AND userid = @userid AND s_star = 1 ORDER BY s_order
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
/*通用分类存储过程*/
--1、分类树
CREATE PROCEDURE cwl_sp_sort_select_tree
@userid int =0, @channelid int = 0, @s_star int = 0
AS
if @s_star = 0
SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = @channelid AND userid = @userid ORDER BY s_ordertree
else
SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = @channelid AND userid = @userid AND s_star <= @s_star ORDER BY s_ordertree
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
exec sp_addextendedproperty N'MS_Description', N'分类归属频道', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N'channelid'
GO
exec sp_addextendedproperty N'MS_Description', N'子，包括自己和所有延伸', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_childs'
GO
exec sp_addextendedproperty N'MS_Description', N'上级id', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_fid'
GO
exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_id'
GO
exec sp_addextendedproperty N'MS_Description', N'分类名称', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_name'
GO
exec sp_addextendedproperty N'MS_Description', N'继承关系，由根到自己本身', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_parents'
GO
exec sp_addextendedproperty N'MS_Description', N'当前级数', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_star'
GO
exec sp_addextendedproperty N'MS_Description', N'分类归属用户', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N'userid'
GO
分类数据调用类
'*****************************************************************************************
' 创 建 人： cuiwl (longchengfy@hotmail.com)
' 创建时间： 2005-10-19
' 修改时间： 2006-7-25
' 说 明： 基于数据库的无限级分类
' 输出相关数据，执行相关操作（存储过程） 创建通用分类表 CWL_SORT_KERNEL
'*****************************************************************************************
Class sort
Public conn, cmd, rs '设置connection对象
Public SQL '设置sql语句
Public execount '操作的纪录数
public userid
public channelid
public errstr
Private i, j
Private Sub class_initialize()
End Sub
Private Sub class_terminate()
End Sub
Private sub Rwrite(par)
response.write (par)
end sub
Private Function sco(byval objstr)
Set sco = server.CreateObject (objstr)
End Function
public Function select_tree(byval s_star) '输出分类树数据
dim tmparr
cmd.commandtext = "cwl_sp_sort_select_tree"
cmd.commandtype = 4
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
cmd.parameters("@s_star") = s_star
Set rs = cmd.execute
if rs.eof then
tmparr = empty
else
tmparr = rs.getrows()
end if
Set rs = Nothing
select_tree = tmparr
End Function
public Function select_root() '输出根类列表
dim tmparr
cmd.commandtext = "cwl_sp_sort_select_root"
cmd.commandtype = 4
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
Set rs = cmd.execute
if rs.eof then
tmparr = empty
else
tmparr = rs.getrows()
end if
Set rs = Nothing
select_root = tmparr
End Function
public Function select_brer(byval s_id) '输出兄弟类
dim tmparr
cmd.commandtext = "cwl_sp_sort_select_brer"
cmd.commandtype = 4
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
cmd.parameters("@s_id") = s_id
Set rs = cmd.execute
if rs.eof then
tmparr = empty
else
tmparr = rs.getrows()
end if
Set rs = Nothing
select_brer = tmparr
End Function
public Function select_parents(byval s_id) '输出前辈分类列表
dim tmparr
cmd.commandtext = "cwl_sp_sort_select_parents"
cmd.commandtype = 4
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
cmd.parameters("@s_id") = s_id
Set rs = cmd.execute
if rs.state = 0 then
tmparr = empty
else
tmparr = rs.getrows()
end if
Set rs = Nothing
select_parents = tmparr
End Function
public Function select_child(byval s_id) '输出子分类
dim tmparr
cmd.commandtext = "cwl_sp_sort_select_child"
cmd.commandtype = 4
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
cmd.parameters("@s_id") = s_id
Set rs = cmd.execute
if rs.eof then
tmparr = empty
else
tmparr = rs.getrows()
end if
Set rs = Nothing
select_child = tmparr
End Function
public Function return_childs(byval s_id) '返回所有后代id
dim tmpvalue
cmd.commandtext = "cwl_sp_sort_return_childs"
cmd.commandtype = 4
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
cmd.parameters("@s_id") = s_id
cmd.execute
tmpvalue = cmd.parameters("@s_childs")
return_childs = tmpvalue
End Function
Private Function code_int(tmpstr) ' 整数处理
If Not isnumeric(tmpstr) Then
tmpstr = 0
Else
tmpstr = int(tmpstr)
End If
code_int = tmpstr
End Function
public Function del(byval s_id) '删除分类
s_id = code_int(s_id)
dim bitvar:bitvar = false
cmd.commandtext = "cwl_sp_sort_del" '存储过程名成
cmd.commandtype = 4 '类型为存储过程
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
cmd.parameters("@s_id") = s_id '给参数赋值
cmd.execute execount '执行操作语句并得出影响总数
bitvar = true
del = bitvar
End Function
public Function delbatch(byval s_idarr) '批量删除分类
dim bitvar:bitvar = false
dim tmpexecount:tmpexecount = ubound(s_idarr)+1
for i = 0 to ubound(s_idarr)
if del(s_idarr(i)) = false then exit Function
if execount = -1 then tmpexecount = tmpexecount + execount
if execount > 1 then tmpexecount = tmpexecount + execount-1
next
execount = tmpexecount
bitvar = true
delbatch = bitvar
End Function
End Class
'*****************************************************************************************
' 创 建 人： cuiwl (longchengfy@hotmail.com)
' 创建时间： 2005-10-19
' 修改时间： 2006-7-25
' 说 明： 基于数据库的无限级分类
' 输出相关数据，执行相关操作（存储过程） 创建通用分类表 CWL_SORT_KERNEL
'*****************************************************************************************
Class sort
Public conn, cmd, rs '设置connection对象
Public SQL '设置sql语句
Public execount '操作的纪录数
public userid
public channelid
public errstr
Private i, j
Private Sub class_initialize()
End Sub
Private Sub class_terminate()
End Sub
Private sub Rwrite(par)
response.write (par)
end sub
Private Function sco(byval objstr)
Set sco = server.CreateObject (objstr)
End Function
public Function select_tree(byval s_star) '输出分类树数据
dim tmparr
cmd.commandtext = "cwl_sp_sort_select_tree"
cmd.commandtype = 4
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
cmd.parameters("@s_star") = s_star
Set rs = cmd.execute
if rs.eof then
tmparr = empty
else
tmparr = rs.getrows()
end if
Set rs = Nothing
select_tree = tmparr
End Function
public Function select_root() '输出根类列表
dim tmparr
cmd.commandtext = "cwl_sp_sort_select_root"
cmd.commandtype = 4
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
Set rs = cmd.execute
if rs.eof then
tmparr = empty
else
tmparr = rs.getrows()
end if
Set rs = Nothing
select_root = tmparr
End Function
public Function select_brer(byval s_id) '输出兄弟类
dim tmparr
cmd.commandtext = "cwl_sp_sort_select_brer"
cmd.commandtype = 4
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
cmd.parameters("@s_id") = s_id
Set rs = cmd.execute
if rs.eof then
tmparr = empty
else
tmparr = rs.getrows()
end if
Set rs = Nothing
select_brer = tmparr
End Function
public Function select_parents(byval s_id) '输出前辈分类列表
dim tmparr
cmd.commandtext = "cwl_sp_sort_select_parents"
cmd.commandtype = 4
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
cmd.parameters("@s_id") = s_id
Set rs = cmd.execute
if rs.state = 0 then
tmparr = empty
else
tmparr = rs.getrows()
end if
Set rs = Nothing
select_parents = tmparr
End Function
public Function select_child(byval s_id) '输出子分类
dim tmparr
cmd.commandtext = "cwl_sp_sort_select_child"
cmd.commandtype = 4
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
cmd.parameters("@s_id") = s_id
Set rs = cmd.execute
if rs.eof then
tmparr = empty
else
tmparr = rs.getrows()
end if
Set rs = Nothing
select_child = tmparr
End Function
public Function return_childs(byval s_id) '返回所有后代id
dim tmpvalue
cmd.commandtext = "cwl_sp_sort_return_childs"
cmd.commandtype = 4
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
cmd.parameters("@s_id") = s_id
cmd.execute
tmpvalue = cmd.parameters("@s_childs")
return_childs = tmpvalue
End Function
Private Function code_int(tmpstr) ' 整数处理
If Not isnumeric(tmpstr) Then
tmpstr = 0
Else
tmpstr = int(tmpstr)
End If
code_int = tmpstr
End Function
public Function del(byval s_id) '删除分类
s_id = code_int(s_id)
dim bitvar:bitvar = false
cmd.commandtext = "cwl_sp_sort_del" '存储过程名成
cmd.commandtype = 4 '类型为存储过程
cmd.parameters("@userid") = userid
cmd.parameters("@channelid") = channelid
cmd.parameters("@s_id") = s_id '给参数赋值
cmd.execute execount '执行操作语句并得出影响总数
bitvar = true
del = bitvar
End Function
public Function delbatch(byval s_idarr) '批量删除分类
dim bitvar:bitvar = false
dim tmpexecount:tmpexecount = ubound(s_idarr)+1
for i = 0 to ubound(s_idarr)
if del(s_idarr(i)) = false then exit Function
if execount = -1 then tmpexecount = tmpexecount + execount
if execount > 1 then tmpexecount = tmpexecount + execount-1
next
execount = tmpexecount
bitvar = true
delbatch = bitvar
End Function
End Class
'*****************************************************************************************
'插入测试数据脚本
declare @i int
set @i = 10000
while @i < 19999
begin
insert into cwl_sort_kernel (s_id, s_name, s_parents, s_childs, s_order, s_ordertree) values (@i, '北京', cast(@i as varchar(210)), cast(@i as varchar(8)), cast(@i as varchar(8)))
set @i = @i + 1
end
set @i = 10000
while @i < 19999
begin
insert into cwl_sort_kernel (s_id, s_name, s_parents, s_childs, s_order, s_ordertree) values (@i, '北京', cast(@i as varchar(210)), cast(@i as varchar(8)), cast(@i as varchar(8)))
set @i = @i + 1
end
简单的调用示例
dim objsort: set objsort = new sort
setcmd 0
with objsort
' set .conn = conn
' set .cmd = cmd
' .userid = 0
' .channelid = 0
'
' rwrite "select_tree<br/>"
' rsarr = .select_tree(2)
' if isarray(rsarr) then
' for i = 0 to ubound(rsarr,2)
' rwrite rsarr(2,i)&"<br/>"
' if i = 100 then exit for
' next
' end if
' response.flush
'
' rwrite "select_root<br/>"
' rsarr = .select_root
' if isarray(rsarr) then
' for i = 0 to ubound(rsarr,2)
' rwrite rsarr(1,i)&"<br/>"
' if i = 10 then exit for
' next
' end if
' response.flush
'
' rwrite "select_brer<br/>"
' rsarr = .select_brer(2)
' if isarray(rsarr) then
' for i = 0 to ubound(rsarr,2)
' rwrite rsarr(1,i)&"<br/>"
' if i = 10 then exit for
' next
' end if
' response.flush
'
' rwrite "select_child<br/>"
' rsarr = .select_child(2)
' if isarray(rsarr) then
' for i = 0 to ubound(rsarr,2)
' rwrite rsarr(1,i)&"<br/>"
' if i = 10 then exit for
' next
' end if
' response.flush
'
' rwrite "select_parents<br/>"
' rsarr = .select_parents(2)
' if isarray(rsarr) then
' for i = 0 to ubound(rsarr,2)
' rwrite rsarr(1,i)&"<br/>"
' if i = 10 then exit for
' next
' end if
' response.flush
'
' rwrite "return_childs<br/>"
' rwrite .return_childs(2)&"<br/>"
' response.flush
' dim s_idarr(100)
' for i = 0 to ubound(s_idarr)
' s_idarr(i) = i
' next
' rwrite "delbatch<br/>"
'' rwrite .del(1)&"<br/>"
' rwrite .delbatch(s_idarr)&"<br/>"
' rwrite .execount&"<br/>"
' response.flush
end with
setcmd 0
with objsort
' set .conn = conn
' set .cmd = cmd
' .userid = 0
' .channelid = 0
'
' rwrite "select_tree<br/>"
' rsarr = .select_tree(2)
' if isarray(rsarr) then
' for i = 0 to ubound(rsarr,2)
' rwrite rsarr(2,i)&"<br/>"
' if i = 100 then exit for
' next
' end if
' response.flush
'
' rwrite "select_root<br/>"
' rsarr = .select_root
' if isarray(rsarr) then
' for i = 0 to ubound(rsarr,2)
' rwrite rsarr(1,i)&"<br/>"
' if i = 10 then exit for
' next
' end if
' response.flush
'
' rwrite "select_brer<br/>"
' rsarr = .select_brer(2)
' if isarray(rsarr) then
' for i = 0 to ubound(rsarr,2)
' rwrite rsarr(1,i)&"<br/>"
' if i = 10 then exit for
' next
' end if
' response.flush
'
' rwrite "select_child<br/>"
' rsarr = .select_child(2)
' if isarray(rsarr) then
' for i = 0 to ubound(rsarr,2)
' rwrite rsarr(1,i)&"<br/>"
' if i = 10 then exit for
' next
' end if
' response.flush
'
' rwrite "select_parents<br/>"
' rsarr = .select_parents(2)
' if isarray(rsarr) then
' for i = 0 to ubound(rsarr,2)
' rwrite rsarr(1,i)&"<br/>"
' if i = 10 then exit for
' next
' end if
' response.flush
'
' rwrite "return_childs<br/>"
' rwrite .return_childs(2)&"<br/>"
' response.flush
' dim s_idarr(100)
' for i = 0 to ubound(s_idarr)
' s_idarr(i) = i
' next
' rwrite "delbatch<br/>"
'' rwrite .del(1)&"<br/>"
' rwrite .delbatch(s_idarr)&"<br/>"
' rwrite .execount&"<br/>"
' response.flush
end with
- ASP 多级分类（支持多频道，多用户，大数据量）
- QBlog博客 V2.5 版本发布 增加健康频道[支持多语言、多用户、多数据库、目录级URL]
- QBlog博客 V2.5 版本发布 增加健康频道[支持多语言、多用户、多数据库、目录级URL]
- ecshop导航栏自动显示三级或多级子栏目,多级频道分类,并实现css高亮显示
- asp中大数据量分页代码示例
- ASP.NET大数据量查询分页例子
- ASP.NET大数据量查询分页例子
- ASP.net大数据量下的分页
- C# ASP.Net 多级分类Treeview的实现
- asp.net中绘制大数据量的可交互的图表（1）-学习
- asp.net中绘制大数据量的可交互的图表（2）-.net导出服务
- Displaytag1.1支持在外部实现大数据量分页
- Windows Server AppFabric Caching支持大数据量的配置
- 在集群上支持数据库大数据量导出
- PHP导出数据到Excel，支持大数据量
- MySQL随机获取数据的方法，支持大数据量
- MySQL随机获取数据的方法，支持大数据量
- 关于SSM+mysql框架大数据量的支持优化
- 多线程笔记
- 将秒数转换成天时分秒的格式
- Struts2 Action
- 为属性页中的对话框添加工具栏，及添加工具栏tool tip & Update command ui
- RandomAcessFile、MappedByteBuffer和缓冲读/写文件
- ASP 多级分类（支持多频道，多用户，大数据量）
- IncrediBuild工具使用及设置
- 《 Unix环境高级编程 》笔记
- ARM体系结构与编程学习（五）
- JavaScript 显示当然日期和时间，年月日星期和时间
- asp.net 数据直接输出为下载
- C#：编辑距离计算及更新维基百科
- 存在于一个表而不存在于另一个表中的数据
- XSD生成 C# 类，关于decimal 类型在webservice 不能显示值的问题
