ASP.NET结合存储过程写的通用搜索分页程序-.NET教程,Asp.Net开发-IDC资讯中心

存储过程改自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="java script: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

ASP.NET结合存储过程写的通用搜索分页程序-.NET教程,Asp.Net开发

相关推荐

热门标签

热门文章

分类目录