无限级别分类的类

 csdn上常看到人问无限级别的数据库设计，平时开发时也经常用到这个，在蓝色理想上看到一篇文章的基础上整理了下面这个东西。 

属性	说明	默认值	读写
version	版本号		读
TableName	数据表的表名	无	写
Id	自增长Id字段名称	"ID"	写
ParentID	所属父类ID字段名称	"ParentID"	写
Condition	附加的查询条件表达式	1=1	写
Conn	使用的数据链接名称	无	写
FieldList	读出的字段列表，用","隔开	所有字段	写
RootValue	根的值（一级分类的父类ID）	0	写
length	取出数组的长度(调用Getlist方法、RootPath方法之后可用)		读
width	取出数组的宽(调用Getlist方法、RootPath方法之后可用)		读
方法	说明	参数
GetList	取得根下面的树数组	无
RootPath(id)	取得从id至根之间的类别数组	id开始的类别id

GetList生成数组大多用在<select><option>这样的地方，也可以用来作为梅花雪的树控件的数据源

RootPath数组可以用在生成

衣服---〉女装---〉女上装---〉衬衫

这样的类别导航栏

调用例子：

  < %
set  class1 = new  NoLimitClass
class1.id = " id "              ' 类别id
class1.TableName = " tb_class "          ' 表
class1.Parentid = " ParentId "          ' 父类id
' class1.Condition="langver='cn'" '额外的条件
class1.Conn = conn             ' 数据连接
class1.FieldList = " id,classname,parentid,langver "   ' 需要取出的字段列表
class1.RootValue = 0              ' 根的值
Classlist = class1.GetList()         ' 开始取数组
ClassNum = class1.length             ' 取出数组的长度(行）
width = class1.width             ' 取出数组的宽度(列）
response.write  " <table border=1> "
response.write  " <tr><td colspan=4 align=center> "   &  class1.version  &   " </td></tr> "
response.write  " <tr><td>id</td><Td>类别名称</td><td>父类id</td><td>语言类别</td></tr> "
for  i = 0   to  ClassNum - 1      ' 循环每行输出
    response.write  " <tr> "
     for  j = 0   to  width - 1      ' 循环每列输出
        response.write  " <td> "
         if  j = 1   then    ' 名称缩进
             for  k = 1   to  ClassList(width,i)     ' 数组的最后一列存储的是该类别的深度,从1开始，可以用来输出缩进，形成一个树型
                response.write  " &nbsp;&nbsp;&nbsp; "
             next
         end   if
        response.write ClassList(j,i)
        response.write  " </td> "
     next
    response.Write( " </tr> " )
next
response.write  " </table> "
% >
< br >
取id12至根之间的路径 </ p >
< p >& lt;% < br >
  Path = class1.RootPath( 12 )  < br >
  % & gt; < br >
   < %
Path = class1.RootPath( 12 )    
ClassNum = class1.length             ' 取出数组的长度(行）
width = class1.width                 ' 取出数组的宽度(列）
response.write  " <table border=1> "
response.write  " <tr><td colspan=4 align=center> "   &  class1.version  &   " </td></tr> "
response.write  " <tr><td>id</td><Td>类别名称</td><td>父类id</td><td>语言类别</td></tr> "
for  i = 0   to  ClassNum - 1      ' 循环每行输出
    response.write  " <tr> "
     for  j = 0   to  width - 1      ' 循环每列输出
        response.write  " <td> "
         if  j = 1   then    ' 名称缩进
             for  k = 1   to  Path(width,i)                    response.write  " &nbsp;&nbsp;&nbsp; "
             next
         end   if
        response.write Path(j,i)
        response.write  " </td> "
     next
    response.Write( " </tr> " )
next
response.write  " </table> "
set  class1 = nothing
% >

 

下面是一个范例数据库：

CREATE TABLE [dbo].[Tb_WebInfo] (
 [Id] [int] Primary Key NOT NULL ,--类别ID
 [ClassName] [Varchar] (200) NULL,  --类别名称 
[ParentID] [Int] NULL,   --所属父类的ID，根类别为0
 [LangVer] [char] (2) NULL  --语言类别
) ON [PRIMARY]
GO

类的代码如下：不限使用，如果觉得好用或有建议请Email：showlin.cn@gmail.com

class NoLimitClass
     private  xxid         ' 类别id字段
     private  xxTableName     ' 存储类别的表
     private  xxParentId     ' 父类id字段
     private  xxCondition     ' 额外的查询条件
     private  xxConn         ' 使用的数据库连接
     private  xxFieldList     ' 取出的字段列表
     Private  xxRootValue     ' 根的值
     Private  xxList()
     Private  i,n,j  ' 临时保存数组长度、递归的深度、列宽
     Public   Property   get  version()
        version = " xx无限级数据库1.0 "
     end property
     public   property   let  id(str)
       xxid  =  str
     end property
     public   property   let  TableName(str)
       xxTableName  =  str
     end property
     public   property   let  ParentId(str)
       xxParentId  =  str
     end property
     public   property   let  condition(str)
       xxCondition  =  str
     end property
     public   property   let  conn(str)
        set  xxConn  =  str
     end property
     public   property   let  FieldList(str)
       xxFieldList  =  str
     end property
     public   property   let  RootValue(num)
       xxRootValue  =  num
     end property
     public   property   Get  length()  ' 数组的长度
       length  =  i
     end property
     public   property   Get  width()  ' 数组列的宽度
       width  =  j
     end property
    

     Private   Sub  Class_Initialize()     ' 初始化变量
        xxid = " id "
        xxParentId = " ParentId "
        i = 0
        j = 0
        n = 0
        xxCondition = " 1=1 "
        xxFieldList = " * "
        xxRootValue = 0
     End Sub


     Private   function  GetSubList(thisid,pid) ' 取得下级ID
         dim  xxSql
         dim  xxRs
         dim  k
         if  pid > 0   then
            xxSql = " select  "   &  xxFieldList  &   "  from  "   &  xxTableName  &   "  where  "   &  xxParentId  &   " = "   &  thisid  &   "  and  "   &  xxCondition
         else
            xxSql = " select  "   &  xxFieldList  &   "  from  "   &  xxTableName  &   "  where  "   &  xxId  &   " = "   &  thisid  &   "  and  "   &  xxCondition
         end   if
         set  xxRs = xxConn.execute(xxSql)
        n = n + 1
        j = xxRs.Fields.count
         do   while   not  xxRs.eof
             Redim  Preserve xxList(j,i)
             for  k = 0   to  j - 1
                xxList(k,i) = xxRs(k)  ' 装入数组中
             next
            xxlist(j,i) = n
            i = i + 1
            thisid = GetSublist(xxRs(xxid), 1 ) ' 这里递归调用,直到最后一个子类
            xxRs.movenext
         loop
        n = n - 1
        xxRs.close
         set  xxRs = nothing

     end function

     public   function  GetList() ' 循环出所有根类
         dim  xxRs
         dim  xxSql
        xxSql = " select  "   &  xxId  &   "  from  "   &  xxTableName  &   "  where  "   &  xxParentId  &   " = "   &  xxRootValue
         set  xxRs = xxConn.execute(xxSql)
         do   while   not  xxRs.eof
             call  GetSubList(xxRs(xxid), 0 )
            xxRs.movenext
         loop
        xxRs.close
         set  xxRs = nothing
        GetList = xxlist
     end function

     public   function  RootPath(id)  ' 取id至根的路径，保存至数组
         dim  xxRs
         dim  xxSql
         dim  xxi,xxj
         dim  ii
        xxSql = " select  "   &  xxFieldList  &   "  from  "   &  xxTableName  &   "  where  "   &  xxId  &   " = "   &  id
         set  xxRs = server.CreateObject( " adodb.recordset " )
        xxRs.open xxSql,xxConn, 1 , 1
         if   not  xxRs.eof  then
            xxi = xxRs.fields.count
            xxj = 0
             Redim  Preserve xxRootPath(xxi,xxj)
             for  ii = 0   to  xxi - 1
                xxRootPath(ii,xxj) = xxRs(ii)
             next
             do  until xxRs(xxParentId) <= xxRootValue
                xxSql = " select  "   &  xxFieldList  &   "  from  "   &  xxTableName  &   "  where  "   &  xxId  &   " = "   &  xxRs(xxParentId)
                xxRs.close
                xxj = xxj + 1
                 Redim  Preserve xxRootPath(xxi,xxj)
                 set  xxRs = xxConn.execute(xxSql)
                 for  ii = 0   to  xxi - 1
                    xxRootPath(ii,xxj) = xxRs(ii)
                 next
             loop
         end   if
        xxRs.close
        i = xxj + 1
        j = xxi
         set  xxRs = nothing
        RootPath = xxRootPath
     end function
    
end  class