(原創)<!--#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
%>
