ASP.NET结合存储过程写的通用搜索分页程序_动网

存储过程改自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">

<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="javascript:void(window.open('info.asp?id=<%# databinder.eval(container.dataitem, "offerid") %>','订单分配','height=600,width=1000'))">订单详情</a></td>
</tr>

</itemtemplate>

</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 '+@a_tablename+' '+@a_selectwhere
execute sp_executesql
@tmpselect,
n'@spintrootrecordcount int output',
@spintrootrecordcount=@introotrecordcount 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 = '+@a_selectorderid+' from '+@a_tablename+' '+@a_selectwhere+' '+@a_selectorder
execute sp_executesql
@tmpselect,
n'@spintrowcount int,@spintbeginid int output',
@spintrowcount=@introwcount,@spintbeginid=@intbeginid output

/*结束rootid*/
set @introwcount = @a_intpageno * @a_intpagesize
/*限制条数*/

select @tmpselect = 'set nocount on;set rowcount @spintrowcount;select @spintendid = '+@a_selectorderid+' from '+@a_tablename+' '+@a_selectwhere+' '+@a_selectorder
execute sp_executesql
@tmpselect,
n'@spintrowcount int,@spintendid int output',
@spintrowcount=@introwcount,@spintendid=@intendid output

if @a_selectwhere='' or @a_selectwhere is null
select @tmpselect = 'set nocount off;set rowcount 0;select '+@a_tablelist+' from '+@a_tablename+' where '+@a_selectorderid+' between '
else
select @tmpselect = 'set nocount off;set rowcount 0;select '+@a_tablelist+' from '+@a_tablename+' '+@a_selectwhere+' and '+@a_selectorderid+' between '

if @intendid > @intbeginid
select @tmpselect = @tmpselect+'@spintbeginid and @spintendid'+' '+@a_selectorder
else
select @tmpselect = @tmpselect+'@spintendid and @spintbeginid'+' '+@a_selectorder

execute sp_executesql
@tmpselect,
n'@spintendid int,@spintbeginid int',
@spintendid=@intendid,@spintbeginid=@intbeginid

return(@@rowcount)
--select @@rowcount
go