AspNetPager分页控件的使用(代码生成器)

记得关于这个AspNetPager分页控件我已经写了两次了，为什么又要写一次呢？因为我觉得这次是整理得比较详细的了，而且，还结合了存储过程与代码生成器的，废话不说了，就直接上代码步骤吧。

1、 在数据库中建立分页存储过程

-- =============================================
-- Author:		
-- Create date: 2009-07-22 12:41
-- Description:	分页，用到了ROW_NUMBER()
-- =============================================
create PROCEDURE [dbo].[proc_SplitPage]
@tblName   varchar(255),       -- 表名
@strFields varchar(1000) = '*', -- 需要返回的列，默认*
@strOrder varchar(255)='',      -- 排序的字段名，必填
@strOrderType varchar(10)='ASC', -- 排序的方式,默认ASC
@PageSize   int = 10,          -- 页尺寸,默认10
@PageIndex int = 1,           -- 页码,默认1
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL   varchar(5000)

if @strWhere !=''
set @strWhere=' where '+@strWhere

set @strSQL=
'SELECT * FROM ('+
	'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS pos,'+@strFields+' '+
	'FROM '+@tblName+' '+@strWhere+
') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)

exec (@strSQL)

2、 在代码生成器生成的DAO代码中加上获取分页数据的方法和计算记录数的方法

        /// <summary>分页获取数据列表
        /// 
        /// </summary>
        /// <param name="fields">选择的字段</param>
        /// <param name="order">排序字段</param>
        /// <param name="ordertype">排序类型</param>
        /// <param name="PageSize">页大小</param>
        /// <param name="PageIndex">页索引</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public DataSet GetList(string fields, string order, string ordertype, int PageSize, int PageIndex, string strWhere)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("proc_SplitPage");
            db.AddInParameter(dbCommand, "tblName", DbType.AnsiString, "shop_news");
            db.AddInParameter(dbCommand, "@strFields", DbType.AnsiString, fields);
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            db.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            db.AddInParameter(dbCommand, "@strOrder", DbType.String, order);
            db.AddInParameter(dbCommand, "@strOrderType", DbType.String, ordertype);
            db.AddInParameter(dbCommand, "strWhere", DbType.AnsiString, strWhere);
            return db.ExecuteDataSet(dbCommand);
        }

        /// <summary>计算记录数
        /// 
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public int CalcCount(string strWhere)
        {
            string sql = "select count(1) from shop_news";
            if (!string.IsNullOrEmpty(strWhere))
            {
                sql += " where" + strWhere;
            }
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            return int.Parse(db.ExecuteScalar(dbCommand).ToString());
        }

3、 在页面中加入anp控件并设置显示的样式

 <webdiyer:AspNetPager ID="anp"   runat="server" FirstPageText="首页" 
                        LastPageText="尾页" NextPageText="下一页" PrevPageText="上一页" 
                        CustomInfoHTML="一共%RecordCount%条记录，当前为第%CurrentPageIndex%页/共%PageCount%页" 
                        onpagechanged="anp_PageChanged" ShowCustomInfoSection="Left">
                    </webdiyer:AspNetPager>

CSS的样式

另附上CSS的代码，觉得这个样式不错，红色的
        .pages
        {
            color: #999;
            margin: 10px 200px;
        }
        .pages a, .pages .cpb
        {
            text-decoration: none;
            float: left;
            padding: 0 5px;
            border: 1px solid #ddd;
            background: #ffff;
            margin: 0 2px;
            font-size: 11px;
            color: #000;
        }
        .pages a:hover
        {
            background-color: #E61636;
            color: #fff;
            border: 1px solid #E61636;
            text-decoration: none;
        }
        .pages .cpb
        {
            font-weight: bold;
            color: #fff;
            background: #E61636;
            border: 1px solid #E61636;
        }

4、 在后台的page_load事件中先设置anp控件的总记录数，再绑定rep控件

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                anp.RecordCount = ndao.CalcCount(GetCond());
                BindRep();
            }
        }    

private string GetCond()
        {
            string cond = "";
            if (txt_key.Text.Trim().Length != 0)
            {
                string key = Niunan.Shop.Utility.Tool.GetSafeSQL(txt_key.Text.Trim());  //GetSafeSQL方法为过滤一些非法字符，其实就是替换掉而已，见博文尾部
                cond = " linkname like '%" + key + "%' or url like  '%" + key + "%'";
            }
            return cond;
        }
 

  private void BindRep()
        {
            rep.DataSource = ndao.GetList("*", "createdate", "desc", anp.PageSize, anp.CurrentPageIndex, GetCond());
            rep.DataBind();
        }

5、 设置anp控件的分页事件

  protected void anp_PageChanged(object sender, EventArgs e)
        {
            BindRep();
        }
    }

好了，就这五步曲了，相比之前那个不是用存储过程来分页的，这个应该是做到了真正意义上的真分页了，因为每次都会调用存储过程来获取数据。

附上过滤一些特殊字符的方法

 /// <summary>过y滤?SQL非¤?法¤¡§字Á?符¤?串ä?
        /// 
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string GetSafeSQL(string value)
        {
            if (string.IsNullOrEmpty(value))
                return string.Empty;
            value = Regex.Replace(value, @";", string.Empty);
            value = Regex.Replace(value, @"'", string.Empty);
            value = Regex.Replace(value, @"&", string.Empty);
            value = Regex.Replace(value, @"%20", string.Empty);
            value = Regex.Replace(value, @"--", string.Empty);
            value = Regex.Replace(value, @"==", string.Empty);
            value = Regex.Replace(value, @"<", string.Empty);
            value = Regex.Replace(value, @">", string.Empty);
            value = Regex.Replace(value, @"%", string.Empty);
            return value;
        }