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

1、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 >

2、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