软件开发培训班 >> 编程开发 >> ASP


asp的通用数据分页类

　　本文标签：asp的通用数据分页类

 (原创)<!--#include file="Conn.asp" -->
通用数据分页类
    通用分页类,以后写分页显示数据时就轻松多啦.直接调用此类,然后再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="table1"
    MyPage.Fields="*"
    MyPage.OrderBy="ID Asc"
    Set PageRs=MyPage.Execute
    Set PageRs=MyPage.ExecuteBy("table1","*","","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("aaaa")&"</td>"
        Response.Write "    <td><a href="""&PageRs("bbbb")&"""><font color=#000000>"&PageRs("bbbb")&"</font></a></td>"
        Response.Write "    <td>"&PageRs("cccc")&"</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
Show_List
%>