[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=size\psize+1 Else coup=size\psize
           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 = "<xml id="""&name&""" name="""&name&"""><root>"&ms.readtext&"</root></xml>"
           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 & "<a href=""?" & getStr & """ target=_self class=page>首页</a> "
           else
                 reStr=reStr & "<font color=#CCCCCC>首页</font> "
           end if
           '*****************************************************
           if queryStr="" then
                 getStr="curp=" & (curp-1)
           else
                 getStr=queryStr & "&curp=" & (curp-1)
           end if
           if curp>1 then
                 reStr=reStr & "<a href=""?" & getStr & """ target=_self class=page>上页</a> "
           else
                 reStr=reStr & "<font color=#CCCCCC>上页</font> "
           end if
           '*****************************************************
           if queryStr="" then
                 getStr="curp=" & (curp+1)
           else
                 getStr=queryStr & "&curp=" & (curp+1)
           end if
           if curp<coup then
                 reStr=reStr & "<a href=""?" & getStr & """ target=_self class=page>下页</a> "
           else
                 reStr=reStr & "<font color=#CCCCCC>下页</font> "
           end if
           '*****************************************************
           if queryStr="" then
                 getStr="curp=" & coup
           else
                 getStr=queryStr & "&curp=" & coup
           end if
           if curp<coup then
                 reStr=reStr & "<a href=""?" & getStr & """ target=_self class=page>末页</a> "
           else
                 reStr=reStr & "<font color=#CCCCCC>末页</font> "
           end if
           '*****************************************************
           if queryStr="" then
                 getStr="curp="
           else
                 getStr=queryStr & "&curp="
           end if
           reStr=reStr & "跳转：<input type=text value=" & curp & " size=4 onkeyup=""this.value=this.value.replace(/[^\d]/ig,'');if(this.value>" & coup & ")this.value=" & coup &";"" onblur=""this.onkeyup();"" onchange=""this.onkeyup();"" onkeydown=""if(event.keyCode==13)location.href='?" & getStr & "'+this.value;""> "
           reStr=reStr & "<span class=green>　" & curp & "/" & coup & "　" & psize & "/页</span> 合计:" & size & " "
           getStr=filterGET("psize")
           if getStr="" then
                 getStr="psize="
           else
                 getStr=getStr & "&psize="
           end if
           reStr=reStr & "页长：<input type=text value=" & psize & " size=4 onkeyup=""this.value=this.value.replace(/[^\d]/ig,'');"" onblur=""this.onkeyup();"" onchange=""this.onkeyup();"" onkeydown=""if(event.keyCode==13)location.href='?" & getStr & "'+this.value;"">" & "<br><br>"
           '*****************************************************
           const DOT="<span style=cursor:hand;>..</span>"
           if showPageC=0 then showPageC      =      5
           l=showPageC\2
           if curp-l<=1 then
           starF=1
           else
                 starF=curp-l
                 reStr=reStr & DOT
           end if
           endF=starF+showPageC
           reStr=reStr & "<script language=javascript>"
           reStr=reStr & "var getStr;"
           reStr=reStr & "for(var i="&starF&";i<="&endF&" && i<="&coup&";i++) {"
           reStr=reStr & "      if('"&queryStr&"'=='') getStr='curp='+i;"
           reStr=reStr & "      else getStr='"&queryStr&"'+'&curp='+i;"
           reStr=reStr & "      if(i!="&curp&") document.write('<a href=""?'+getStr+'"" target=_self class=page>['+i+']</a> ');"
           reStr=reStr & "      else document.write('<b><font color=#CC9933>['+i+']</a></b> ');"
           reStr=reStr & "}"
           reStr=reStr & "</script>"
           if endF<coup then reStr=reStr & DOT
           '*****************************************************
           page=reStr
     end function
End Class



dim con
set con=server.createobject("adodb.connection")
con.open "Provider=SQLOLEDB.1;Password=****;Persist Security Info=True;User ID=sa;Initial Catalog=Northwind;Data Source=(local)"

dim forxml
set forxml=new Cls_ForXml_Page
response.write(forxml.getXml(con,"select ID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued from Products where 1=1","xml1"))
%>
<div id="top_page"><%=forxml.page(10)%></div>
<%
set forxml=nothing
set con=nothing
%>


<table width="100%"  border="0" align="center" cellpadding="0" cellspacing="0">
  <tr>
    <td bgcolor="#CCCCCC">
     <table width="100%" datasrc="#xml1" border="0" cellspacing="1" cellpadding="3">
           <thead>
        <th width="6%" bgcolor="#FF9933"><span style="color: #FFFFFF">编号</span></th>
        <th width="29%" bgcolor="#FF9933"><span style="color: #FFFFFF">名称</span></th>
        <th width="6%" bgcolor="#FF9933"><span style="color: #FFFFFF">编号1</span></th>
        <th width="6%" bgcolor="#FF9933"><span style="color: #FFFFFF">编号2</span></th>
        <th width="23%" bgcolor="#FF9933"><span style="color: #FFFFFF">单元1</span></th>
        <th width="8%" bgcolor="#FF9933"><span style="color: #FFFFFF">价格</span></th>
        <th width="5%" bgcolor="#FF9933"><span style="color: #FFFFFF">单元2</span></th>
        <th width="5%" bgcolor="#FF9933"><span style="color: #FFFFFF">单元3</span></th>
        <th width="7%" bgcolor="#FF9933"><span style="color: #FFFFFF">记录等级</span></th>
        <th width="4%" bgcolor="#FF9933"><span style="color: #FFFFFF">未知</span></th>
           </thead>
      <tr bgcolor="#FFFFFF">
        <td><div datafld="ID" /></td>
        <td><div datafld="ProductName" /></td>
        <td><div datafld="SupplierID" /></td>
        <td><div datafld="CategoryID" /></td>
        <td><div datafld="QuantityPerUnit" /></td>
        <td><div datafld="UnitPrice" /></td>
        <td><div datafld="UnitsInStock" /></td>
        <td><div datafld="UnitsOnOrder" /></td>
        <td><div datafld="ReorderLevel" /></td>
        <td><div datafld="Discontinued" /></td>
      </tr>
    </table></td>
  </tr>
</table>
<div id="bot_page"></div>
<script>document.getElementById("bot_page").innerHTML=document.getElementById("top_page").innerHTML;</script>
<style type="text/css">
<!--
body,table,td {
     font-size:9pt;
}
#top_page,#bot_page {
     background-color: #F0F0F0;
     border: 1px dashed #FF9933;
}
-->
</style>
<%t2=timer()%>
<br>
<div align="center"><%=round((t2-t1)*1000,3)%></div>

[本日志由 hkylin 于 2010-05-02 01:19 AM 编辑]
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags:
相关日志: