欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

使用 ASP.NET Core Razor 页、Web API 和实体框架进行分页和排序

程序员文章站 2022-03-10 22:48:14
...

目录

核心类

数据层

The API

Razor页面

如何使用 .NET Core Razor 页、Web API 和实体框架实现分页和排序，以产生良好的性能。

该项目的特点是：

  • 选择页面大小(Page Size)
  • 页面导航
  • 显示记录编号
  • 列的排序

您可以在这里查看此项目的源和最新更新。

https://www.youtube.com/embed/FYeTPTMCW-k?feature=oembed

核心类

第一件事是定义用户可以要求应用程序获取什么：

  • 页面大小(Page Size)
  • 页号
  • 要排序的字段
  • 排序方向

代码如下所示：

public class PageSortParam
    {
        public int PageSize { get; set; } = 10;  //default page size
        public int CurrentPage { get; set; } = 1;

        public string SortField { get; set; } = null;
        public SortDirection SortDir { get; set; } 
    }

    public enum SortDirection
    {
        Ascending = 0,   //default as ascending
        Decending
    }

接下来，我们定义应用程序应返回的，如下所示：

  • 记录总数
  • 页数总数
  • 上一页编号 —— 用于用户导航到上一页时
  • 下一页编号 —— 用于导航到下一页
  • 当前页面上的第一个记录编号
  • 当前页面上的最后一个记录编号

代码如下所示：

public class PageSortResult
    {
        public int TotalCount { get; set; } = 0;
        public int TotalPages { get; set; } = 1;
        public int? PreviousPage { get; set; }
        public int? NextPage { get; set; }
        public int FirstRowOnPage { get; set; }
        public int LastRowOnPage { get; set; }
    }

通过定义用户参数和结果，我们创建从List<T>继承的PageList<T>类，以便我们可以将结果存储在List中。类将接受参数并查找结果。

下面的代码显示了GetData()方法中所需的逻辑。从数据库获取记录的行是对ToListAsync()的调用，它将通过调用Skip()跳过不需要的记录，并且仅通过调用Take()获取所需的记录：

public class PageList<T> : List<T>
    {
        public PageSortParam Param { get; }
        public PageSortResult Result { get; }

        public PageList(PageSortParam param)
        {
            Param = param;
            Result = new PageSortResult();
        }

        public async Task GetData(IQueryable<T> query)
        {
            //get the total count
            Result.TotalCount = await query.CountAsync();
            //find the number of pages
            Result.TotalPages = (int)Math.Ceiling(Result.TotalCount / (double)Param.PageSize);
            //find previous and next page number
            if (Param.CurrentPage - 1 > 0)
                Result.PreviousPage = Param.CurrentPage - 1;
            if (Param.CurrentPage + 1 <= Result.TotalPages)
                Result.NextPage = Param.CurrentPage + 1;
            //find first row and last row on the page
            if (Result.TotalCount == 0)  //if no record found
                Result.FirstRowOnPage = Result.LastRowOnPage = 0;
            else
            {
                Result.FirstRowOnPage = (Param.CurrentPage - 1) * Param.PageSize + 1;
                Result.LastRowOnPage = 
                  Math.Min(Param.CurrentPage * Param.PageSize, Result.TotalCount);
            }

            //if has sorting criteria
            if (Param.SortField != null)
                query = query.OrderBy(Param.SortField + 
                 (Param.SortDir == SortDirection.Ascending ? " ascending" : " descending"));

            List<T> list = await query.Skip((Param.CurrentPage - 1) * 
                           Param.PageSize).Take(Param.PageSize).ToListAsync();
            AddRange(list);  //add the list of items
        }
    }

数据层

在数据层项目中定义了Customer：

public class Customer
    {
        [Required]
        public int CustomerId { get; set; }

        [Required, StringLength(80)]
        public string FirstName { get; set; }
        [Required, StringLength(80)]
        public string LastName { get; set; }
    }

我们将定义通用的CRUD接口和实现，唯一的区别是该Get()方法将使用PageList<T>类来仅获取所需的记录，从而通过将工作推送到数据库来提高性能。下面是接口：

public interface ICustomerData
    {
        Task<PageList<Customer>> Get(PageSortParam pageSort);
        Task<Customer> GetCustomerById(int customerId);
        Task<Customer> Update(int customerId, Customer customer);
        Task<Customer> Add(Customer customer);
        Task<int> Delete(int customerId);
    }

和实现：

public class SqlCustomerData : ICustomerData
    {
        public StarterDbContext DbContext { get; }

        public SqlCustomerData(StarterDbContext dbContext)
        {
            DbContext = dbContext;
        }

        public async Task<Customer> Add(Customer customer)
        {
            DbContext.Add(customer);
            await DbContext.SaveChangesAsync();
            return customer;
        }

        public async Task<int> Delete(int customerId)
        {
            Customer c = await this.GetCustomerById(customerId);
            if (c != null)
            {
                this.DbContext.Remove(c);
                await DbContext.SaveChangesAsync();
                return customerId;
            }
            return -1;
        }

        public async Task<PageList<Customer>> Get(PageSortParam pageSortParam)
        {
            PageList<Customer> list = new PageList<Customer>(pageSortParam);
            
            await list.GetData(DbContext.Customer);
            return list;
        }

        public async Task<Customer> GetCustomerById(int customerId)
        {
            Customer c = await this.DbContext.Customer.FindAsync(customerId);
            if (c != null)
                return c;
            return null;
        }

        public async Task<Customer> Update(int customerId, Customer customer)
        {
            Customer c = await GetCustomerById(customerId);
            if (c != null)
            {
                c.FirstName = customer.FirstName;
                c.LastName = customer.LastName;
                await DbContext.SaveChangesAsync();
                return c;
            }
            return null;
        }
    }

实体框架的DbContext很简单

public class StarterDbContext : DbContext
    {
        public DbSet<Customer> Customer { get; set; }

        public StarterDbContext(DbContextOptions<StarterDbContext> options)
            : base(options)
        {
        }
    }

The API

在 Web API 项目中，我们定义将接受PageSortParam为参数的GetCustomers()方法，在数据层中调用该Get()方法，在响应标头的PageSortResult中添加元数据（如记录总数、总页数等），并在响应正文中提供实际记录：

[Route("api/[controller]")]
    [ApiController]
    public class CustomerController : ControllerBase
    {
        public ICustomerData CustomerData { get; }

        public CustomerController(ICustomerData customerData)
        {
            CustomerData = customerData;
        }

        // GET: api/Customer
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Customer>>> 
               GetCustomers([FromQuery] PageSortParam pageSortParam)
        {
            PageList<Customer> list = await this.CustomerData.Get(pageSortParam);
            //return result metadata in the header
            Response.Headers.Add("X-PageSortResult", JsonSerializer.Serialize(list.Result));
            return Ok(list);
        }

        // GET: api/Customer/5
        [HttpGet("{customerId}")]
        public async Task<ActionResult<Customer>> GetCustomer(int customerId)
        {
            return Ok(await this.CustomerData.GetCustomerById(customerId));
        }

        // PUT: api/Customer/5
        [HttpPut("{customerId}")]
        public async Task<ActionResult<Customer>> 
               PutCustomer(int customerId, Customer customer)
        {
            return Ok(await this.CustomerData.Update(customerId, customer));
        }

        // POST: api/Customer
        [HttpPost]
        public async Task<ActionResult<Customer>> PostCustomer(Customer customer)
        {
            return Ok(await this.CustomerData.Add(customer));
        }

        // DELETE: api/Customer/5
        [HttpDelete("{customerId}")]
        public async Task<ActionResult<int>> DeleteCustomer(int customerId)
        {
            return Ok(await this.CustomerData.Delete(customerId));
        }
    }

Razor页面

我们定义用户可以选择的页面大小(page size)：

public IEnumerable<SelectListItem> PageSizeList { get; set; } = 
                                   new SelectList(new List<int> { 5, 10, 25, 50 });

我们使用[BindProperty(SupportsGet=true)]将值传递给HTML页面，并获取该值。对于每次访问，我们需要从 HTML 页面传递和获取以下属性：

  • PageSize——用户请求的页面大小(page size)
  • PageNumber——用户当前的页号
  • SortField——用户请求排序的列
  • SortDir——它应该排序的方向
  • SortDirNext——用户单击列链接时的下一个排序方向 
[BindProperty(SupportsGet = true)]
        public int? PageSize { get; set; }

        [BindProperty(SupportsGet = true)]
        public int PageNumber { get; set; } = 1;

        [BindProperty(SupportsGet = true)]
        public string SortField { get; set; }  

        [BindProperty(SupportsGet = true)]
        public SortDirection SortDir { get; set; }

        //for the next sort direction when the user clicks on the header
        [BindProperty(SupportsGet = true)]
        public SortDirection? SortDirNext { get; set; }

该OnGet()方法将获取每个属性的值，构建参数并将其传递给API，然后显示记录并显示有关页面的元数据。以下是完整的代码：

public class ListModel : PageModel
    {
        public IEnumerable<Dto.Customer> CustomerList { get; set; }
        private readonly IConfiguration config;

        public IEnumerable<SelectListItem> 
           PageSizeList { get; set; } = new SelectList(new List<int> { 5, 10, 25, 50 });

        public PageSortParam PageSortParam { get; set; } = new PageSortParam();
        public PageSortResult PageSortResult { get; set; }

        [BindProperty(SupportsGet = true)]
        public int? PageSize { get; set; }

        [BindProperty(SupportsGet = true)]
        public int PageNumber { get; set; } = 1;

        [BindProperty(SupportsGet = true)]
        public string SortField { get; set; }  

        [BindProperty(SupportsGet = true)]
        public SortDirection SortDir { get; set; }

        //for the next sort direction when the user clicks on the header
        [BindProperty(SupportsGet = true)]
        public SortDirection? SortDirNext { get; set; }

        public ListModel(IConfiguration config)
        {
            this.config = config;
        }

        public async Task OnGet()
        {
            if (PageSize.HasValue)
                PageSortParam.PageSize = (int)PageSize;

            PageSortParam.CurrentPage = PageNumber;

            //if never sorted
            if (SortField == null)
                SortDir = new SortDirection();
            else if (SortDirNext != null)  //if requested new sort direction
                SortDir = (SortDirection)SortDirNext;

            //SortDirNext will be the reverse of SortDir
            SortDirNext = SortDir == SortDirection.Ascending ? 
                          SortDirection.Decending : SortDirection.Ascending;
            
            PageSortParam.SortField = SortField;
            PageSortParam.SortDir = SortDir;
            
            HttpResponseMessage response = await new HttpClient().GetAsync
              (this.config["APIurl"] + "Customer?PageSize=" + PageSortParam.PageSize
                                     + "&CurrentPage=" + PageSortParam.CurrentPage
                                     + "&SortField=" + PageSortParam.SortField
                                     + "&SortDir=" + PageSortParam.SortDir);
            //display the list of customers
            if (response.IsSuccessStatusCode)
                CustomerList = await response.Content.ReadAsAsync<IEnumerable<Dto.Customer>>();
            //get the paging meta data from the header
            IEnumerable<string> headerValue;
            if (response.Headers.TryGetValues("X-PageSortResult", out headerValue))
            {
                PageSortResult = JsonConvert.DeserializeObject<PageSortResult>
                                 (headerValue.First());
            }
        }
    }

html页面将通过使用http get提交表单或单击链接来接受用户的输入。请注意，参数是在每个动作中传递的。在列标题链接中仅指定排序列的名称和方向：

<div>
    <div>
        <table class="table table-bordered table-hover table-sm w-auto">
            <caption>Items @Model.PageSortResult.FirstRowOnPage 
                     to @Model.PageSortResult.LastRowOnPage</caption>
            <thead class="thead-light">
                <tr>
                    <th scope="col">
                        <a asp-page="./Edit" asp-route-customerId="0">
                            <i class="material-icons icon">add_box</i>
                        </a>
                    </th>
                    <th scope="colgroup" colspan="4" class="text-right">
                        <form method="get">
                            Page Size:
                            @Html.DropDownListFor(m => m.PageSize, Model.PageSizeList, 
                                  "-Select-", new { onchange = "submit()" })
                            <input type="hidden" name="PageNumber" value="1" /> 
                            <input type="hidden" name="SortField" value="@Model.SortField" />
                            <input type="hidden" name="SortDir" value="@Model.SortDir" />
                        </form>
                    </th>
                </tr>
                <tr>
                    <th scope="col" class="pl-2 pr-2">
                        <a asp-page="./List"
                           asp-route-SortField="CustomerId"
                           asp-route-SortDir="@Model.SortDir"
                           asp-route-SortDirNext="@Model.SortDirNext"
                           asp-route-PageSize="@Model.PageSize"
                           asp-route-PageNumber="@Model.PageNumber">
                            Customer ID
                        </a>
                    </th>
                    <th scope="col" class="pl-2 pr-2">
                        <a asp-page="./List"
                           asp-route-SortField="FirstName"
                           asp-route-SortDir="@Model.SortDir"
                           asp-route-SortDirNext="@Model.SortDirNext"
                           asp-route-PageSize="@Model.PageSize"
                           asp-route-PageNumber="@Model.PageNumber">
                            First Name
                        </a>
                    </th>
                    <th scope="col" class="pl-2 pr-2">
                        <a asp-page="./List"
                           asp-route-SortField="LastName"
                           asp-route-SortDir="@Model.SortDir"
                           asp-route-SortDirNext="@Model.SortDirNext"
                           asp-route-PageSize="@Model.PageSize"
                           asp-route-PageNumber="@Model.PageNumber">
                            Last Name
                        </a>
                    </th>
                    <th scope="col"></th>
                    <th scope="col"></th>
                </tr>
            </thead>
            <tbody>
                @foreach (var c in Model.CustomerList)
                {
                    <tr>
                        <td class="pl-2 pr-2">@c.CustomerId</td>
                        <td class="pl-2 pr-2">@c.FirstName</td>
                        <td class="pl-2 pr-2">@c.LastName</td>
                        <td class="td-center pl-2 pr-2">
                            <a asp-page="./Edit" asp-route-customerId="@c.CustomerId">
                                <i class="material-icons icon">edit</i>
                            </a>
                        </td>
                        <td class="td-center pl-2 pr-2">
                            <a asp-page="./Delete" asp-route-customerId="@c.CustomerId">
                                <i class="material-icons icon">delete</i>
                            </a>
                        </td>
                    </tr>
                }
            </tbody>
        </table>
    </div>
</div>
<div>
    @{
        var prev = Model.PageSortResult.PreviousPage.HasValue ? "" : "disabled";
        var next = Model.PageSortResult.NextPage.HasValue ? "" : "disabled";
        var first = Model.PageNumber != 1 ? "" : "disabled";
        var last = Model.PageNumber != Model.PageSortResult.TotalPages ? "" : "disabled";
    }
</div>
<a asp-page="./List"
   asp-route-pageNumber="1"
   asp-route-PageSize="@Model.PageSize"
   asp-route-SortField="@Model.SortField"
   asp-route-SortDir="@Model.SortDir"
   class="btn @first">
    <i class="material-icons icon">first_page</i>
</a>
<a asp-page="./List"
   asp-route-pageNumber="@Model.PageSortResult.PreviousPage" 
   asp-route-PageSize="@Model.PageSize"
   asp-route-SortField="@Model.SortField"
   asp-route-SortDir="@Model.SortDir"
   class="btn @prev">
    <i class="material-icons icon">chevron_left</i>
</a>
Page @Model.PageNumber of @Model.PageSortResult.TotalPages
<a asp-page="./List"
   asp-route-pageNumber="@Model.PageSortResult.NextPage"
   asp-route-PageSize="@Model.PageSize"
   asp-route-SortField="@Model.SortField"
   asp-route-SortDir="@Model.SortDir"
   class="btn @next">
    <i class="material-icons icon">chevron_right</i>
</a>
<a asp-page="./List"
   asp-route-pageNumber="@Model.PageSortResult.TotalPages"
   asp-route-PageSize="@Model.PageSize"
   asp-route-SortField="@Model.SortField"
   asp-route-SortDir="@Model.SortDir"
   class="btn @last">
    <i class="material-icons icon">last_page</i>
</a>

就这些！希望您发现这在构建分页和排序应用程序时很有用。

https://www.codeproject.com/Articles/5292061/Paging-and-Sorting-using-ASP-NET-Core-Razor-Page-W

相关标签： ASP.NET CORE ASP.NET Core Razor Page Web API EF 分页

上一篇： 使用 ASP.NET Core 创建 Web API教程遇到的问题

下一篇： 剧本杀行业报告：线上与线下热度相当，资本介入引领行业头部形成

推荐阅读