存储过程分页

一，首先创建分页存储过程

CREATE PROCEDURE usp_PagingLarge 
@TableNames VARCHAR(300),    --表名，可以是多个表，但不能用别名 
@PrimaryKey VARCHAR(100),    --主键，可以为空，但@Order为空时该值不能为空 
@Fields    VARCHAR(350),        --要取出的字段，可以是多个表的字段，可以为空，为空表示select * 
@PageSize INT,            --每页记录数 
@CurrentPage INT,        --当前页，0表示第1页 
@Filter VARCHAR(200) = '',    --条件，可以为空，不用填 where 
@Group VARCHAR(200) = '',    --分组依据，可以为空，不用填 group by 
@Order VARCHAR(200) = '',   --排序，可以为空，为空默认按主键升序排列，不用填 order by 
@RecordCount int = 0 output 
AS 
BEGIN 
    DECLARE @SortColumn VARCHAR(200) 
    DECLARE @Operator CHAR(2) 
    DECLARE @SortTable VARCHAR(200) 
    DECLARE @SortName VARCHAR(200) 
    DECLARE @TmpSelect  NVarchar(200) 
    IF @Fields = '' 
        SET @Fields = '*' 
    IF @Filter = '' 
        SET @Filter = 'WHERE 1=1' 
    ELSE 
        SET @Filter = 'WHERE ' +  @Filter 
    IF @Group <>'' 
        SET @Group = 'GROUP BY ' + @Group 
    IF @Order <> '' 
    BEGIN 
        DECLARE @pos1 INT, @pos2 INT 
        SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC') 
        IF CHARINDEX(' DESC', @Order) > 0 
            IF CHARINDEX(' ASC', @Order) > 0 
            BEGIN 
                IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order) 
                    SET @Operator = '<=' 
                ELSE 
                    SET @Operator = '>=' 
            END 
            ELSE 
                SET @Operator = '<=' 
        ELSE 
            SET @Operator = '>=' 
        SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '') 
        SET @pos1 = CHARINDEX(',', @SortColumn) 
        IF @pos1 > 0 
            SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1) 
        SET @pos2 = CHARINDEX('.', @SortColumn) 
        IF @pos2 > 0 
        BEGIN 
            SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1) 
            IF @pos1 > 0 
                SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1) 
            ELSE 
                SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2) 
        END 
        ELSE 
        BEGIN 
            SET @SortTable = @TableNames 
            SET @SortName = @SortColumn 
        END 
    END 
    ELSE 
    BEGIN 
        SET @SortColumn = @PrimaryKey 
        SET @SortTable = @TableNames 
        SET @SortName = @SortColumn 
        SET @Order = @SortColumn 
        SET @Operator = '>=' 
    END 
    DECLARE @type varchar(50) 
    DECLARE @prec int 
    SELECT @type=t.name, @prec=c.prec 
    FROM sysobjects o 
    JOIN syscolumns c on o.id=c.id 
    JOIN systypes t on c.xusertype=t.xusertype 
    WHERE o.name = @SortTable AND c.name = @SortName 
    IF CHARINDEX('char', @type) > 0 
    SET @type = @type + '(' + CAST(@prec AS varchar) + ')' 
   
    DECLARE @TopRows INT 
    SET @TopRows = @PageSize * @CurrentPage + 1 
    print @TopRows 
    print @Operator 
    EXEC(' 
        DECLARE @SortColumnBegin ' + @type + ' 
        SET ROWCOUNT ' + @TopRows + ' 
        SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + ' 
        SET ROWCOUNT ' + @PageSize + ' 
        SELECT ' + @Fields + ' FROM  ' + @TableNames + ' ' + @Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '     
    ') 
    DECLARE @str_Count_SQL nvarchar(500) 
    SET @str_Count_SQL= 'SELECT @TotalCount=count('+@PrimaryKey+') FROM ' + @TableNames + ' ' + @Filter 
    EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output 
End 
GO

二，编写sqlHelper方法

/// <summary> 
        /// 执行存储过程返回 DataTable 
        /// </summary> 
        /// <param name="storedProcName">存储过程名</param> 
        /// <param name="parameters">存储过程参数</param> 
        /// <returns>DataTable</returns> 
        public static DataTable ExecuteDataTableByProc(string storedProcName, SqlParameter[] parameters) 
        { 
            using (SqlConnection connection = new SqlConnection(connectionString)) 
            { 
                connection.Open(); 
                SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); 
                SqlDataAdapter da = new SqlDataAdapter(); 
                DataSet ds = new DataSet(); 
                da.SelectCommand = command; 
                da.Fill(ds); 
                DataTable dt = ds.Tables[0]; 
                return dt; 
            } 
        } 

        /// <summary> 
        /// 执行存储过程返回SqlDataReader 
        /// </summary> 
        /// <param name="storedProcName">存储过程名</param> 
        /// <param name="parameters">存储过程参数</param> 
        /// <returns>SqlDataReader</returns> 
        public static SqlDataReader ExecuteSqlDataReaderByProc(string storedProcName, SqlParameter[] parameters) 
        { 
            SqlConnection connection = new SqlConnection(connectionString); 
            SqlDataReader returnReader; 
            connection.Open(); 
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); 
            command.CommandType = CommandType.StoredProcedure; 
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection); 
            return returnReader; 
        } 

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, SqlParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)   
                command.Parameters.Add( parameter );   
            return command;   
        }

数据库层代码如下：

    public class Notice:INotice
    {
        public Notice() { }
        public int GetMaxId()
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select max(NoticeID)+1 from Notice");
            object obj = DbHelperSQL.ExecuteScalar(strSQL.ToString());
            if (obj == null)
                return 1;
            else
                return Convert.ToInt32(obj);
        }
        public bool Exists(int NoticeID)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select count(1) from Notice ");
            strSQL.Append("where NoticeID=@NoticeID");
            SqlParameter parm = new SqlParameter("@NoticeID", SqlDbType.Int, 4);
            parm.Value = NoticeID;
            object obj = DbHelperSQL.ExecuteScalar(strSQL.ToString(), parm);
            int result;
            if ((object.Equals(obj, null)) || (object.Equals(obj, System.DBNull.Value)))
                result = 0;
            else
                result = Convert.ToInt32(obj.ToString());
            if (result == 0)
                return false;
            else
                return true;
        }
        public void Add(Model.Notice model)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("insert into Notice values (");
            strSQL.Append("@NoticeTitle,@TitleColor,@NoticeContent,@Disabled,@IsTop,@AddDate,@AddAdminName)");
            SqlParameter[] parlist ={
                  new SqlParameter("@NoticeTitle",SqlDbType.VarChar,100),
                  new SqlParameter("@TitleColor",SqlDbType.VarChar,50),
                  new SqlParameter("@NoticeContent",SqlDbType.Text),
                  new SqlParameter("@Disabled",SqlDbType.Bit,1),
                  new SqlParameter("@IsTop",SqlDbType.Bit,1),
                  new SqlParameter("@AddDate",SqlDbType.DateTime,8),
                   new SqlParameter("@AddAdminName",SqlDbType.VarChar,50),
             };
            parlist[0].Value = model.NoticeTitle;
            parlist[1].Value = model.TitleColor;
            parlist[2].Value = model.NoticeContent;
            parlist[3].Value = model.Disabled;
            parlist[4].Value = model.IsTop;
            parlist[5].Value = model.AddDate;
            parlist[6].Value = model.AddAdminName;
            DbHelperSQL.ExecuteNonQueryBySQL(strSQL.ToString(), parlist);
        }
        public void Update(Model.Notice model)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("update Notice set ");
            strSQL.Append("NoticeTitle=@NoticeTitle,");
            strSQL.Append("TitleColor=@TitleColor,");
            strSQL.Append("NoticeContent=@NoticeContent,");
            strSQL.Append("Disabled=@Disabled,");
            strSQL.Append("IsTop=@IsTop,");
            strSQL.Append("AddDate=@AddDate,");
            strSQL.Append("AddAdminName=@AddAdminName ");
            strSQL.Append("where NoticeID=@NoticeID");
            SqlParameter[] parlist ={
                 new SqlParameter("@NoticeTitle",SqlDbType.VarChar,100),
                 new SqlParameter("@TitleColor",SqlDbType.VarChar,50),
                 new SqlParameter("@NoticeContent",SqlDbType.Text),
                 new SqlParameter("@Disabled",SqlDbType.Bit,1),
                 new SqlParameter("@IsTop",SqlDbType.Bit,1),
                 new SqlParameter("@AddDate",SqlDbType.DateTime,8),
                 new SqlParameter("@AddAdminName",SqlDbType.VarChar,50),
                 new SqlParameter("@NoticeID",SqlDbType.Int,4),
               };
            parlist[0].Value = model.NoticeTitle;
            parlist[1].Value = model.TitleColor;
            parlist[2].Value = model.NoticeContent;
            parlist[3].Value = model.Disabled;
            parlist[4].Value = model.IsTop;
            parlist[5].Value = model.AddDate;
            parlist[6].Value = model.AddAdminName;
            parlist[7].Value = model.NoticeID;
            DbHelperSQL.ExecuteNonQueryBySQL(strSQL.ToString(), parlist);
        }
        public void Delete(int NoticeID)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("delete from Notice ");
            strSQL.Append("where NoticeID=@NoticeID");
            SqlParameter parm = new SqlParameter("@NoticeID", SqlDbType.Int, 4);
            parm.Value = NoticeID;
            DbHelperSQL.ExecuteNonQueryBySQL(strSQL.ToString(), parm);
        }
        public Model.Notice GetModel(int NoticeID)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select NoticeTitle,TitleColor,NoticeContent,Disabled,IsTop,AddDate,AddAdminName from Notice ");
            strSQL.Append("where NoticeID=@NoticeID");
            SqlParameter parm = new SqlParameter("@NoticeID", SqlDbType.Int, 4);
            parm.Value = NoticeID;
            Model.Notice model = new Model.Notice();

            //using (SqlDataReader dr = DbHelperSQL.ExecuteSqlDataReaderBySQL(strSQL.ToString(), parm))
            //{
            //    if (dr.Read())
            //    {
            //        model.NoticeTitle = dr.GetString(0);
            //        model.TitleColor = dr.GetString(1);
            //        model.NoticeContent = dr.GetString(2);
            //        model.Disabled = dr.GetBoolean(3);
            //        model.IsTop = dr.GetBoolean(4);
            //        model.AddDate = dr.GetDateTime(5);
            //        model.AddAdminName = dr.GetString(6);
            //    }
            //}
            //return model;

            DataSet ds = DbHelperSQL.ExecuteDataSetBySQL(strSQL.ToString(), parm);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.NoticeTitle = ds.Tables[0].Rows[0]["NoticeTitle"].ToString();
                model.TitleColor = ds.Tables[0].Rows[0]["TitleColor"].ToString();
                model.NoticeContent = ds.Tables[0].Rows[0]["NoticeContent"].ToString();
                model.AddAdminName = ds.Tables[0].Rows[0]["AddAdminName"].ToString();
                if ((ds.Tables[0].Rows[0]["Disabled"].ToString() == "1") || (ds.Tables[0].Rows[0]["Disabled"].ToString().ToLower() == "true"))
                    model.Disabled = true;
                else
                    model.Disabled = false;
                if ((ds.Tables[0].Rows[0]["IsTop"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsTop"].ToString().ToLower() == "true"))
                    model.IsTop = true;
                else
                    model.IsTop = false;
                if (ds.Tables[0].Rows[0]["AddDate"].ToString() != "")
                    model.AddDate = DateTime.Parse(ds.Tables[0].Rows[0]["AddDate"].ToString());
                return model;
            }
            else
                return null;
        }
        public DataSet GetDataSet(string strWhere)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select * from Notice");
            if (strWhere.Trim() != "")
                strSQL.Append(" where " + strWhere);
            strSQL.Append(" order by NoticeID desc");
            return DbHelperSQL.ExecuteDataSetBySQL(strSQL.ToString());
        }
        public int GetRecordCount(string strWhere)
        {
            int result = 0;
            SqlParameter[] parlist ={
                new SqlParameter("@tblName",SqlDbType.VarChar,50),
                new SqlParameter("@strWhere",SqlDbType.VarChar,1000),
             };
            parlist[0].Value = "Notice";
            parlist[1].Value = strWhere;
            using (SqlDataReader dr = DbHelperSQL.ExecuteSqlDataReaderByProc("GetRecordCount", parlist))
            {
                if (dr.Read())
                    result = dr.GetInt32(0);
            }
            return result;
        }
        public IList<Model.Notice> GetList(string filename, string sortfilename, int pageSize, int pageIndex, int OrderType, string strWhere)
        {
            IList<Model.Notice> notices = new List<Model.Notice>();
            Model.Notice model = null;
            SqlParameter[] parlist ={
                new SqlParameter("@tblName",SqlDbType.VarChar,255),
                new SqlParameter("@strGetFields",SqlDbType.VarChar,1000),
                new SqlParameter("@fldName",SqlDbType.VarChar,255),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@PageIndex",SqlDbType.Int),
                new SqlParameter("@OrderType",SqlDbType.Int),
                new SqlParameter("@strWhere",SqlDbType.VarChar,500),
            };
            parlist[0].Value = "Notice";
            parlist[1].Value = filename;
            parlist[2].Value = sortfilename;
            parlist[3].Value = pageSize;
            parlist[4].Value = pageIndex;
            parlist[5].Value = OrderType;
            parlist[6].Value = strWhere;
            using (SqlDataReader dr = DbHelperSQL.ExecuteSqlDataReaderByProc("UP_GetRecordByPage", parlist))
            {
                while (dr.Read())
                {
                    model = new Model.Notice();
                    model.NoticeID = dr.GetInt32(0);
                    model.NoticeTitle = dr.GetString(1);
                    model.TitleColor = dr.GetString(2);
                    model.NoticeContent = dr.GetString(3);
                    model.Disabled = dr.GetBoolean(4);
                    model.IsTop = dr.GetBoolean(5);
                    model.AddDate = dr.GetDateTime(6);
                    model.AddAdminName = dr.GetString(7);
                    notices.Add(model);
                }
            }
            return notices;
        }
        public IList<Model.Notice> GetListByWhere(string strWhere, string Order, int PageSize, int CurrentPage, out int Record)
        {
            IList<Model.Notice> notice = new List<Model.Notice>();
            Model.Notice model = null;
            SqlParameter[] parlist ={
                new SqlParameter("@TableNames",SqlDbType.VarChar,300),
                new SqlParameter("@PrimaryKey",SqlDbType.VarChar,100),
                new SqlParameter("@Fields",SqlDbType.VarChar,350),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@CurrentPage",SqlDbType.Int),
                new SqlParameter("@Filter",SqlDbType.VarChar,200),
                new SqlParameter("@Group",SqlDbType.VarChar,200),
                new SqlParameter("@Order",SqlDbType.VarChar,200),
                new SqlParameter("@RecordCount",SqlDbType.Int),
             };
            parlist[0].Value = "Notice";
            parlist[1].Value = "NoticeID";
            parlist[2].Value = "NoticeID,NoticeTitle,TitleColor,NoticeContent,AddDate,IsTop";
            parlist[3].Value = PageSize;
            parlist[4].Value = CurrentPage;
            parlist[5].Value = strWhere;
            parlist[6].Value = "";
            parlist[7].Value = Order;
            parlist[8].Direction = ParameterDirection.Output;
            using (SqlDataReader dr = DbHelperSQL.ExecuteSqlDataReaderByProc("usp_PagingLarge", parlist))
            {
                while (dr.Read())
                {
                    model = new Model.Notice();
                    model.NoticeID = dr.GetInt32(0);
                    model.NoticeTitle = dr.GetString(1);
                    model.TitleColor = dr.GetString(2);
                    model.NoticeContent = dr.GetString(3);
                    model.AddDate = dr.GetDateTime(4);
                    model.IsTop = dr.GetBoolean(5);
                    notice.Add(model);
                }
            }
            Record = Convert.ToInt32(parlist[8].Value);
            return notice;
        }
        public DataTable GetListByWhereDataTable(string strWhere, string Order, int PageSize, int CurrentPage, out int Record)
        {
            DataTable dt = new DataTable();
            SqlParameter[] parlist ={
                new SqlParameter("@TableNames",SqlDbType.VarChar,300),
                new SqlParameter("@PrimaryKey",SqlDbType.VarChar,100),
                new SqlParameter("@Fields",SqlDbType.VarChar,350),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@CurrentPage",SqlDbType.Int),
                new SqlParameter("@Filter",SqlDbType.VarChar,200),
                new SqlParameter("@Group",SqlDbType.VarChar,200),
                new SqlParameter("@Order",SqlDbType.VarChar,200),
                new SqlParameter("@RecordCount",SqlDbType.Int),
              };
            parlist[0].Value = "Notice";
            parlist[1].Value = "NoticeID";
            parlist[2].Value = "NoticeID,NoticeTitle,TitleColor,NoticeContent,AddDate,IsTop";
            parlist[3].Value = PageSize;
            parlist[4].Value = CurrentPage;
            parlist[5].Value = strWhere;
            parlist[6].Value = "";
            parlist[7].Value = Order;
            parlist[8].Direction = ParameterDirection.Output;
            dt = DbHelperSQL.ExecuteDataTableByProc("usp_PagingLarge", parlist);
            Record = Convert.ToInt32(parlist[8].Value);
            return dt;
        }
    }

前台页面代码：

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head id="Head1" runat="server"> 
    <title>存储过程分页</title> 
    <meta http-equiv="Content-Language" content="zh-CN" /> 
    <meta content="all" name="MyWeb" /> 
    <meta name="author" content="晴儿" /> 
    <meta name="Copyright" content="版权归晴儿所有" /> 
    <meta name="description" content="晴儿个人网站系统" /> 
    <meta content="文章,新闻,相册,实用工具" name="keywords" /> 
    <meta content="文章,新闻,相册,实用工具" name="searchtitle" /> 
    <meta http-equiv="Content-Type" content="text/html; charset=gb2312" /> 
    <link href="../images/style.css" type="text/css" rel="stylesheet" /> 
    <script language="javascript" type="text/javascript" src="../Script/DateOperate.js"></script> 
    <script language="javascript" type="text/javascript" src="../Script/calendar.js"></script> 
    <script language="javascript" type="text/javascript"> 
        function check() 
        { 
            var checks=false; 
            var innar=document.getElementsByTagName("input"); 
            for(var i=0;i<innar.length;i++) 
            { 
                if(innar.type=="checkbox") 
                { 
                    if(innar.checked==true) 
                        checks=true; 
                } 
            } 
            if(checks==false) 
            { 
                alert("您未选中任何记录！"); 
                return false; 
            } 
            else 
            { 
                return confirm('您确定要删除所有选中的记录吗?'); 
                return true; 
            } 
        } 
        function quanxuan() 
        { 
            var innar=document.getElementsByTagName("input"); 
            for(var i=0;i<innar.length;i++) 
            { 
                if(innar.type=="checkbox") 
                    innar.checked=true; 
            } 
        } 
        function fanxuan() 
        { 
            var innar=document.getElementsByTagName("input"); 
            for(var i=0;i<innar.length;i++) 
            { 
                if(innar.type=="checkbox") 
                { 
                    if(innar.checked==true) 
                        innar.checked=false; 
                    else 
                        innar.checked=true; 
                } 
            } 
        } 
        function yanzheng() 
        { 
            if(isDate(document.getElementById("txtStartTime").value)==false)             
            { 
                alert('开始日期时间格式错误！正确格式为：2000-12-12 00:00:00'); 
                return false; 
            } 
            if(isDate(document.getElementById("txtEndTime").value)==false) 
            { 
                alert('结束日期时间格式错误！正确格式为：2000-12-12 00:00:00'); 
                return false; 
            } 
            if(compareDate(document.getElementById("txtStartTime").value,document.getElementById("txtEndTime").value)=="0") 
            { 
                alert('查询开始日期必须小于结束日期！'); 
                return false; 
            } 
        } 
    </script> 
</head> 
<body> 
    <form id="form2" runat="server"> 
    <div id="hearder"> 
        <span>公告管理</span></div> 
    <div id="site"> 
        您的位置：<a href="../index.aspx"> 管理首页 </a>>> 公告管理 
    </div> 
     

    <table class="table1" border="1" cellpadding="2" cellspacing="1"> 
        <tr class="tr1"> 
            <td class="td1"> 
                公告标题： 
            </td> 
            <td class="td2"> 
                <asp:TextBox ID="txtKey" runat="server" CssClass="input1" Width="150px"></asp:TextBox> 
            </td> 
            <td class="td1"> 
                状态： 
            </td> 
            <td class="td2"> 
                <select id="Status" runat="server" class="select1"> 
                    <option selected="selected" value="">所有</option> 
                    <option value="1">置顶</option> 
                    <option value="0">不置顶</option> 
                </select> 
            </td> 
            <td rowspan="2" class="td3"> 
                <asp:Button ID="btnSearch" runat="server" Text="查询" CssClass="button" OnClientClick="return yanzheng();" 
                    OnClick="btnSearch_Click" /> 
            </td> 
        </tr> 
        <tr class="tr1"> 
            <td class="td1"> 
                时间段： 
            </td> 
            <td class="td2"> 
                <table cellpadding="0" cellspacing="0" border="0"> 
                    <tr> 
                        <td> 
                            <asp:TextBox ID="txtStartTime" CssClass="input1" Width="140px" runat="server"></asp:TextBox> 
                        </td> 
                        <td> 
                            <a href="javascript:void(0);" onclick="javascript:setday(this,txtStartTime,true,false);" 
                                style="font-weight: bold; color: Red;">选择</a> 
                        </td> 
                        <td> 
                            --- 
                        </td> 
                        <td> 
                            <asp:TextBox ID="txtEndTime" CssClass="input1" Width="140px" runat="server"></asp:TextBox> 
                        </td> 
                        <td> 
                            <a href="javascript:void(0);" onclick="javascript:setday(this,txtEndTime,true,false);" 
                                style="font-weight: bold; color: Red;">选择</a> 
                        </td> 
                        <td style="font-weight: bold; color: Fuchsia; font-size: 13px">(默认查询一个月记录)</td> 
                    </tr> 
                </table> 
            </td> 
        </tr> 
    </table> 
    <table class="table2"> 
        <tr class="tablehearder4"> 
            <td class="tablehearder1" align="center"> 
                选择 
            </td> 
            <td  class="tablehearder1" align="center"> 
                公告标题 
            </td> 
            <td class="tablehearder1" align="center"> 
                公告信息 
            </td> 
            <td align="center"  class="tablehearder1" > 
                是否置顶 
            </td> 
            <td align="center" class="tablehearder1"> 
                添加时间 
            </td> 
            <td align="center" class="tablehearder1"> 
                修改 
            </td> 
            <td align="center" class="tablehearder1"> 
                删除 
            </td> 
        </tr> 
        <asp:Repeater ID="Repeater1" runat="server" OnItemCommand="Repeater1_ItemCommand"> 
            <ItemTemplate> 
                <tr class="tr2" onmouseover="this.style.backgroundColor='#fffff2';this.style.color='red'" 
                    onmouseout="this.style.backgroundColor='#ffffff';this.style.color=''"> 
                    <td height="25"> 
                        <asp:CheckBox ID="CheckBox1" runat="server" /><asp:HiddenField ID="HiddenField1" 
                            runat="server" Value='<%#Eval("NoticeID") %>' /> 
                    </td> 
                   <td> 
                        <span style='color: <%#Eval("TitleColor") %>'> 
                            <%#PageValidate.CutString(Eval("NoticeTitle").ToString(),12)%> 
                        </span> 
                    </td> 
                    <td align="center"> 
                        <%#PageValidate.CutString(Eval("NoticeContent").ToString(),20)%> 
                    </td> 
                     <td align="center"> 
                        <%#WebUtinity.GetIsTop(Eval("IsTop").ToString())%> 
                    </td> 
                    <td align="center"> 
                        <%#Eval("AddDate")%> 
                    </td> 
                    <td align="center"> 
                        <a href='#'> 
                            <img src="../images/icon_xg.gif" alt="点击修改公告" /></a> 
                    </td> 
                    <td align="center"> 
                        <asp:ImageButton ID="imbDelete" runat="server" CommandName="delete" CommandArgument='<%#Eval("NoticeID") %>' 
                            ImageUrl="../images/icon_sc.gif" AlternateText="删除" OnClientClick="return confirm('确认删除此公告吗? 删除后不可恢复！');"> 
                        </asp:ImageButton> 
                    </td> 
                </tr> 
            </ItemTemplate> 
            <AlternatingItemTemplate> 
                <tr class="tr2" onmouseover="this.style.backgroundColor='#fffff4';this.style.color='red'" 
                    onmouseout="this.style.backgroundColor='#EEF7FF';this.style.color=''"> 
                    <td height="25"> 
                        <asp:CheckBox ID="CheckBox1" runat="server" /><asp:HiddenField ID="HiddenField1" 
                            runat="server" Value='<%#Eval("NoticeID") %>' /> 
                    </td> 
                    <td> 
                        <span style='color: <%#Eval("TitleColor") %>'> 
                            <%#PageValidate.CutString(Eval("NoticeTitle").ToString(),12)%> 
                        </span> 
                    </td> 
                    <td align="center"> 
                        <%#PageValidate.CutString(Eval("NoticeContent").ToString(),20)%> 
                    </td> 
                     <td align="center"> 
                        <%#WebUtinity.GetIsTop(Eval("IsTop").ToString())%> 
                    </td> 
                    <td align="center"> 
                        <%#Eval("AddDate")%> 
                    </td> 
                    <td align="center"> 
                        <a href='#'> 
                            <img src="../images/icon_xg.gif" alt="点击修改公告" /></a> 
                    </td> 
                    <td align="center"> 
                        <asp:ImageButton ID="imbDelete" runat="server" CommandName="delete" CommandArgument='<%#Eval("NoticeID") %>' 
                            ImageUrl="../images/icon_sc.gif" AlternateText="删除" OnClientClick="return confirm('确认删除此公告吗? 删除后不可恢复！');"> 
                        </asp:ImageButton> 
                    </td> 
                </tr> 
            </AlternatingItemTemplate> 
        </asp:Repeater> 
        <asp:Panel ID="Panel3" runat="server"> 
            <tr class="tablehearder2"> 
                <td align="center" colspan="10"> 
                    <asp:LinkButton ID="lbtDeleteAll" runat="server" OnClick="lbtDeleteAll_Click" OnClientClick="return check();" 
                        ForeColor="#0066cc" Font-Bold="true">删除选中</asp:LinkButton>   
                    <a href="javascript:quanxuan();" style="font-weight: bold; color: #0066cc">全选</a>   
                    <a href="javascript:fanxuan();" style="font-weight: bold; color: #0066cc">反选</a> 
                </td> 
            </tr> 
        </asp:Panel> 
        <asp:Panel ID="Panel1" runat="server" Visible="false"> 
            <tr class="tablehearder2"> 
                <td colspan="10" class="tablehearder3"> 
                    没有查询到符合该查询条件的记录！<a href="#" style="color: Blue; font-weight: bold">点击此处</a>添加公告 
                </td> 
            </tr> 
        </asp:Panel> 
    </table> 

    分页样式一：<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>     
    <asp:Label ID="Label3" runat="server" Text="Label"></asp:Label> 
     

    分页样式二：<asp:Label ID="Label4" runat="server" Text="Label"></asp:Label>     
    <asp:Label ID="Label5" runat="server" Text="Label"></asp:Label> 
    </form> 
</body> 
</html>

using System; 
using System.Collections; 
using System.Configuration; 
using System.Data; 
using System.Linq; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.HtmlControls; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Xml.Linq; 

public partial class Admin_noticeManage_Notice : System.Web.UI.Page 
{ 
    BLL.Notice notice = new BLL.Notice(); 
     
    // 总记录 
    private int Record; 
    // 每页显示记录数 
    private int PageSize = 10; 
    // 当前页 
    private int CurrentPage = 1; 
    private string strUrl = ""; 
    protected void Page_Load(object sender, EventArgs e) 
    { 
        if (Request.QueryString["page"] != null) 
        { 
            if (!Int32.TryParse(Request.QueryString["page"].ToString(), out CurrentPage)) 
            { 
                ClientScript.RegisterStartupScript(GetType(), "", "alert('请输入整数页！');", true); 
                return; 
            } 
        } 
        if (!IsPostBack) 
        { 
            BindData(); 
            BindSOSOPager(); 
            BindPager(); 
            if (Repeater1.Items.Count < 1) 
            { 
                Panel1.Visible = true; 
                Panel3.Visible = false; 
            } 
        } 
    } 
    private void BindData() 
    { 
        try 
        { 
            int y = Convert.ToInt32(DateTime.Now.Year.ToString()); 
            int m = Convert.ToInt32(DateTime.Now.Month.ToString()); 
            int d = Convert.ToInt32(DateTime.Now.Day.ToString()); 
            if (m == 1) 
                txtStartTime.Text = Convert.ToString(y - 1) + "-" + "12" + "-" + d.ToString() + " " + DateTime.Now.ToLongTimeString(); 
            else 
                txtStartTime.Text = y.ToString() + "-" + Convert.ToInt32(m - 1) + "-" + d.ToString() + " " + DateTime.Now.ToLongTimeString(); 
            txtEndTime.Text = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); 
            string strWhere = ""; 
            if (Request.QueryString["key"] != null && Request.QueryString["istop"] != null && Request.QueryString["starttime"] != null && Request.QueryString["endtime"] != null) 
            { 
                txtKey.Text = Request.QueryString["key"]; 
                Status.Value = Request.QueryString["istop"]; 
                txtStartTime.Text = Request.QueryString["starttime"]; 
                txtEndTime.Text = Request.QueryString["endtime"]; 
                strUrl = string.Format("&key={0}&istop={1}&starttime={2}&endtime={3}", Request.QueryString["key"], Request.QueryString["istop"], Request.QueryString["starttime"], Request.QueryString["endtime"]); 
                if (Status.SelectedIndex == 0) 
                    strWhere = "NoticeTitle like '%" + Request.QueryString["key"] + "%' and AddDate between '" + Request.QueryString["starttime"] + "' and '" + Request.QueryString["endtime"] + "'"; 
                else 
                    strWhere = "NoticeTitle like '%" + Request.QueryString["key"] + "%' and IsTop=" + byte.Parse(Status.Value) + " and AddDate between '" + Request.QueryString["starttime"] + "' and '" + Request.QueryString["endtime"] + "'"; 
            } 
            else 
                strWhere =  "AddDate between '" + txtStartTime.Text + "' and '" + txtEndTime.Text + "'"; 
             //这里用 notice.GetListByWhereDataTable(); 方法也可以，但IList效率要高 
            Repeater1.DataSource = notice.GetListByWhere(strWhere, "IsTop desc,AddDate desc", PageSize, CurrentPage, out Record); 
            Repeater1.DataBind();             
        } 
        catch 
        { 
            ClientScript.RegisterStartupScript(GetType(), "", "alert('出现错误！');", true); 
        } 
    } 
    private void BindPager()     
    { 
        // 偏移量 
        int Step = 10; 
        // 左页码 
        int LeftNum = 0; 
        // 右页码 
        int RightNum = 0; 
        // 获取当前请求的虚拟路径 
        string PageUrl = Request.FilePath; 
        // 获取总的页数 
        int PageCount = (int)Math.Ceiling((double)(Record) / PageSize); 
        // 如果当前页码小于等于偏移量，就把左页码设置为1 
        if (CurrentPage - Step < 1) 
            LeftNum = 1; 
        // 否则左页码=当前页码-偏移量 
        else 
            LeftNum = CurrentPage - Step; 
        // 如果总页码减去偏移量小于等于当前页码，就把右页码设置为总页码 
        if (CurrentPage + Step > PageCount) 
            RightNum = PageCount; 
        // 否则把右页码设置为当前页码+偏移量 
        else 
            RightNum = CurrentPage + Step; 
        System.Text.StringBuilder sb = new System.Text.StringBuilder(); 
        // 循环生成页码 
        for (int i = LeftNum; i <= RightNum; i++) 
        { 
            // 如果是本页，字体变红，并且没有链接 
            if (i == CurrentPage) 
            { 
                // 注意这里的 margin-left 是让他们之间产生间隙，也可以用   代替 
                sb.Append("<span style='margin-left:3px;color:red'>"); 
                sb.Append(i.ToString()); 
                sb.Append("</span>"); 
            } 
            // 如果不是本页，字体颜色不变，链接到相应页码 
            else 
            { 
                sb.Append("<a style='margin-left:3px;' href='"); 
                sb.Append(PageUrl); 
                sb.Append("?page="); 
                sb.Append(i.ToString()); 
                // 查询条件 
                sb.Append(strUrl); 
                sb.Append("'>"); 
                sb.Append(i.ToString()); 
                sb.Append("</a>"); 
            } 
        } 
        // 如果当前页不是第一页，则左边出现“上一页”，注意这里是用 sb.Insert 方法在 sb 最前面插入 
        if (CurrentPage > 1) 
        { 
            sb.Insert(0, string.Format("<a href='{0}?page={1}{2}'>上一页  </a>", PageUrl, (CurrentPage - 1), strUrl)); 
        } 
        // 如果当前页不是最后一页，则出现“下一页” 
        if (CurrentPage < PageCount) 
        { 
            sb.Append("<a href='"); 
            sb.Append(PageUrl); 
            sb.Append("?page="); 
            sb.Append(CurrentPage + 1); 
            sb.Append(strUrl); 
            sb.Append("'>  下一页</a>"); 
        } 
        Label3.Text = string.Format("总记录数：<span style='color:red'>{0}</span>  总页数：<span style='color:red'>{1}</span>  当前页码：<span style='color:red'>{2}</span>", Record, PageCount, CurrentPage); 
        Label4.Text = sb.ToString(); 
    }

// 此方法主要实现类似与搜搜网的分页 http://wenwen.soso.com/z/TopQuestion.e?sp=9

private void BindSOSOPager() 
    { 
        // 获取当前请求的虚拟路径 
        string PageUrl = Request.FilePath; 
        // 获取总的页数 
        int PageCount = (int)Math.Ceiling((double)(Record) / PageSize); 
        System.Text.StringBuilder sb = new System.Text.StringBuilder(); 
        if (Request.QueryString["page"] == null || Request.QueryString["page"] == "1") 
        { 
            if (PageCount > 1) 
            { 
                if (PageCount < 12) 
                { 
                    for (int i = 1; i <= PageCount; i++) 
                    { 
                        if (i == 1) 
                            sb.Append("<span style='color:red; font-weight:bold'>　1</span>"); 
                        else 
                            sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); 
                    } 
                } 
                else 
                { 
                    for (int i = 1; i <= PageCount; i++) 
                    { 
                        if (i < 11) 
                        { 
                            if (i == 1) 
                                sb.Append("<span style='color:red; font-weight:bold'>　1</span>"); 
                            else 
                                sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); 
                        } 
                        else if (i == PageCount) 
                            sb.Append(string.Format(" ... [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); 
                    } 
                } 
                sb.Append(string.Format(" <a href='{0}?page=2{1}'>下一页</a>", PageUrl, strUrl)); 
            } 
        } 
        else if (Request.QueryString["page"] != null || Request.QueryString["page"] != "") 
        { 
            if (PageCount < 12) 
            { 
                if (CurrentPage == 1) 
                { 
                    for (int i = 1; i <= PageCount; i++) 
                    { 
                        if (i == 1) 
                            sb.Append("<span style='color:red; font-weight:bold'>　1</span>"); 
                        else 
                            sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}]</a>", PageUrl, i.ToString(), strUrl, i.ToString())); 
                    } 
                    sb.Append(string.Format(" <a href='{0}?page={1}{2}'>下一页</a>", PageUrl, Convert.ToString(CurrentPage + 1), strUrl)); 
                } 
                else if (CurrentPage == PageCount) 
                { 
                    sb.Append(string.Format(" <a href='{0}?page={1}{2}'>上一页</a>", PageUrl, Convert.ToString(CurrentPage - 1), strUrl)); 
                    for (int i = 1; i <= PageCount; i++) 
                    { 
                        if (i == PageCount) 
                            sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString())); 
                        else 
                            sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); 
                    } 
                } 
                else 
                { 
                    sb.Append(string.Format(" <a href='{0}?page={1}{2}'>上一页</a>", PageUrl, Convert.ToString(CurrentPage - 1), strUrl)); 
                    for (int i = 1; i <= PageCount; i++) 
                    { 
                        if (i == CurrentPage) 
                            sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString())); 
                        else 
                            sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); 
                    } 
                    sb.Append(string.Format(" <a href='{0}?page={1}{2}'>下一页</a>", PageUrl, Convert.ToString(CurrentPage + 1), strUrl)); 
                } 
            } 
            else 
            { 
                if (CurrentPage != 1) 
                    sb.Append(string.Format(" <a href='{0}?page={1}{2}'>上一页</a>", PageUrl, Convert.ToString(CurrentPage - 1), strUrl)); 
                if (PageCount > 11 && CurrentPage > 6 && CurrentPage < PageCount - 5) 
                { 
                    for (int i = 1; i <= PageCount; i++) 
                    { 
                        if (i == 1 && CurrentPage > 6 && PageCount > 11) 
                            sb.Append(string.Format(" <a href='{0}?page=1{1}'>[1]</a> ...", PageUrl, strUrl)); 
                        else if (i == PageCount && CurrentPage > 6 && PageCount > 11) 
                            sb.Append(string.Format(" ... <a href='{0}?page={1}{2}'>[{3}]</a>", PageUrl, i.ToString(), strUrl, i.ToString())); 
                        else if (i.ToString() == Request.QueryString["page"].ToString()) 
                            sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString())); 
                        else if (i < CurrentPage + 5 && i > CurrentPage - 5) 
                            sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); 
                    } 
                } 
                else if (PageCount > 11 && CurrentPage > 6 && CurrentPage >= PageCount - 5) 
                { 
                    for (int i = 1; i <= PageCount; i++) 
                    { 
                        if (i == 1 && CurrentPage > 6 && PageCount > 11) 
                            sb.Append(string.Format(" <a href='{0}?page=1{1}'>[1]</a> ...", PageUrl, strUrl)); 
                        else if (CurrentPage == PageCount && i.ToString() == Request.QueryString["page"].ToString()) 
                            sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString())); 
                        else if (i.ToString() == Request.QueryString["page"].ToString()) 
                            sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString())); 
                        else if (CurrentPage > PageCount - 6 && i > PageCount - 10) 
                            sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); 
                        else if (i < CurrentPage + 5 && i > CurrentPage - 5) 
                            sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); 
                    } 
                } 
                else if (PageCount > 11 && CurrentPage <= 6 && CurrentPage < PageCount - 5) 
                { 
                    for (int i = 1; i <= PageCount; i++) 
                    { 
                        if (i == 1 && CurrentPage == 1) 
                            sb.Append("<span style='color:red; font-weight:bold'>　1</span>"); 
                        else if (i.ToString() == Request.QueryString["page"].ToString()) 
                            sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString())); 
                        else if (i == PageCount) 
                            sb.Append(string.Format(" ... <a href='{0}?page={1}{2}'>[{3}]</a>", PageUrl, i.ToString(), strUrl, i.ToString())); 
                        else if (CurrentPage < 7 && i < 11) 
                            sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); 
                        else if (i < CurrentPage + 5 && i >= CurrentPage - 5) 
                            sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); 
                    } 
                } 
                if (CurrentPage != PageCount) 
                    sb.Append(string.Format(" <a href='{0}?page={1}{2}'>下一页</a>", PageUrl, Convert.ToString(CurrentPage + 1), strUrl)); 
            } 
        } 
        Label1.Text = string.Format("总记录数：<span style='color:red'>{0}</span>  总页数：<span style='color:red'>{1}</span>  当前页码：<span style='color:red'>{2}</span>", Record, PageCount, CurrentPage); 
        Label2.Text = sb.ToString(); 
    } 

    protected void btnSearch_Click(object sender, EventArgs e) 
    { 
        Response.Redirect("Notice.aspx?key=" + txtKey.Text + "&istop=" + Status.Value + "&starttime=" + txtStartTime.Text + "&endtime=" + txtEndTime.Text); 
    } 
    protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e) 
    { 
        try 
        { 
            notice.Delete(Convert.ToInt32(e.CommandArgument.ToString())); 
            ClientScript.RegisterStartupScript(GetType(), "", "alert('删除成功！');", true); 
            BindData(); 
            BindSOSOPager(); 
            BindPager(); 
        } 
        catch 
        { 
            ClientScript.RegisterStartupScript(GetType(), "", "alert('出现错误！');", true); 
        } 
    } 

    protected void lbtDeleteAll_Click(object sender, EventArgs e) 
    { 
        try 
        { 
            for (int i = 0; i < Repeater1.Items.Count; i++) 
            { 
                if (((CheckBox)Repeater1.Items.FindControl("CheckBox1")).Checked == true) 
                    notice.Delete(Convert.ToInt32(((HiddenField)Repeater1.Items.FindControl("HiddenField1")).Value)); 
            } 
            ClientScript.RegisterStartupScript(GetType(), "", "alert('删除成功！');", true); 
            BindData(); 
            BindSOSOPager(); 
            BindPager(); 
        } 
        catch 
        { 
            ClientScript.RegisterStartupScript(GetType(), "", "alert('出现错误！');", true); 
        } 
    } 
}

posted on 2011-09-05 15:58 xiacy 阅读(214) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

xiacy

导航

公告

存储过程分页