存儲過程改自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收集整理，歡迎訪問。
