ASP + MS SQL 高效分页通用方法

[forwhj]

这些天一直在研究一种通用的分页，在数据库版找到了一个通用的分页存储过程，感觉效率还是蛮可以的（一百万数据大约需要三秒多，而如果用ＡＳＰ直接写的话，不计算计录总数的时候和这个差不多，计算了总记录数就变成十七秒了），于是拿来写成一个ＡＳＰ的例子，希望对各位兄弟姐妹有所帮助。
注：以下版本只是针对MS SQL数据库。

1。建立表

CREATE TABLE [dbo].[MemberTest] (
[MemberId] [bigint] NOT NULL ,
[MemberName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

2。写入测试数据(我这里写入是两百万，可根据你的需要写入）
declare @i int
set @i=1
while @i<=1000000
begin
insert into MemberTest values(@i,'Member' + cast(@i as varchar(10)))
set @i=@i+1
end
３。存储过程
　　--利用SQL未公开的存储过程实现分页
　　CREATE procedure p_splitpage
　　@sql nvarchar(4000), --要执行的sql语句
　　@currentpage int=2, --要显示的页码
　　@pagesize int=10, --每页的大小
　　@recordcount int=0 output, --记录数
　　@pagecount int=0 output --总页数
　　as
　　set nocount on
　　declare @p1 int
　　exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
　　set @recordcount=@pagecount
　　set @pagecount=ceiling(1.0*@pagecount/@pagesize)
　　set @currentpage=(@currentpage-1)*@pagesize+1
　　--select @recordcount recordcount ,@pagecount pagecount,@currentpage currentpage
　　exec sp_cursorfetch @p1,16,@currentpage,@pagesize
　　exec sp_cursorclose @p1
　　set nocount off
　　GO
４。调用存储过程的函数
　　'....................................................
　　'得到一个记录集对像
　　'....................................................
　　Function GetData(Sql,Conn,Cur_page,P_size,R_Count,P_Count)
dim rs,Cmd
Set Cmd = Server.CreateObject("ADODB.Command")
Cmd.ActiveConnection=Conn
Cmd.CommandType=4 'adCmdStoredProc
Cmd.prepared=true '保存编译
Cmd.CommandText="p_splitpage" '存储过程 名称
'设置参数
Cmd.Parameters.Append Cmd.CreateParameter("@sql",200,1,500,Sql)
Cmd.Parameters.Append Cmd.CreateParameter("@currentpage",3,1,4,Cur_page)
Cmd.Parameters.Append Cmd.CreateParameter("@pagesize",3,1,4,P_size)
Cmd.Parameters.Append Cmd.CreateParameter("@recordcount",3,2)
Cmd.Parameters.Append Cmd.CreateParameter("@pagecount",3,2)
'set rs=cmd.execute '开始执行
set rs=server.CreateObject("adodb.recordset")
rs.CursorType =3
rs.LockType =3
rs.CursorLocation =3
set rs.Source =cmd
rs.open
R_Count=Cmd("@recordcount")
P_Count=Cmd("@pagecount")
set rs=rs.nextrecordset
set GetData2=rs
　　End Function
５。测试询程序
　　time1=timer
　　sql="select * from membertest"
　　response.write "<br><br>用存储过程实现<br>"
　　set rs=GetData(sql,conn,cur_page,p_size,R_Count,P_Count)
　　do while not rs.eof
response.Write rs("memberid") & "----" & rs("membername") & " "
rs.movenext
　　loop
　　response.Write( " <br>记录数" &R_Count & "总页数:" & P_Count)
　　response.write "<br>---------用时" & (timer-time1)*1000 & "毫秒-------------"

[白夜花寒]

我有个自己生成存储过程的东西....

<script language='vbscript'>
dim field_info,theexceptfield,tablename
function createsqlstr
dim str : str=""
str=str & "@num int" & "<br />"
str=str & "@page int" & "<br />"
for each thefieldname in field_info
str=str & "@" & field_info(thefieldname) & "<br />"
next
str=str & "@" & theexceptfield & "<br />"

str=str & "declare @thepage int" & "<br />"

str=str & "set @thepage=(@page-1) * @num" & "<br />"



str=str & "set @sql='select top '+cast(@num as nvarchar(30))+' * from " & tablename
i=1
for each thefieldname in field_info
if i=1 then str=str & " where "
str=str & field_info(thefieldname) & "='+cast(@" & field_info(thefieldname) & " as nvarchar(30))+' and "
i=i+1
next
str=str & theexceptfield & " not in(select top '+cast(@thepage as nvarchar(30))+'" & " " & theexceptfield
str=str & " from " & tablename & " where "
i=1
for each thefieldname in field_info
if i<>1 then str=str & " and "
str=str & field_info(thefieldname) & "='+cast(@" & field_info(thefieldname) & " as nvarchar(30))+'"
i=i+1
next
str=str & "order by " & theexceptfield &") order by " & theexceptfield & "'"
createsqlstr=str
end function


function setfieldnum
set thefieldname=document.getElementById("fieldname")
set thesetnum=document.getElementById("setnum")
thenum=thesetnum.value
if isnumeric(thenum) then
thenum=Cint(thenum)

str="请输入您所要查询的字段名：" & "<br />"
for i=0 to thenum-1
str=str & "字段" & i+1 & "：<input type='text' name='fieldnames' size='30' />" & "<br />"
next
str=str & "请输入您所用的标识字段名：" & "<input type='text' id='exceptfield' />" & "<br />"
str=str & "请输入表明：" & "<input type='text' id='tablenamed' />" & "<br />"
str=str & "<a href='vbscript:saveinfo()'>显示sql</a>"
thefieldname.innerHTML=str
end if
end function

function saveinfo()
set theshowsql=document.getElementById("showsql")
set thefieldnames=document.getElementsByName("fieldnames")
set the_exceptfield=document.getElementById("exceptfield")
set field_info=createobject("scripting.dictionary")
set thetablename=document.getElementById("tablenamed")
theexceptfield=the_exceptfield.value
tablename=thetablename.value
for i=0 to thefieldnames.length-1
thevalue=thefieldnames(i).value
field_info.add thevalue,thevalue
next
str=createsqlstr
theshowsql.innerhtml=str
end function
</script>
<html>
<head>
<base target="_self">
</head>
<body>
设定查询字段个数：<input type='text' id='setnum' size='2' />----<a href='vbscript:setfieldnum()'>确定</a>
<div id='fieldname'>
</div>
<div id='showsql'>
</div>
</body>
</html>

[i三千]

up

[man2004]

都用了，还可以，有没有更好的呢？？