asp.net结合存储过程写的通用搜索分页程序_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