|
|
|
|
你现在的位置：您现在的位置是: 中国ASP>>ASP教程>>asp类 |
|
| ASP类库之查询翻页优化 |
|
<SCRIPT language=VBSCRIPT RUNAT=SERVER>
''//---- COCOON_自定义类_翻页优化代码 ----//''
Class cc_db_Pager
''--------------------------------
'' COCOON Db_Pager 类 (Ver: 1.02)
'' 作者: Sunrise_Chen (sunrise_chen@msn.com)
'' 请保留此信息，谢谢。
''
'' 2003/06 修正一个错误，描述：如果ID不唯一，将产生多条重复记录的错误。
'' 2003.5
''--------------------------------
''//-------------------- 定义变量 --------------------//''
Private sTableName ''//表名
Private sSqlString ''//自定义Sql语句
Private aCondition() ''//查询条件(数组)
Private sCondition ''//查询条件(字符串)
Private iPage ''//当前页码
Private iPageSize ''//每页记录数
Private iPageCount ''//总页数
Private iRecCount ''//当前查询条件下的记录数
Private iTotalRecCount ''//总记录数
Private sFields ''//输出的字段名
Private sOrderBy ''//排序字符串
Private sSql ''//当前的查询语句
Private sPkey ''//主键
Private oConn ''//连接对象
Private iDefPageSize ''//默认每页显示的记录数
Private sProjectName ''/项目名
Private sVersion ''/版本号
Private bShowError ''//是否显示错误信息
''//-------------------- 事件、方法 --------------------//''
''//类初始化事件
Private Sub Class_Initialize()
ReDim aCondition(-1)
iPage = 1
iRecCount = Null
iTotalRecCount = Null
iPageCount = Null
bShowError = True
iPageSize = 10
sFields = "*"
sPkey = "ID"
sCondition = ""
sOrderBy = ""
sSqlString = ""
sProjectName = "COCOON 类系列 数据库翻页优化"
sVersion = "1.02"
End Sub
''//类结束事件
Private Sub Class_Terminate()
Set oConn = Nothing
End Sub
''//错误处理
Public Sub doError(s)
Dim sTmp
sTmp = CLng(Rnd() * 100)
Response.write( "<DIV STYLE=''WIDTH:760;font-size:9pt;cursor:hand''>" )
Response.write( "<LABEL onCLICK=''ERRORDIV"&sTmp&".style.display=(ERRORDIV"&sTmp&".style.display==""""?""none"":"""")''>" )
Response.write( "<SPAN STYLE=''BACKGROUND-COLOR:#CCCC00;COLOR:WHITE;''>〖 CC_db_Pager 提示信息〗</SPAN><BR></LABEL>" )
Response.write( "<DIV ID=''ERRORDIV"&sTmp&"'' STYLE=''DISPLAY:NONE;WIDTH:100%;" )
Response.write( "BORDER: 1PX SOLID #CCCC00;PADDING:5;overflow:hidden;text-overflow:ellipsis;''><NOBR>" )
Response.write( "<SPAN STYLE=''COLOR:RED''>Description</SPAN>: " & s & "<BR>" )
Response.write( "<SPAN STYLE=''COLOR:RED''>Provider</SPAN>: " & sProjectName )
Response.write( " <SPAN STYLE=''COLOR:RED''>Version</SPAN>: " & sVersion & "<BR>" )
Response.write( "</NOBR></DIV></DIV><BR>" )
End Sub
''//产生分页的SQL语句
Public Function getSql()
Dim iStart, iEnd
Call makeCondition()
iStart = ( iPage - 1 ) * iPageSize
iEnd = iStart + iPageSize
getSql = " Select DISTINCT " & sFields & " FROM ["&sTableName&"] " _
& " Where ["&sPKey&"] NOT IN ( " _
& " Select TOP "&iStart&" ["&sPKey&"] FROM ["&sTableName&"] " & sCondition & " " & sOrderBy & " " _
& " )" _
& " AND ["&sPKey&"] IN ( " _
& " Select TOP "&iEnd&" ["&sPKey&"] FROM ["&sTableName&"] " & sCondition & " " & sOrderBy & " " _
& " )" _
& " " & sOrderBy & " "
End Function
''//产生条件字符串
Private Sub makeCondition()
If UBound(aCondition)>=0 Then
sCondition = " Where " & Join(aCondition, " AND ")
End If
End Sub
''//计算总记录数（带条件）
Private Sub CaculateRecCount()
On Error Resume Next
Dim oRs
Call makeCondition()
Set oRs = oConn.Execute( "Select COUNT(["&sPKey&"]) FROM [" & sTableName & "]" & sCondition )
If Err Then
doError Err.Description
Response.End()
End If
iRecCount = oRs.Fields.Item(0).value
Set oRs = Nothing
End Sub
''//计算总记录数（不带条件）
Private Sub CaculateTotalRecCount()
On Error Resume Next
Dim oRs
Set oRs = oConn.Execute( "Select COUNT(["&sPKey&"]) FROM [" & sTableName & "]" )
If Err Then
doError Err.Description
Response.End()
End If
iTotalRecCount = oRs.Fields.Item(0).value
Set oRs = Nothing
End Sub
''//计算页数
Private Sub CaculatePageCount()
If isNull(iRecCount) Then CaculateRecCount()
If iRecCount = 0 Then iPageCount = 0 : Exit Sub
iPageCount = Abs( Int( 0 - (iRecCount / iPageSize) ) )
End Sub
''//增加条件
Public Sub AddCondition(s)
If Len(s)<0 Then Exit Sub
ReDim Preserve aCondition(UBound(aCondition)+1)
aCondition(UBound(aCondition)) = s
End Sub
''//版本信息
Public Function Information()
doError "Coding by <A HREF=''MAILTO:sunrise_chen@msn.com''>Sunrise_Chen</A> @ <A HREF=''http://www.ccopus.com''>http://www.ccopus.com<;/A> ."
End Function
''//-------------------- 输入属性 --------------------//''
''//定义连接对象
Public Property Set ActiveConnection(o)
Set oConn = o
End Property
''//定义查询表名
Public Property Let TableName(s)
sTableName = s
End Property
''//定义需要输出的字段名
Public Property Let Fields(s)
sFields = s
End Property
''//定义主键
Public Property Let Pkey(s)
sPkey = s
End Property
''//定义排序规则
Public Property Let OrderBy(s)
sOrderBy = " ORDER BY " & s & " "
End Property
''//定义每页的记录条数
Public Property Let PageSize(s)
iPageSize = s
If Not isNumeric(iPageSize) Then iPageSize = iDefaultPageSize
If CLng(iPageSize)<1 Then iPageSize = iDefaultPageSize
End Property
''//定义当前页码
Public Property Let Page(s)
iPage = s
If Not isNumeric(iPage) Then iPage = 1
If CLng(iPage)<1 Then iPage = 1
Call CaculatePageCount()
If CLng(iPage)>CLng(iPageCount) And iPageCount>0 Then iPage = iPageCount
End Property
''//自定义查询语句
Public Property Let Sql(s)
sSqlString = s
End Property
''//-------------------- 输出属性 --------------------//''
''//取得当前条件下的记录数
Public Property Get RecordCount
If isNull(iRecCount) Then CaculateRecCount()
RecordCount = iRecCount
End Property
''//取得当前页码
Public Property Get Page
Page = iPage
End Property
''//取得当前页码
Public Property Get AbsolutePage
AbsolutePage = iPage
End Property
''//取得当前查询的条件
Public Property Get Condition
If Len(sCondition)<1 Then makeCondition()
Condition = sCondition
End Property
''//取得总的记录数
Public Property Get TotalRecordCount
If isNull(iTotalRecCount) Then CaculateTotalRecCount()
TotalRecordCount = iTotalRecCount
End Property
''//取得总页数
Public Property Get PageCount
If isNull(iPageCount) Then CaculatePageCount()
PageCount = iPageCount
End Property
''//得到分页后的记录集
Public Property Get Recordset
On Error Resume Next
sSql = getSql()
Set Recordset = oConn.Execute( sSql )
If Err Then
If bShowError Then doError Err.Description
If Len(sSqlString)>0 Then
Set Recordset = oConn.Execute( sSqlString )
If Err Then
doError Err.Description
Response.End()
End If
Else
doError Err.Description
End If
End If
Err.Clear()
End Property
''//版本信息
Public Property Get Version
Version = sVersion
End Property
End Class
</SCRIPT>
>> 使用说明 Coding by Sunrise_Chen.
Class cc_db_Pager
Public Property Let ConnectionString ''//设置连接字符串
Public Property Set ActiveConnection(o) ''//设置连接对象（与ConnectionString属性二者取一）
Public Property Let TableName(s) ''//设置查询表名或视图名
Public Property Let Fields(s) ''//设置输出字段名（可省略，默认为"*",即所有字段）
Public Property Let Pkey(s) ''//设置主键（可省略，默认为"ID"）
Public Property Let OrderBy(s) ''//排序规则（可省略，省略则按默认规则排序）
Public Property Let Page(s) ''//设置当前页码
Public Property Let PageSize(s) ''//设置每页记录数
Public Property Let Sql(s) ''//容错的SQL语句（此功能有待进完善，暂保留）
Puvlic Property Get Sql ''//返回已翻页的SQL语句
Public Property Get RecordCount ''//返回当前记录数
Public Property Get TotalRecordCoun ''//返回总记录数
Public Property Get Condition ''//保留
Public Property Get PageCount ''//返回总页数
Public Property Get Recordset ''//返回已分页的记录集(核心)
Public Property Get Version ''//返回本段代码的版本信息
Public Property Get Page ''//返回当前页码
Public Property Get AbsolutePage ''//返回当前页(同上)
Public Function Information()
Private Function getSql()
Private Sub makeCondition()
Private Sub CaculateRecCount()
Private Sub CaculateTotalRecCount()
Private Sub CaculatePageCount()
Private Sub AddCondition(s)
Private Sub Class_Initialize()
Private Sub Class_Terminate()
End Class
>> 使用方法演示 Coding by Sunrise_Chen.
<SCRIPT language="javascript">
function doPage(n){
location.href=location.pathname+''?page=''+n+''&其他条件...'';
}
</SCRIPT>
<%
Dim oDbPager
iPageSize = DefaultPageSize
Set oDbPager = New cc_db_Pager
Set oDbPager.ActiveConnection = oConn
oDbPager.TableName = "t_Company"
oDbPager.Fields = "ID,Company,WebSite,MainProduct,UserLevel, Status, Phone, Fax"
oDbPager.Pkey = "ID"
oDbPager.OrderBy = "UserLevel DESC, Status DESC, CharIndex(''中国'',Country) DESC, ID DESC"
oDbPager.PageSize = iPageSize
oDbPager.AddCondition "Status>0"
If Not isEmpty(Request.QueryString("Auth1")) Then
oDbPager.AddCondition "Auth1>0"
End If
oDbPager.Page = Request.QueryString("page")
iCurrPage = oDbPager.Page
iRecCount = oDbPager.RecordCount
iPageCount = oDbPager.PageCount
sPageInfo = "当前页为 "&iCurrPage&" 共计 "&iPageCount&" 个页面 共有 "&iRecCount&" 条信息"
sPager = "" & vbCrLf _
& "[<A "&IIf(CInt(iCurrPage)<=1,"disabled href=''javascript:void(0);''","href=''javascript:doPage(1);''")&">首页 </A>]" & vbCrLf _
& "[<A "&IIf(CInt(iCurrPage)<=1,"disabled href=''javascript:void(0);''","href=''javascript:doPage("&(iCurrPage-1)& ");''")&">前页</A>]" & vbCrLf _
& "[<A "&IIf(CInt(iCurrPage)>=CInt(iPageCount),"disabled href=''javascript:void(0);''","href=''javascript:doPage("&(iCurrPage+1)& ");''")&">后页</A>]" & vbCrLf _
& "[<A "&IIf(CInt(iCurrPage)>=CInt(iPageCount),"disabled href=''javascript:void(0);''","href=''javascript:doPage("&iPageCount& ");''")&">末页</A>]"
%>
...
<%
Set oRs = oDbPager.Recordset
Do While Not oRs.Eof
''// ...
oRs.MoveNext()
Loop
%>
...
<%=sPageInfo%>
...
<%=sPager%>
|
|
|
