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">

   <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="javascript: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 '+@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

posted on 2007-02-07 13:17 mbskys 阅读(94) 评论(0) 收藏举报