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

开课吧开课吧锤锤2021-04-28 16:24

    如何实现分页和排序以产生良好的性能

    如何使用.NETCoreRazor页面,WebAPI和实体框架实现分页和排序以产生良好的性能。

    该项目的特点是:

    选择页面大小

    页面导航

    显示记录号

    列排序

    核心课程

    第一件事是定义用户可以要求应用程序获取的内容:

    页面大小

    页码

    字段排序

    排序方向

    代码如下所示:

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

    使用user参数和定义的结果,我们创建PageList<T>继承自的类,List<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)
        {
        }
    }

    API

    在WebAPI项目中,我们定义GetCustomers()将接受PageSortParam作为参数的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));
        }
    }

    剃刀页

    我们定义用户可以选择的页面大小:

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

    然后,我们[BindProperty(SupportsGet=true)]将值传递给HTML页面,然后又将值取回。对于每次旅行,我们都需要从HTML页面传递以下属性:

    PageSize–用户请求的页面大小

    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>

    就这样!希望您发现这对构建页面调度和排序应用程序很有用。

    以上就是开课吧广场小编为大家整理发布的“使用ASP .NET Core Razor页面,Web API和实体框架进行分页和排序”一文,更多人Web教程相关内容尽在开课吧广场Web教程频道!

有用
分享