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 @ 2006-11-06 17:50  Athrun  阅读(231)  评论(0)  编辑  收藏  举报