Sql 分页查询

?

1 2 3 4

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

服务端：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

/// <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; }    }

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

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));         }         } }

　　

客户端辅助类：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114

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;             }         }       } }