(原创)通用数据分页类

通用分页类,以后写分页显示数据时就轻松多啦.直接调用此类,然后再Execute即可以取得当前页的所有数据.
此类所做的工作是只取得当前页的数据,和总页数和总记录数等等数据.

ASP代码:

<%
'/*****************************分页显示类**************************
'/* 作者：哇哇鱼
'/* 日期：2004年11月18日
'/* 作用：取得某一页的数据并返回给外部
'/* 说明示例：
'/* Dim MyPage=New PageClass
'/* MyPage.Conn=Conn                '设置连接对象
'/* MyPage.PageSize=20                 '设置一页显示多少条数据 (默认为10条)
'/* MyPage.CurPage=2                   '设置当前要显示的页码
'/*''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'/* MyPage.TableName="Member"       '设置表名
'/* MyPage.Fields="ID,MemberName,MemberPass"   '设置显示字段列表
'/* MyPage.Condition="ID>100"          '设置查询条件
'/* MyPage.OrderBy="ID DESC"           '设置排序条件（一定要设置该属性)
'/* Set PageRs=MyPage.Execute          '返回当前第2页的数据(RecordSet对象)，如果出错则返回Nothing值
'/*''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'/*'以上的定义也可以用以下的方法：ExecuteBy("表名","字段列表","查询条件","排序条件")
'/* Set PageRs=MyPage.ExecuteBy("Member","ID,MemberName,MemberPass","ID>100","ID DESC")
'/*''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'/* PageCount=MyPage.PageCount         '返回页码总数
'/* RecordCount=MyPage.RecordCount     '返回记录总数
'/* NextPage=MyPage.NextPage           '返回下页的页码
'/* PrePage=MyPage.PrePage             '返回上一页的页码
'/*****************************************************************
Class PageClass
 Private Connection               '连接数据库的外部Connection对象
 Private Rs
 Private List_Fields
 Private Table_Name
 Private Query_Where
 Private OrderBy_SQL              '字段排序语句部分
 Private Page_Count               '返回当前查询的记录页总数
 Private Page_Size                '设置一页显示多少条的记录
 Private Cur_Page                 '设置当前的页码
 Private Record_Count             '返回当前查询的记录总数
 '/****************设置Connection对象****************************
 Public Property Let Conn(ByRef ObjConn)
  Set Connection=ObjConn
 End Property
 Public Property Get Conn()
  Set Conn=Connection
 End Property
 '/****************End******************************************

 '/****************设置查询SQL语句*******************************
 ''查询显示字段
 Public Property Let Fields(ByVal Value)
  List_Fields=Value
 End Property
 Public Property Get Fields()
  Fields=List_Fields
 End Property
 ''查询表名
 Public Property Let TableName(ByVal Value)
  Table_Name=Value
 End Property
 Public Property Get TableName()
  TableName=Table_Name
 End Property
 ''查询条件
 Public Property Let Condition(ByVal Value)
  Query_Where=Value
 End Property
 Public Property Get Condition()
  Condition=Query_Where
 End Property
 ''*****************排序部分********************************************
 ''Value 语不用写上Order By 。如： [object].OrderBy="ID Desc,PostTime Asc"
 Public Property Let OrderBy(ByVal Value)
  OrderBy_SQL=Value
 End Property
 Public Property Get OrderBy()
  OrderBy=OrderBy_SQL
 End Property
 '/****************End******************************************
 
 '/****************返回当前查询结果的总页数***********************
 Public Property Get PageCount()
  PageCount=Page_Count
 End Property
 Public Property Get RecordCount()
  RecordCount=Record_Count
 End Property
 Public Property Get NextPage()
     If Cur_Page<Page_Count Then
     NextPage=Cur_Page+1
  Else
     NextPage=Page_Count
  End If
 End Property
 Public Property Get PrePage()
  If Cur_Page>1 Then
     PrePage=Cur_Page-1
  Else
     PrePage=Cur_Page
  End If
 End Property
 '/****************End******************************************

 '/****************设置一页显示的记录数***************************
 Public Property Let PageSize(ByVal Value)
  If Not IsNumeric(Value) Or Value="" Then
     Value=10
  Else
     Value=Cint(Value)
  End If
  If Value<1 Then Value=10
  Page_Size=Value
 End Property
 Public Property Get PageSize()
  PageSize=Page_Size
 End Property
 ''设置当前的页码数**************************
 Public Property Let Page(ByVal Value)
  If Not IsNumeric(Value) Or Value="" Then
     Value=1
  Else
     Value=CLng(Value)
  End If
  If Value<1 Then Value=1
  Cur_Page=Value
 End Property
 Public Property Get Page()
  Page=Cur_Page
 End Property
    '/****************End******************************************

 Private Sub Class_Initialize
     '初始化RecordSet对象
  Page_Size=10            '默认一页为10条数据
  CurPage=1                   '默认当前为第一页
  Record_Count=0
  Page_Count=0
 End Sub

 Private Sub Class_Terminate
  Call CloseRecordSet
 End Sub
   
 '/***关闭数据库的连接*******
 Private Sub CloseRecordSet
 On Error Resume Next
  If IsObject(Rs) Then
     Rs.Close
     Set Rs=Nothing
  End If
 On Error Goto 0
 End Sub

    '/**********执行查询返回对应页码的数据***********************************************
 Public Function ExecuteBy(ByVal oTableName,ByVal oFields,ByVal oCondition,ByVal oOrderBy)
  Table_Name=oTableName
  List_Fields=oFields
  Query_Where=oCondtion
  OrderBy_SQL=oOrderBy
  Set ExecuteBy=Execute()
 End Function
 '查询并返回当前CurPage的页码记录
 Public Function Execute()
 Call CloseRecordSet
  On Error Resume Next
  Dim TSQL,TopMod,sWhere
  If Not IsObject(Connection) Or Table_Name="" Or OrderBy_SQL="" Then
     Set Execute=Nothing
     Record_Count=0
     Page_Count=0
     Exit Function
  End If
  If Trim(Query_Where)<>"" Then
     sWhere="Where "&Query_Where
  Else
     sWhere=""
  End If
  TSQL="Select Count(*) From ["&Table_Name&"] "&sWhere
  Record_Count=Connection.Execute(TSQL)(0)    '获取记录总数
  If Err Then
     Err.Clear
     Set Execute=Nothing
     Record_Count=0
     Page_Count=0
     Exit Function
  End If
  If Record_Count<1 Then
     Set Execute=Nothing
     Record_Count=0
     Page_Count=0
     Exit Function
  End If
  '取得页的总数
  If Record_Count Mod Page_Size <>0 Then
     TopMod=Record_Count Mod Page_Size
     Page_Count=Fix(Record_Count/Page_Size)+1
     If Cur_Page<Page_Count Then
     TopMod=Page_Size
     End If
  Else
     TopMod=Page_Size
     Page_Count=Fix(Record_Count/Page_Size)
  End If
  If Cur_Page>Page_Count Then Cur_Page=Page_Count
  If Cur_Page<1 Then Cur_Page=1
  If Trim(List_Fields)="" Then List_Fields="*"
  TSQL="Select * From (Select Top "&TopMod&" * From (Select Top "&(Cur_Page*Page_Size)&" "&List_Fields&" From ["&Table_Name&"] "&sWhere&" Order By "&OrderBy_SQL&") Order By "&TransformOrder(OrderBy_SQL)&")Order By "&OrderBy_SQL
  Set Rs=Connection.Execute(TSQL)
  If Err Then
     Err.Clear
     Set Execute=Nothing
     Record_Count=0
     Page_Count=0
     Exit Function
  End If
  Set Execute=Rs
 End Function

    '转换OrderBy的顺序 ASC->DESC   DESC->ASC
 Private Function TransformOrder(ByVal Value)
  If Value="" Then
     TransformOrder=""
           Exit Function
  End If
  Dim OrderArray,i,Result,ByString,Fields,InPos
  OrderArray=Split(Value,",")   '分解每个字段值
  For i=0 To Ubound(OrderArray)
      If OrderArray(i)<>"" Then
      InPos=InStrRev(Trim(OrderArray(i))," ")  '找出排序的顺序
      If InPos<1 Then  '如果找不到则是ASC排序
         ByString="ASC"
      Fields=OrderArray(i)+" "
      Else
         ByString=Trim(Mid(OrderArray(i),InPos+1))
      Fields=Left(OrderArray(i),InPos)
      If ByString<>"" Then
         ByString=UCase(ByString)
      Else
         ByString="ASC"
      End If
      End If
      ''转换排序
      If ByString="ASC" Then
         ByString="DESC"
      Else
         ByString="ASC"
      End If
      Result=Result+Fields+ByString+","
   End If
  Next
  If Result<>"" Then Result=Left(Result,Len(Result)-1)
  TransformOrder=Result
 End Function
End Class
%>

示例代码:
Sub Show_List
 Dim Page,PageRs
 Page=Request("Page")
 Dim MyPage
 Set MyPage=New PageClass
 MyPage.Conn=Conn
 MyPage.PageSize=20
 MyPage.Page=Page
 'MyPage.TableName="Movie"
 'MyPage.Fields="*"
 'MyPage.OrderBy="ID Asc"
 'Set PageRs=MyPage.Execute
 Set PageRs=MyPage.ExecuteBy("Movie","*","","ID Asc")
 If PageRs Is Nothing Then Exit Sub
 Do Until PageRs.Eof
    Response.Write " <tr bgcolor=""#FDFDFD"" style=""cursor:hand"" onmouseover=""this.style.background='#F3F3F3'"" onmouseout=""this.style.background='#FDFDFD'"">"
    Response.Write "    <td height=""20""><div align=""center"">"&PageRs("ID")&"</div></td>"
    Response.Write "    <td>"&PageRs("Name")&"</td>"
    Response.Write "    <td><a href="""&PageRs("URL")&"""><font color='#000000'>"&PageRs("URL")&"</font></a></td>"
    Response.Write "    <td>"&PageRs("Type")&"</td>"
    Response.Write "  </tr>"
    PageRs.MoveNext
    Loop
 PageRs.Close
 PageCount=MyPage.PageCount
 Page=MyPage.Page            '取得当前正确的页码数
 NextPage=MyPage.NextPage
 PrePage=MyPage.PrePage
 Set PageRs=Nothing
 Set MyPage=Nothing
End Sub

本文地址：http://com.8s8s.com/it/it7993.htm