日期:2014-05-17  浏览次数:21029 次

SQL分页、查找行记录兼C#ASP.NET分页方法
SQL语句:
select * from
(select ROW_NUMBER() over(order by @排序字段) as rowNum,显示的字段 from Expend where 条件) as tab where 
rowNum>=@记录 and rowNum<=@记录


C#代码:
         /// <summary>
        /// 获取分页列表
         /// </summary>
        /// <typeparam name="T">Domain类型</typeparam>
        /// <param name="selectView">查询视图(必填,非空)</param>
        /// <param name="field">查询字段</param>
        /// <param name="whereSQL">条件语句(不含where关键字)</param>
        /// <param name="orderSQL">排序语句(必填,非空)</param>
        /// <param name="start">开始记录数(从0开始,即不包含start这条记录)</param>
        /// <param name="limit">每页记录数</param>
        /// <returns></returns>
        public List<T> QueryPageList<T>(string selectView,string field, string whereSQL , string orderSQL, int start, int limit) where T : IDomainObject, new()
        {
            List<T> _ret = new List<T>();

            if (string.IsNullOrEmpty(field)) field = "*";
            if (string.IsNullOrEmpty(whereSQL)) whereSQL = " 1=1 ";
            whereSQL = whereSQL.Replace("where", "");

            string sql = "select * from(";
            sql += " select ROW_NUMBER() over(ORDER BY {0}) as RowNumber,{1} from {2} where {3}";
            sql += " ) as tab where RowNumber>{4} and RowNumber<={5}";
            sql = string.Format(sql, orderSQL, field, selectView, whereSQL, start, limit);

            _ret = QueryForList<T>(sql, null);

            return _ret;
        }