分——————页

分页存储过程

-- =============================================
-- Author:		THY
-- Create date: 2012-02-29
-- 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)

 

获取分页数据和数据的记录数

/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="fields">选择的字段</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, "office_guestbook");
			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 CalcCount(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());
        }

 aspx.cs代码

    protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                anp.RecordCount = new Huayu.Office.DAL.office_guestbook().CalcCount(Getcond());
                bindrep();
            }
        }

        private string Getcond()
        {
            string cond = "";
            return cond;
        }

        private void bindrep()
        {
            rep.DataSource = new Huayu.Office.DAL.office_guestbook().GetList("*", "id", "desc", anp.PageSize, anp.CurrentPageIndex, Getcond());
            rep.DataBind();
        }
        // 删除
        protected void Del(object sender, EventArgs e)
        {
            string id = (sender as LinkButton).CommandArgument;
            new Huayu.Office.DAL.office_guestbook().Delete(int.Parse(id));
            bindrep();

        }

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

 

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

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

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

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

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

<!--EndFragment-->