Sql 分页查询

SELECT * FROM 
(SELECT *,ROW_NUMBER() OVER (ORDER BY 会员编号 DESC) AS RowNumber FROM Mold_List
  ) T 
WHERE T.RowNumber BETWEEN @PageSize*(@StartPage-1)+1 AND  @PageSize*@StartPage 

服务端：

 /// <summary>
    /// MES的API 控制器基类
    /// </summary>
    [Route("api/mes/[controller]")]
    [ApiController]
    public abstract class MesApiControllerBase : Controller
    {
        public MesApiControllerBase()
        {
            this.ConnectionString= SqlHelper.ConnectionStringMES; 
        }
        /// <summary>
        /// MES 数据库连接字符串
        /// </summary>
        public string? ConnectionString { get; set; }
    }

　　

namespace LG.ERP.API.Controllers.MESControllers
{
    /// <summary>
    /// 模具
    /// </summary>
    public class MoldController : MesApiControllerBase
    {
        public MoldController() : base()
        {
        }

        [SwaggerOperation(Summary = "分页查询模具", Description = "分页查询模具", Tags = new string[2] { "MES","Mold" })]
        [HttpGet("{pageSize:int}/{startPage:int}")]
        public async Task<ActionResult<IEnumerable<Mold>>> Query(int pageSize, int startPage)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT * FROM ");
            sql.Append(" (SELECT *,ROW_NUMBER() OVER (ORDER BY MoldNo ) AS RowNumber FROM Mold_List ) T ");
            sql.Append(" WHERE T.RowNumber BETWEEN @PageSize*(@StartPage-1)+1 AND  @PageSize*@StartPage ");
            using var conn = new SqlConnection(ConnectionString);
            var result = await conn.QueryAsync<Mold>(sql.ToString(), new
            {
                pageSize,
                startPage
            });
            return Ok(result);
        }

        [SwaggerOperation(Summary = "模具总记录数量", Description = "模具总记录数量", Tags = new string[2] { "MES", "Mold" })]
        [HttpGet(nameof(Count))]
        public async Task<ActionResult<long>> Count()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT Count(1) FROM Mold_List ");
            using var conn = new SqlConnection(ConnectionString);
            var result = await conn.ExecuteScalarAsync<object>(sql.ToString());
            return Ok(Convert.ToInt64(result));
        }


    }
}

　　

客户端辅助类：

namespace MvcMovie.Utility
{
    /// <summary>
    /// 分页帮助类
    /// </summary>
    public class PageHelper
    {
        public PageHelper(int pageSize, int pageIndex)
        {
            this.PageSize = pageSize;
            this.PageIndex = pageIndex;
        }
        private long _count = 0;
        /// <summary>
        /// 总记录数
        /// </summary>
        public long Count
        {
            get
            {
                return _count;
            }
            set
            {
                _count = value;
                TotalPage = CalacTotalPage(value);
            }
        }
        /// <summary>
        /// 总页数
        /// </summary>
        public int TotalPage { get; private set; } = 1;
        /// <summary>
        /// 当前页
        /// </summary>
        public int PageIndex { get; set; } = 1;
        /// <summary>
        /// 页大小(一页显示的记录条数)
        /// </summary>
        public int PageSize { get; set; } = 10;
        /// <summary>
        /// 计算总页数
        /// </summary>
        /// <param name="count">总记录数</param>
        /// <returns></returns>
        public int CalacTotalPage(long count)
        {
            return (int)Math.Ceiling(count * 1.0 / PageSize);
        }
        /// <summary>
        /// 页开始记录位置
        /// </summary>
        public int StartIndex
        {
            get
            {
                return (int)(PageIndex - 1) * PageSize;
            }
        }
        /// <summary>
        /// 是否有上一页
        /// </summary>
        /// <returns></returns>
        public bool HasPrevious
        {
            get
            {
                return PageIndex > 1;
            }
        }
        /// <summary>
        /// 是否有下一页
        /// </summary>
        /// <returns></returns>
        public bool HasNext
        {
            get
            {
                return PageIndex < TotalPage;
            }
        }
        /// <summary>
        /// 上一页页码
        /// </summary>
        public int PreviousPageIndex
        {
            get
            {
                int index = PageIndex - 1;
                if (index > 0)
                {
                    return index;
                }
                return 1;
            }
        }
        /// <summary>
        /// 下一页页码
        /// </summary>
        public int NextPageIndex
        {
            get
            {
                int index = PageIndex + 1;
                if (index <= TotalPage)
                {
                    return index;
                }
                return TotalPage;
            }
        }

    }
}