|
如何實(shí)現(xiàn)分頁(yè)和排序以產(chǎn)生良好的性能 如何使用.NETCoreRazor頁(yè)面，WebAPI和實(shí)體框架實(shí)現(xiàn)分頁(yè)和排序以產(chǎn)生良好的性能。 該項(xiàng)目的特點(diǎn)是： 選擇頁(yè)面大小 頁(yè)面導(dǎo)航 顯示記錄號(hào) 列排序 核心課程第一件事是定義用戶(hù)可以要求應(yīng)用程序獲取的內(nèi)容： 頁(yè)面大小 頁(yè)碼 字段排序 排序方向 代碼如下所示： 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
}1234567891011121314復(fù)制代碼類(lèi)型：[html]接下來(lái)，我們定義應(yīng)用程序應(yīng)返回的內(nèi)容，如下所示： 記錄總數(shù) 總頁(yè)數(shù) 前一頁(yè)編號(hào)-用戶(hù)導(dǎo)航到前一頁(yè)時(shí)的編號(hào) 下一頁(yè)編號(hào)-用于導(dǎo)航到下一頁(yè) 當(dāng)前頁(yè)面上的第一個(gè)記錄號(hào) 當(dāng)前頁(yè)面上的最后一條記錄號(hào) 代碼如下所示： 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; }
}123456789復(fù)制代碼類(lèi)型：[html]使用user參數(shù)和定義的結(jié)果，我們創(chuàng)建PageList<T>繼承自的類(lèi)，List<T>以便將結(jié)果存儲(chǔ)在中List。該類(lèi)將使用參數(shù)并找到結(jié)果。 下面顯示了代碼以及該GetData()方法所需的邏輯。從數(shù)據(jù)庫(kù)獲取記錄的行是對(duì)的調(diào)用ToListAsync()，它將跳過(guò)調(diào)用不需要的記錄，Skip()而僅通過(guò)調(diào)用獲取所需的記錄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
}
}123456789101112131415161718192021222324252627282930313233343536373839404142復(fù)制代碼類(lèi)型：[html]數(shù)據(jù)層的定義Customer在數(shù)據(jù)層項(xiàng)目中定義： 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; }
}12345678910復(fù)制代碼類(lèi)型：[html]我們將定義常規(guī)CRUD的接口和實(shí)現(xiàn)，唯一的區(qū)別是該Get()方法將使用PageList<T>該類(lèi)來(lái)僅獲取所需的記錄，從而通過(guò)將工作推送到數(shù)據(jù)庫(kù)來(lái)提高性能。下面是界面： 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);
}12345678復(fù)制代碼類(lèi)型：[html]并執(zhí)行： 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;
}
}123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657復(fù)制代碼類(lèi)型：[html]在DbContext從實(shí)體框架是簡(jiǎn)單的： public class StarterDbContext : DbContext
{
public DbSet<Customer> Customer { get; set; }
public StarterDbContext(DbContextOptions<StarterDbContext> options)
: base(options)
{
}
}123456789復(fù)制代碼類(lèi)型：[html]API 在WebAPI項(xiàng)目中，我們定義GetCustomers()將接受PageSortParam作為參數(shù)的Get()方法，在數(shù)據(jù)層中調(diào)用該方法，從PageSortResult響應(yīng)標(biāo)頭中添加來(lái)自的元數(shù)據(jù)（例如記錄總數(shù)，頁(yè)面總數(shù)等）。，并在響應(yīng)正文中提供實(shí)際記錄： [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));
}
}123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051復(fù)制代碼類(lèi)型：[html]剃刀頁(yè)我們定義用戶(hù)可以選擇的頁(yè)面大小： public IEnumerable<SelectListItem> PageSizeList { get; set; } =
new SelectList(new List<int> { 5, 10, 25, 50 });12復(fù)制代碼類(lèi)型：[html]然后，我們[BindProperty(SupportsGet=true)]將值傳遞給HTML頁(yè)面，然后又將值取回。對(duì)于每次旅行，我們都需要從HTML頁(yè)面?zhèn)鬟f以下屬性： PageSize–用戶(hù)請(qǐng)求的頁(yè)面大小 PageNumber–用戶(hù)當(dāng)前所在的頁(yè)碼 SortField–用戶(hù)要求進(jìn)行排序的列 SortDir–應(yīng)該排序的方向 SortDirNext–用戶(hù)單擊列鏈接時(shí)的下一個(gè)排序方向 [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; }123456789101112131415復(fù)制代碼類(lèi)型：[html]該OnGet()方法將獲取每個(gè)屬性的值，構(gòu)建參數(shù)并將其傳遞給API，然后顯示記錄并顯示有關(guān)頁(yè)面的元數(shù)據(jù)。以下是完整的代碼： 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());
}
}
}123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869復(fù)制代碼類(lèi)型：[html]html頁(yè)面將通過(guò)使用http提交表單get或單擊鏈接來(lái)接受用戶(hù)的輸入。請(qǐng)注意，參數(shù)是在每個(gè)動(dòng)作中傳遞的。在列標(biāo)題鏈接中僅指定排序列的名稱(chēng)和方向： <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>123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122復(fù)制代碼類(lèi)型：[html]就這樣！希望您發(fā)現(xiàn)這對(duì)構(gòu)建頁(yè)面調(diào)度和排序應(yīng)用程序很有用。 |
|
|
來(lái)自： 碼農(nóng)9527 > 《WEB》
