<!-- #include?file="conn.asp" -->
<!-- #include?file="Page.asp" -->
<%
? Set ?My? = ? New ?Page
? With ?My
?.SetConnstr = Connstr? ' 數據庫鏈接字符串
?.SetStrTable = " users " ? ' 表名
?.SetStrText = " id,username,type,names " ? ' 查詢的字段
?.SetStrIndex = " id " ? ' 主鍵
?rsArray = .GetRs()? ' 返回數據，類型為數組
? End ? With
%>
< table >
<%
if ? IsArray (rsArray)? then
? For ?i? = ? 0 ? To ? UBound (rsArray,? 2 )
%>
? < tr >
? < td > <% = ?rsArray( 0 ,?i)? %> </ td >
? < td > <% = ?rsArray( 1 ,?i)? %> </ td >
? < td > <% = ?rsArray( 2 ,?i)? %> </ td >
? </ tr >
<%
? next
end ? if
%>
? < tr >
? < td? colspan ="4" > 共 <% = ?My.GetTotalNum? %> 條?每頁 <% = ?My.GetiPageSize? %> 條?共 <% = ?My.GetTotalPage? %> 頁?頁碼: <% = ?My.GetFenYeJmp( false )? %> </ td >
? </ tr >

</ table >

'*******************************************************
'Page?分頁類
'特點：采用?not?in?分頁輸出方法,需配合?sp_Page?存儲過程使用，速度比較快
'輸入參數?connstr:數據庫鏈接字符串?StrTable:要查詢的表?StrText:要查詢的字段?StrIndex:索引字段
'可選參數:(iPageSize:每頁記錄數量?StrWhere:條件語句?StrOrder:排序字段?StrSc:排序方法?ActionStr:From提交的查詢參數)
'輸出參數?rsArray:返回的記錄集數組?GetTotalNum:返回的總記錄數?GetiPage:當前頁碼?GetTotalPage:總頁數?GetiPageSize:每頁顯示條數
'輸出方法?GetFenYe():分頁?GetFenYeJmp(inForm):帶跳轉的分頁
'作者：六月雨?QQ:44569457?整理時間:2008年9月27日
'*******************************************************
Class?Page
?Private?connstr?'數據庫鏈接字符串
?
?Private?iPage?'當前頁碼
?Private?iPageSize?'每頁記錄數量
?Private?StrTable?'要查詢的表
?Private?StrText?'要查詢的字段
?Private?StrWhere?'條件語句
?Private?StrIndex?'索引字段
?Private?StrOrder?'排序字段
?Private?StrSc?'排序方法
?
?Private?ActionStr?'翻頁鏈接字符
?Private?Rs_dbs?'記錄集名稱
?Private?cmd?'cmd對象名稱
?Private?rsArray?'返回的記錄集數組
?Private?TotalNum?'返回的總記錄數
?
?Private?Sub?Class_Initialize()?'初始化類
?iPageSize=10
?iPage=trim(Request("iPage"))
?ActionStr=""
?StrWhere=""
?StrOrder=""
?StrSc=""
?If?(not?IsNumeric(iPage))?Then
?iPage=1
?Else
?If?iPage < 1? then?iPage =1
? If?iPage > 5000000?then?iPage=1
?End?If
?End?Sub
?
?Private?Sub?Class_Terminate()?'釋放類
?set?Rs_dbs=nothing
?End?Sub
?
?Public?Property?Let?SetConnstr(svalue)?'取得：數據庫鏈接字符串
?connstr=Lcase(svalue)
?End?Property
?
?Public?Property?Let?SetiPageSize(svalue)?'取得：每頁記錄數量
?iPageSize=clng(svalue)
?End?Property
?
?Public?Property?Let?SetStrTable(svalue)?'取得：SQL表名
?StrTable=Lcase(svalue)
?End?Property
?
?Public?Property?Let?SetStrText(svalue)?'取得：SQL查詢的字段
?StrText=Lcase(svalue)
?End?Property
?
?Public?Property?Let?SetStrWhere(svalue)?'取得：SQL條件字段
?StrWhere=Lcase(svalue)
?End?Property
?
?Public?Property?Let?SetStrIndex(svalue)?'取得：索引字段
?StrIndex=Lcase(svalue)
?End?Property
?
?Public?Property?Let?SetStrOrder(svalue)?'取得：排序字段
?StrOrder=Lcase(svalue)
?End?Property
?
?Public?Property?Let?SetStrSc(svalue)?'取得：排序方法
?StrSc=Lcase(svalue)
?End?Property
?
?Public?Property?Let?SetActionStr(svalue)?'取得：翻頁鏈接字符
?ActionStr=Lcase(svalue)
?End?Property
?
?Private?Function?MadeOrderBy()?'方法：組合Order?By?語句
?dim?TempStrOrder,TempStrSc,t
?if?StrOrder <> ""?and?StrSc <> ""?then
?TempStrOrder=split(StrOrder,",")
?TempStrSc=split(StrSc,",")
?if?ubound(TempStrOrder)=ubound(TempStrSc)?then
?for?t=0?to?ubound(TempStrOrder)
?if?t=0?then
?MadeOrderBy=TempStrOrder(t)&"?" &TempStrSc (t)
?else
?MadeOrderBy=MadeOrderBy&"," &TempStrOrder (t)&"?" &TempStrSc (t)
?end?if
?next
?end?if
?end?if
?End?Function
?
?Private?Sub?OpenRs()?'方法：獲得記錄集
?Set?Rs_dbs=Server.CreateObject("ADODB.RECORDSET")
?Set?cmd?=?Server.CreateObject("ADODB.Command")
?with?cmd
?.ActiveConnection?=?connstr?'數據庫連接字串
?.CommandText?=?"sp_Page"?'指定存儲過程名
?.CommandType?=?4?'表明這是一個存儲過程
?.Prepared?=?true?'要求將SQL命令先行編譯
?.Parameters.append?.CreateParameter("@iPage",3,1,4,iPage)?'指定頁數
?.Parameters.append?.CreateParameter("@iPageSize",3,1,4,iPageSize)?'每頁記錄數
?.Parameters.append?.CreateParameter("@StrTable",200,1,200,StrTable)?'分頁時要查詢的表名
?.Parameters.append?.CreateParameter("@StrText",200,1,1000,StrText)?'字段
?.Parameters.append?.CreateParameter("@StrWhere",200,1,1000,StrWhere)?'查詢條件where?中的條件語句
?.Parameters.append?.CreateParameter("@StrIndex",200,1,30,StrIndex)?'索引值
?.Parameters.append?.CreateParameter("@StrOrder",200,1,100,MadeOrderBy())?'排序的字段
?.Parameters.Append?.CreateParameter("@StrTotals",3,2,10)?'總頁數output
?Set?Rs_dbs?=?.Execute
?end?with
?End?Sub
?
?Public?Property?Get?GetRs()?'輸出：記錄集(數組方式)
?call?OpenRs()
?If?not?(Rs_dbs.eof?and?Rs_dbs.bof?)?then
?GetRs?=?Rs_dbs.GetRows()
?End?If
?Rs_dbs.close
?TotalNum?=?cmd(7)
?set?cmd?=?nothing
?End?Property
?
?Public?Property?Get?GetTotalNum?'輸出：總記錄數
?GetTotalNum=TotalNum
?End?Property
?
?Public?Property?Get?GetTotalPage?'輸出：總頁數
?If?(TotalNum?mod?iPageSize) <> 0?Then
?GetTotalPage=(TotalNum\iPageSize)+1
?Else
?GetTotalPage=(TotalNum\iPageSize)
?End?If
?End?Property
?
?Public?Property?Get?GetiPageSize?'輸出：每頁顯示條數
?GetiPageSize=iPageSize
?End?Property
?
?Public?Property?Get?GetiPage?'輸出：當前頁碼
?GetiPage=iPage
?End?Property
?
?Private?Function?GetQueryUrl()?'方法：獲得當前URL
?dim?UrlFile,Query,Querys,i
?UrlFile=?Request.Servervariables("URL")
?Query=?Request.Servervariables("QUERY_STRING")
?Querys=split(Query,"&")
?For?i=0?to?ubound(Querys)
?if?trim(Querys(i)) <> ""?then
?If?trim(Querys(i))=replace(trim(Querys(i)),"iPage=","")?then
?GetQueryUrl=GetQueryUrl &Querys (i)&"&"
?End?If
?End?If
?Next
?if?ActionStr=""?then
?GetQueryUrl=UrlFile&"?" &GetQueryUrl
?else
?GetQueryUrl=UrlFile&"?" &ActionStr &"&" &GetQueryUrl
?end?if
?End?Function
?
?Public?Property?Get?GetFenYe()?'輸出：分頁
?If?(iPage>=1)?and?(clng(iPage) < =clng (GetTotalPage))?then
?dim?x,y,m,n,i,ActionUrL
?ActionUrL =GetQueryUrl()
? If?iPage ="" ?then?iPage =1
? iPage =Clng(iPage)
? If?TotalNum< > 0?then
?x=TotalNum\iPageSize
?y=TotalNum?mod?iPageSize
?
?If?y <> 0?then?x=x+1?'總頁數
?if?iPage>5?then
?If?(iPage+5) < =x? then
?n =iPage-4
? m =iPage+5
? Else
?n =iPage-4
? m =x
? End?If
?Else
?n =1
? m =10
? End?If
?
?If?x< =10? then
?If?x< > 1?then
?if?iPage <> 1?then
?Response.Write"? < a? href ="&ActionUrL&" iPage ="&(iPage-1)&" > 上一頁 </ a > ?"
?end?if
?for?i=1?to?x
?if?iPage=i?then
?Response.Write(i)
?Else
?Response.Write"? < a? href ="&ActionUrL&" iPage ="&i&" > [" &i &"] </ a > ?"
?End?If
?next
?if?iPage <> x?then
?Response.Write"? < a? href ="&ActionUrL&" iPage ="&(iPage+1)&" > 下一頁 </ a > ?"
?end?if
?End?If
?Else
?If?iPage>5?then
?Response.Write"? < a? href ="&ActionUrL&" iPage =1 > [首頁] </ a > ?"
?End?If
?If?iPage <> 1?then
?Response.Write"? < a? href ="&ActionUrL&" iPage ="&(iPage-1)&" > 上一頁 </ a > ?"
?end?if
?for?i=n?to?m
?if?iPage>x?then?Exit?For
?if?iPage=i?then
?Response.Write(i)
?Else
?Response.Write"? < a? href ="&ActionUrL&" iPage ="&i&" > [" &i &"] </ a > ?"
?End?If
?next
?if?iPage <> x?then
?Response.Write"? < a? href ="&ActionUrL&" iPage ="&(iPage+1)&" > 下一頁 </ a > ?"
?End?If
?if?(iPage+5) < x? then
?Response.Write"?<a?href ="&ActionUrL&" iPage ="&x&" > [尾頁] </ a > ?"
?End?If
?End?If
?End?If
?End?If
?End?Property
?
?Public?Property?Get?GetFenYeJmp(inForm)?'輸出：帶跳轉的分頁
?if?inForm?then
?response.Write(GetFenYe()&"? < input? name ='iPage'? type ='text'? value ="&iPage&" ?id ='iPage'? size ='3' > ?")
?response.Write(" < input? type ='submit'? name ='Submit'? value ='轉' > ")
?else
?response.Write(" < form? name ='Jmp'? method ='post'? action ="&GetQueryUrl()&" > ")
?response.Write(GetFenYe()&"? < input? name ='iPage'? type ='text'? value ="&iPage&" ?id ='iPage'? size ='3' > ")
?response.Write(" < input? type ='submit'? name ='Submit'? value ='轉' > ")
?response.Write(" </ form > ")
?end?if
?End?Property
End?Class

CREATE ? PROCEDURE ? [ dbo ] . [ sp_Page ]
@iPage ? int = 1 ,? -- 當前頁碼
@iPageSize ? int = 10 , -- 每頁條數
@StrTable ? varchar ( 200 ), -- 查詢的表
@StrText ? varchar ( 1000 ), -- 查詢的字段
@StrWhere ? varchar ( 1000 ), -- 條件
@StrIndex ? varchar ( 30 ), -- 索引
@StrOrder ? varchar ( 100 ) = '' , -- 排序字段
@StrTotals ? int ?output? -- 返回總條數
? AS

-- 定義變量
declare ? @SqlCount ? nvarchar ( 2000 )
declare ? @Sql ? nvarchar ( 2000 )
declare ? @TempOrder ? nvarchar ( 1000 )

if ? @StrOrder <> ""
? begin
? set ? @TempOrder = ' ?order?by? ' + @StrOrder
? end
else
? begin
? set ? @TempOrder = ''
? end

-- 組合sql語句
if ? @iPage = 1
? begin
? set ? @Sql = ' select?top? ' + str ( @iPageSize ) + ' ? ' + @StrText + ' ?from? ' + @StrTable + ' ?where?1=1? ' + @StrWhere + @TempOrder
? end
else
? begin
? set ? @Sql = ' select?top? ' + str ( @iPageSize ) + ' ? ' + @StrText + ' ?from? ' + @StrTable + ' ?where? ' + @StrIndex + ' ?not?in?(select?top? ' + str ( @iPageSize * ( @iPage - 1 )) + ' ? ' + @StrIndex + ' ?from? ' + @StrTable + ' ?where?1=1? ' + @StrWhere + @TempOrder + ' )? ' + @StrWhere + @TempOrder
? end

set ? @SqlCount = ' select?@StrTotals=isnull(count(*),10000)?from? ' + @StrTable + ' ?where?1=1? ' + @StrWhere

-- 查詢總記錄數量
exec ?sp_executesql? @SqlCount ,N ' @StrTotals?int?output ' , @StrTotals ?output

-- 執行sql語句返回
exec ?( @Sql )
GO