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

2022-07-15 80酷酷网 80kuku.com

　　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>

<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" ,
SPintEndID=intEndID,SPintBeginID=intBeginID

return(rowcount)
--select rowcount
GO