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="fileds">选择的字段</param>
        /// <param name="order">排序字段</param>
        /// <param name="ordertype">排序类型desc或者asc</param>
        /// <param name="PageSize">页大小</param>
        /// <param name="PageIndex">第几页（索引）</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
      
        public DataSet GetList(string fileds,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_link");
//表名
            db.AddInParameter(dbCommand, "strFields", DbType.AnsiString, fileds);
            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  datacount(string strwhere)
        {
            string sql = "select count(1) from shop_link ";
            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控件并设置显示的样式

工具箱里 选择项 添加

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

 

anp1.RecordCount = new xh.shop.DAL.link().datacount(getcound());
// RecordCount 获取总记录数
GridView1.DataSource = new xh.shop.DAL.link().GetList("*", "creatdate", "desc",anp1.PageSize,anp1.CurrentPageIndex,getcound());
            GridView1.DataBind();
//设置anp1.PageSize  anp1.CurrentPageIndex

 

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

直接绑定数据就可以了