SQL Server 2008 分页获取数据列表

最新推荐文章于 2019-07-26 00:13:19 发布

_风清扬

最新推荐文章于 2019-07-26 00:13:19 发布

阅读量939

点赞数

本文链接：https://blog.csdn.net/atceedsun/article/details/38735555

版权

 /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int pageIndex, int pageSize, out int recordCount)
        {
            int PageLowerBound, PageUpperBound;
            PageLowerBound = pageSize * (pageIndex - 1);
            PageUpperBound = PageLowerBound + pageSize;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TT.* FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.Id desc");
            }
            strSql.Append(")AS Row, T.*  from UserList T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT ");
            strSql.AppendFormat(" WHERE  TT.Row > {0} and TT.Row <= {1} ", PageLowerBound, PageUpperBound);
            recordCount = GetRecordCount(strWhere);
            return SqlHelper.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
   /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM UserList ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = SqlHelper.ExecuteScalar(CommandType.Text, strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }