[XML] asp+sql分页xml绑定

sql 表结构
复制代码
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Products]
GO
Create TABLE [dbo].[Products] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ProductName] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[SupplierID] [int] NULL ,
[CategoryID] [int] NULL ,
[QuantityPerUnit] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[UnitPrice] [money] NULL ,
[UnitsInStock] [smallint] NULL ,
[UnitsOnOrder] [smallint] NULL ,
[ReorderLevel] [smallint] NULL ,
[Discontinued] [bit] NOT NULL
) ON [PRIMARY]
GO
代码如下：
复制代码
<% '可能通用性不怎么好 '注意查询出来的信息与HTML绑定的列，是一致的 dim T1,T2 T1=timer() const Default_psize=18 Class Cls_ForXml_Page Private mmax 'ID最大值 Private size '总元素 Private curp '当前页 Private coup '总页数 Private psize '页长 Private list '列表 Private temp '临时 '析构 Private Sub Class_Terminate() End Sub '构造 Private Sub Class_Initialize() End Sub '处理数字 Private Function nfilter(ByVal Param) Param=trim(Param) If IsNumeric(Param) Then nfilter=Fix(Param) Else nfilter=0 End If End Function Public Function getxml(ByVal con,ByVal sql,ByVal name) list=Trim(Left(sql,InStr(sql," from ")-1)) list=Trim(Right(list,len(list)-len("select "))) temp=split(list,",") size=con.execute("select count(ID) from (" & sql & ") as b").fields(0) psize=nfilter(request.QueryString("psize")) If psize=0 Then psize=Default_psize If Not size Mod psize=0 Then coup=sizepsize+1 Else coup=sizepsize curp=nfilter(request.QueryString("curp")) If curp<=0 Then curp=1 If curp>coup Then curp=coup
sql =Right(sql,Len(sql)-InStr(sql," from ")+1)
Dim rs
set rs=con.execute("select Max(ID) from (Select Top "&(curp-1)*psize&" ID "&sql&" order by ID) as a")
If not rs.eof Then mmax=rs.fields(0)
If mmax="" or isNull(mmax) Then mmax=0
set rs=nothing
If InStr(sql," where ")<>0 Then
sql=replace(sql," where "," where ID>"&mmax&" and ",1)
End If
sql="Select Top "&psize&" "&list&" "&sql
sql=sql&" for xml auto,elements"
Dim ms,cm,msxml
set ms=server.CreateObject("ADODB.Stream")
set cm=server.CreateObject("ADODB.Command")
ms.open
set cm.ActiveConnection=con
cm.properties("output stream").value=ms
cm.commandtext=sql
cm.execute ,,1024
ms.position = 0
msxml = ""&ms.readtext&""
ms.close
set ms = nothing
getxml=msxml
End Function
'过滤不需要的GET串
Private Function filterGET(ByVal filters)
Dim url,i,obj,newGet,flag
set url=request.QueryString()
filters=Split(filters,",")
newGet=""
For Each obj In url
flag=False
For i=0 To UBound(filters)
If Trim(LCase(CStr(obj)))=Trim(LCase(CStr(filters(i)))) Then
flag=True
Exit For
End If
Next
If Not flag Then newGet = newGet & obj & "=" & url(obj) & "&"
Next
If Not newGet="" Then newGet=Trim(Left(newGet,Len(newGet)-1))
filterGET=newGet
End Function
'显示上下页（当前页,总页数,前后加起来的页数,除去cp参数的GET串）
public function page(ByVal showPageC)
dim i,l,starF,endF,reStr,getStr,queryStr
queryStr=filterGET("curp,mmax")
'*****************************************************
if queryStr="" then
getStr="curp=1"
else
getStr=queryStr & "&curp=1"
end if
if curp>1 then
reStr=reStr & "首页 "
else
reStr=reStr & "首页 "
end if
'*****************************************************
if queryStr="" then
getStr="curp=" & (curp-1)
else
getStr=queryStr & "&curp=" & (curp-1)
end if
if curp>1 then
reStr=reStr & "上页 "
else
reStr=reStr & "上页 "
end if
'*****************************************************
if queryStr="" then
getStr="curp=" & (curp+1)
else
getStr=queryStr & "&curp=" & (curp+1)
end if
if curp下页 "
else
reStr=reStr & "下页 "
end if
'*****************************************************
if queryStr="" then
getStr="curp=" & coup
else
getStr=queryStr & "&curp=" & coup
end if
if curp末页 "
else
reStr=reStr & "末页 "
end if
'*****************************************************
if queryStr="" then
getStr="curp="
else
getStr=queryStr & "&curp="
end if
reStr=reStr & "跳转：" & coup & ")this.value=" & coup &";"" onblur=""this.onkeyup();"" onchange=""this.onkeyup();"" onkeydown=""if(event.keyCode==13)location.href='?" & getStr & "'+this.value;""> "
reStr=reStr & "　" & curp & "/" & coup & "　" & psize & "/页 合计:" & size & " "
getStr=filterGET("psize")
if getStr="" then
getStr="psize="
else
getStr=getStr & "&psize="
end if
reStr=reStr & "页长：" & "

"
'*****************************************************
const DOT=".."
if showPageC=0 then showPageC = 5
l=showPageC2
if curp-l<=1 then starF=1 else starF=curp-l reStr=reStr & DOT end if endF=starF+showPageC reStr=reStr & ""
if endF

<% set forxml=nothing set con=nothing %>

<%t2=timer()%>