存储过程改自bigeagle的论坛分页程序。请大家批判！：）
select.aspx
--------------------------------------------------------------------------------
<%@ page language="c#" %>
<%@ import namespace="system.data" %>
<%@ import namespace="system.data.sqlclient" %>
<script runat="server">
protected void page_load(object sender, eventargs e)
{
int intpageno,intpagesize,intpagecount;
intpagesize = 25;
if (request["currentpage"]==null)
{
intpageno = 1;
}
else
{
intpageno = int32.parse(request["currentpage"]);
}
sqlconnection mysqlconnection = new sqlconnection("server=(local);database=test;user id=sa;password=");
sqlcommand mysqlcommand = new sqlcommand("up_gettopiclist", mysqlconnection);
mysqlcommand.commandtype = commandtype.storedprocedure;
sqlparameter workparm;
//搜索表字段，以","号分隔
workparm = mysqlcommand.parameters.add("@a_tablelist", sqldbtype.varchar, 200);
mysqlcommand.parameters["@a_tablelist"].value = "offerid,type,offertime";
//搜索表名
workparm = mysqlcommand.parameters.add("@a_tablename", sqldbtype.varchar, 30);
mysqlcommand.parameters["@a_tablename"].value = "offer";
//搜索条件，如"select * from aa where a=1 and b=2 and c=3"则条件为"where a=1 and b=2 and c=3"
workparm = mysqlcommand.parameters.add("@a_selectwhere", sqldbtype.varchar, 500);
mysqlcommand.parameters["@a_selectwhere"].value = "where type='idl'";
//表主键字段名，必须为int类型
workparm = mysqlcommand.parameters.add("@a_selectorderid", sqldbtype.varchar, 50);
mysqlcommand.parameters["@a_selectorderid"].value = "offerid";
//排序，可以使用多字段排序但主键字段必需在最前面
workparm = mysqlcommand.parameters.add("@a_selectorder", sqldbtype.varchar, 50);
mysqlcommand.parameters["@a_selectorder"].value = "order by offerid desc";
//页号
workparm = mysqlcommand.parameters.add("@a_intpageno", sqldbtype.int);
mysqlcommand.parameters["@a_intpageno"].value = intpageno;
//每页显示数
workparm = mysqlcommand.parameters.add("@a_intpagesize", sqldbtype.int);
mysqlcommand.parameters["@a_intpagesize"].value = intpagesize;
//总记录数(存储过程输出参数)
workparm = mysqlcommand.parameters.add("@recordcount", sqldbtype.int);
workparm.direction = parameterdirection.output;
//当前页记录数(存储过程返回值)
workparm = mysqlcommand.parameters.add("rowcount", sqldbtype.int);
workparm.direction = parameterdirection.returnvalue;
mysqlconnection.open();
repeater.datasource = mysqlcommand.executereader();
repeater.databind();
mysqlconnection.close();
int32 recordcount = (int32)mysqlcommand.parameters["@recordcount"].value;
int32 rowcount = (int32)mysqlcommand.parameters["rowcount"].value;
labelrecord.text = recordcount.tostring();
labelrow.text = intpageno.tostring();
intpagecount = recordcount/intpagesize;
if ((recordcount%intpagesize)>0)
intpagecount += 1;
labelpage.text = intpagecount.tostring();
if (intpageno>1)
{
hlfistpage.navigateurl = "select.aspx?currentpage=1";
hlprevpage.navigateurl = string.concat("select.aspx?currentpage=","",intpageno-1);
}
else
{
hlfistpage.navigateurl = "";
hlprevpage.navigateurl = "";
//hlfistpage.enabled = false;
//hlprevpage.enabled = false;
}
if (intpageno<intpagecount)
{
hlnextpage.navigateurl = string.concat("select.aspx?currentpage=","",intpageno+1);
hlendpage.navigateurl = string.concat("select.aspx?currentpage=","",intpagecount);
}
else
{
hlnextpage.navigateurl = "";
hlendpage.navigateurl = "";
//hlnextpage.enabled=false;
//hlendpage.enabled=false;
}
}
</script>
<html>
<meta http-equiv="content-type" content="text/html; charset=gb2312">
<head>
<link href="/style.css" rel="stylesheet" />
<style type="text/css">
.high { font-family: "宋体"; font-size: 9pt; line-height: 140%}
.mid { font-size: 9pt; line-height: 12pt}
.small { font-size: 9pt; line-height: normal}
.tp10_5 {
font-size: 14px;
line-height: 140%;
}
</style>
<style type="text/css">a:link {
color: #cc6666
}
</style>
</head>
<body>
<form runat="server">
<span class="high"> 第<font color="#cc0000"><asp:label id="labelrow" runat="server"/></font>页 | 共有<asp:label id="labelpage" runat="server"/>页
| <asp:label id="labelrecord" runat="server"/>条信息 |
<asp:hyperlink id="hlfistpage" text="首页" runat="server"/>
| <asp:hyperlink id="hlprevpage" text="上一页" runat="server"/>
| <asp:hyperlink id="hlnextpage" text="下一页" runat="server"/>
| <asp:hyperlink id="hlendpage" text="尾页" runat="server"/></span><br>
<asp:repeater id=repeater runat="server">
<headertemplate>
<table width="583" border="0" cellspacing="0" cellpadding="0">
<tr>
<td bgcolor="#000000"><table width="100%" border="0" cellpadding="4" cellspacing="1" class="tp10_5">
<tr bgcolor="#999999">
<td align="center"> <strong><font color="#ffffff">订单号</font></strong></td>
<td align="center"> <strong><font color="#ffffff">服务项目</font></strong></td>
<td align="center"> <strong><font color="#ffffff">预订日期</font></strong></td>
<td align="center"> <strong><font color="#ffffff">操作人员</font></strong></td>
<td align="center"> <strong><font color="#ffffff">分配状态</font></strong></td>
<td> <div align="center"></div></td>
</tr>
</headertemplate>
<itemtemplate>
<tr align="center" bgcolor="#ffffff" class="small" onmouseover='this.style.background="#cccccc"' onmouseout='this.style.background="#ffffff"'>
<td><%# databinder.eval(container.dataitem, "offerid") %></td>
<td><%# databinder.eval(container.dataitem, "type") %></td>
<td><%# databinder.eval(container.dataitem, "offertime") %></td>
<td> </td>
<td> </td>
<td><a href="java script:void(window.open('info.asp?id=<%# databinder.eval(container.dataitem, "offerid") %>','订单分配','height=600,width=1000'))">订单详情</a></td>
</tr>
</itemtemplate>
<footertemplate>
</table></td>
</tr>
</table>
</footertemplate>
</asp:repeater>
</form>
</body>
</html>
--------------------------------------------------------------------------------
up_gettopiclist.sql
--------------------------------------------------------------------------------
create proc up_gettopiclist
@a_tablelist varchar(200),
@a_tablename varchar(30),
@a_selectwhere varchar(500),
@a_selectorderid varchar(20),
@a_selectorder varchar(50),
@a_intpageno int,
@a_intpagesize int,
@recordcount int output
as
/*定义局部变量*/
declare @intbeginid int
declare @intendid int
declare @introotrecordcount int
declare @introwcount int
declare @tmpselect nvarchar(600)
/*关闭计数*/
set nocount on
/*求总共根贴数*/
select @tmpselect = 'set nocount on;select @spintrootrecordcount = count(*) from '[email protected]_tablename+' '[email protected]_selectwhere
execute sp_executesql
@tmpselect,
n'@spintrootrecordcount int output',
@[email protected] output
select @recordcount = @introotrecordcount
if (@introotrecordcount = 0) --如果没有贴子，则返回零
return 0
/*判断页数是否正确*/
if (@a_intpageno - 1) * @a_intpagesize > @introotrecordcount
return (-1)
/*求开始rootid*/
set @introwcount = (@a_intpageno - 1) * @a_intpagesize + 1
/*限制条数*/
select @tmpselect = 'set nocount on;set rowcount @spintrowcount;select @spintbeginid = '[email protected]_selectorderid+' from '[email protected]_tablename+' '[email protected]_selectwhere+' '[email protected]_selectorder
execute sp_executesql
@tmpselect,
n'@spintrowcount int,@spintbeginid int output',
@[email protected],@[email protected] output
/*结束rootid*/
set @introwcount = @a_intpageno * @a_intpagesize
/*限制条数*/
select @tmpselect = 'set nocount on;set rowcount @spintrowcount;select @spintendid = '[email protected]_selectorderid+' from '[email protected]_tablename+' '[email protected]_selectwhere+' '[email protected]_selectorder
execute sp_executesql
@tmpselect,
n'@spintrowcount int,@spintendid int output',
@[email protected],@[email protected] output
if @a_selectwhere=' or @a_selectwhere is null
select @tmpselect = 'set nocount off;set rowcount 0;select '[email protected]_tablelist+' from '[email protected]_tablename+' where '[email protected]_selectorderid+' between '
else
select @tmpselect = 'set nocount off;set rowcount 0;select '[email protected]_tablelist+' from '[email protected]_tablename+' '[email protected]_selectwhere+' and '[email protected]_selectorderid+' between '
if @intendid > @intbeginid
select @tmpselect = @tmpselect+'@spintbeginid and @spintendid'+' '[email protected]_selectorder
else
select @tmpselect = @tmpselect+'@spintendid and @spintbeginid'+' '[email protected]_selectorder
execute sp_executesql
@tmpselect,
n'@spintendid int,@spintbeginid int',
@[email protected],@[email protected]
return(@@rowcount)
--select @@rowcount
go
本文来源于网页设计爱好者web开发社区http://www.html.org.cn收集整理，欢迎访问。
