<%
'*********************************************************************************************
' 创 建 人： cuiwl (longchengfy@hotmail.com)
' 创建时间： 2005-10-19
' 修改时间： 2006-7-25
' 说    明： 基于数据库的无限级分类
' 输出相关数据，执行相关操作（存储过程） 创建通用分类表 CWL_SORT_KERNEL
'*********************************************************************************************
Class sort_mssql
Public conn, cmd, rs         '设置connection对象
public userid
public channelid
Public execount       '操作的纪录数
public errstr
public islarge        '判断是否为大数据量，如果为true那么输出sql语句
Public SQL            '设置sql语句

Private i, j

Private Sub class_initialize()
islarge = false
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
if not islarge then
    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
    else
     sql = "SELECT s_id, s_name, s_star FROM cwl_sort_kernel WHERE channelid = " & channelid & " AND userid = " & userid & " and s_star <= " & s_star & " ORDER BY s_ordertree"
    end if
End Function

public Function select_root()  '输出根类列表
dim tmparr
if not islarge then
    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
    else
     sql = "SELECT s_id, s_name, s_star FROM cwl_sort_kernel WHERE channelid = " & channelid & " AND userid = " & userid & " AND s_star = 1 ORDER BY s_order"
    end if
End Function

public Function select_brer(byval s_id)  '输出兄弟类
dim tmparr
if not islarge then
    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
    else
     dim s_fid
     s_fid = "(select s_fid FROM cwl_sort_kernel WHERE channelid = " & channelid & " AND userid = " & userid & " AND s_id = " & s_id & " ORDER BY s_order)"
     sql = "SELECT s_id, s_name, s_star FROM cwl_sort_kernel WHERE channelid = " & channelid & " AND userid = " & userid & " AND s_fid = " & s_fid & " ORDER BY s_order"
    end if
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
if not islarge then
    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
    else
     sql = "SELECT s_id, s_name, s_star FROM cwl_sort_kernel WHERE channelid = " & channelid & " AND userid = " & userid & " AND s_fid = " & s_id & " ORDER BY s_order"
    end if
End Function

public Function return_childs(byval s_id)  '返回所有后代id
dim tmpvalue
if not islarge then
    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")
    else
     sql = "SELECT s_childs FROM cwl_sort_kernel WHERE channelid = " & channelid & " AND userid = " & userid & " AND s_id = " & s_id & " ORDER BY s_order"
tmpvalue = conn.execute(sql)(0)
    end if
    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
if not islarge then
    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        '执行操作语句并得出影响总数
    else
SQL = "SELECT s_childs, s_parents FROM cwl_sort_kernel WHERE channelid = " & channelid & " AND userid = " & userid & " AND s_id = " & s_id
dim s_childs, s_parents
rs.open sql, conn, 0, 1
if not rs.eof then
s_childs = rs(0)
s_parents = rs(1)
else
s_childs = 0
s_parents = 0
end if
rs.close
Conn.Begintrans
SQL = "DELETE FROM cwl_sort_kernel WHERE channelid = " & channelid & " AND userid = " & userid & " AND  s_id IN (" & s_childs & ");"
conn.execute SQL, execount
sql = "select s_childs from cwl_sort_kernel where channelid = " & channelid & " AND userid = " & userid & " AND  s_id IN (" & s_parents & ");"
rs.open sql, conn, 2, 3
do while not rs.eof
rs(0) = REPLACE(rs(0),"," & s_id,"")
rs.movenext
loop
rs.updatebatch
rs.close
Conn.CommitTrans
    end if
if execount = -1 then execount = 0
    if execount > 0 then
    bitvar = true
    else
     errstr = "无数据！"
    end if
    del = bitvar
End Function

public Function delbatch(byval s_idarr)  '批量删除分类
dim bitvar:bitvar = false
dim tmpexecount:tmpexecount = 0
for i = 0 to ubound(s_idarr)
del s_idarr(i)
tmpexecount = tmpexecount + execount
next
execount = tmpexecount
    if execount > 0 then
    bitvar = true
    else
     errstr = "无数据！"
    end if
    delbatch = bitvar
End Function
End Class
'*********************************************************************************************
'sql = "select s_id, s_name, s_parents, s_childs, s_fid, s_star, s_order, s_ordertree, userid, channelid frome cwl_sort_kernel where "

'插入测试数据
'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)), cast(@i as varchar(8)))
'set @i = @i + 1
'end

'数据结构说明
'tablename: "cwl_sort_kernel"
'tablefields: "s_id, s_name, s_parents, s_childs, s_fid, s_star, userid, channelid, s_order, s_ordertree" "guid"

%>

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_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_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 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 cwl_sort_kernel WHERE s_id = @s_id AND channelid = @channelid AND userid = @userid
SET @SQL = 'SELECT s_id, s_name, s_star FROM 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_del
@userid int=0, @channelid int=0, @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 cwl_sort_kernel WHERE s_id = @s_id AND  channelid = @channelid AND userid = @userid
SET @SQL = 'DELETE FROM 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 cwl_sort_kernel SET s_childs = REPLACE(s_childs,'',''+cast(s_id as  varchar(8)),'') 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=0, @channelid int=0,
@s_id int,
@s_childs varchar(7896) output
AS
--set nocount on
SELECT @s_childs = s_childs FROM 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 ON
GO
SET ANSI_NULLS OFF
GO


/*通用分类存储过程*/
--1、子类
CREATE PROCEDURE cwl_sp_sort_select_child
@userid int=0, @channelid int=0, @s_id int
AS
SELECT  s_id, s_name, s_star FROM 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 cwl_sort_kernel WHERE  s_id = @s_id  AND userid = @userid AND channelid = @channelid
SET @SQL = 'SELECT  s_id, s_name, s_star FROM 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 s_id, s_name, s_star FROM 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 s_id, s_name, s_star FROM cwl_sort_kernel WHERE channelid = @channelid AND userid = @userid ORDER BY s_ordertree
else
SELECT s_id, s_name, s_star FROM 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

简单的调用示例

dim objsort: set objsort = new sort_mssql
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

哇,强人回来了.顶一个.

看来龙哥最近头发又白了不少，真是可喜可忧，要注意身体哦！

http://www.52web.org/user/user_sort_edit.asp

不错的东东欧国家