ASP.net Core 3.1 Razor页面分页控件不适用于dapper数据集 [英] ASP.net Core 3.1 Razor page Paging control not working with dapper dataset

问题描述

我是asp.net核心的新手，我试图在ASP.net core 3.1 Razor Page项目中使用寻呼机控件LazZiya.TagHelpers进行分页以显示新项目，由于某种原因，它显示了所有新闻项目(共20个) )在新闻页面上，但寻呼机控件似乎显示正确的数字

I am new to asp.net core and i am trying to use pager control LazZiya.TagHelpers in ASP.net core 3.1 Razor Page project for paging to display new items, for some reason it shows all the news items (total 20) on the news page but pager control seems to show correct numbers

似乎此分页控件仅适用于EF，不适用于Dapper，我试图使其与不起作用的dapper一起使用?以下是与Dapper相关的代码以及使用EF的工作代码.如果能与Dapper一起使用，我将不胜感激(由于我有限的知识，这似乎是不可能的)

Its seems this paging control only works with EF and not Dapper, i tried to make it work with dapper which didnt work?. Below is code related to Dapper as well as working code using EF. I would appreciate if i can make it work with Dapper also (which seems not to be possible with my limited knowledge)

出于测试目的，我将页面大小设置为3，因此它应该在页面上显示3个项目，而在页面上显示所有20个项目，

For testing purpose i set page size to 3 so it should show 3 items on page rather it shows all 20 items on page,

似乎寻呼机控件未绑定到列表eNewsList= await NewsService.GetNews(1);

It seems pager control is not binding to List eNewsList= await NewsService.GetNews(1);

     public class NewsModel : PageModel
{
    public IEnumerable<News> NewsList { get; set; }

    public INewsService NewsService { get; }

    public NewsModel(INewsService newsService)
    {
        NewsService = newsService ?? throw new ArgumentNullException(nameof(newsService));
    }

    [BindProperty]
    public IEnumerable<News> eNewsList { get; set; }

    [BindProperty]
    public int TotalRecords { get; set; }

    [BindProperty]
    public int PageNo { get; set; }

    [BindProperty]
    public int PageSize { get; set; }
    // public 

    public async Task OnGet( int p=1, int s=3)
    {

        eNewsList = await NewsService.GetNews(1);     
        TotalRecords = eNewsList.Count();
        eNewsList.Skip((p - 1) * s).Take(s);
        PageNo = p;
        PageSize = s;

        //NewsList.ToList();
    }


}

代码

<div class="container">
    <div class="row">
        @foreach (var item in Model.eNewsList)
        {
            <div class="col-xl-4 col-lg-4 col-md-6">
                <div class="card">
                    <img src="images/NewsImages/@item.NewsImage" class="card-img-top" alt="...">
                    <div class="card-body">
                        <h5 class="card-title">@item.NewsHeading</h5>
                        @if (item.NewsBrief.Length > 50)
                        {
                            <p class="card-text">@item.NewsBrief.Substring(0, 50)...</p>}
                        else
                        {
                            <p class="card-text">@item.NewsBrief</p>
                        }
                        <a href="/news/@item.NewsID/@item.NewsHeading.Replace(" ", "-").ToLower()" class="btn btn-primary">Read More</a>
                    </div>
                </div>
            </div>
        }

        <div class="row">
            <div class="col-12">
                <paging  total-records="Model.TotalRecords"
                        page-no="Model.PageNo"
                        page-size="Model.PageSize">
                </paging>
            </div>
        </div>

    </div>
</div>

Dapper代码

 // Get all News
        public async Task<List<News>> GetNews(int langID)
        {
            IEnumerable<News> newslist;
            using (var conn = new SqlConnection(_configuration.Value))
            {
                string query = "select * from dbo.News Where LanguageID ="+ langID;
                        query = query +" AND NewsActive =1 AND NewsVisible=1 order by NewsDate Desc";

                conn.Open();
                try
                {
                    newslist = await conn.QueryAsync<News>(query, commandType: CommandType.Text);

                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }

            }
            return newslist.ToList();
        }

我不确定如何将电子新闻列表绑定到寻呼机控件

I am not sure how to bind data eNewslist to pager control

我正在使用 http://www.ziyad.info/en /articles/21-Paging_TagHelper_ASP_NET_Core

以下代码在与EF结合使用时有效

使用EF的工作代码

<div class="container">
    <div class="row">
        @foreach (var item in Model.eNewsList)
        {
            <div class="col-xl-4 col-lg-4 col-md-6">
                <div class="card">
                    <img src="https://www.habtoor.com/images/NewsImages/@item.NewsImage" class="card-img-top" alt="...">
                    <div class="card-body">
                        <h5 class="card-title">@item.NewsHeading</h5>
                        @if (item.NewsBrief.Length > 50)
                        {
                            <p class="card-text">@item.NewsBrief.Substring(0, 50)...</p>}
                        else
                        {
                            <p class="card-text">@item.NewsBrief</p>
                        }
                        <a href="/news/@item.NewsID/@item.NewsHeading.Replace(" ", "-").ToLower()" class="btn btn-primary">Read More</a>
                    </div>
                </div>
            </div>
        }

        <div class="row">
            <div class="col-12">
                <paging total-records="Model.TotalRecords"
                        page-no="Model.P"
                        page-size="Model.S"
                        show-prev-next="true"
                        show-total-pages="false"
                        show-total-records="false"
                        show-page-size-nav="false"
                        show-gap="true"
                        show-first-numbered-page="true"
                        show-last-numbered-page="true"
                        gap-size="2"
                        max-displayed-pages="8"
                        query-string-key-page-no="p"
                        query-string-key-page-size="s"
                        query-string-value="@@(Request.QueryString.Value)"
                        page-size-nav-block-size="10"
                        page-size-nav-max-items="5"
                        page-size-nav-on-change="get"
                        page-size-nav-form-method="this.form.submit();"
                        class="row"
                        class-paging-control-div="col"
                        class-info-div="col"
                        class-page-size-div="col"
                        class-paging-control="pagination"
                        class-active-page="disabled"
                        class-disabled-jumping-button="disabled"
                        class-total-pages="badge badge-secondary"
                        class-total-records="badge badge-info"
                        text-page-size="Items per page:"
                        text-total-pages="pages"
                        text-total-records="records"
                        text-first="&laquo;"
                        text-last="&raquo;"
                        text-previous="&lsaquo;"
                        text-next="&rsaquo;"
                        sr-text-first="First"
                        sr-text-last="Last"
                        sr-text-previous="Previous"
                        sr-text-next="Next">
                </paging>
            </div>
        </div>

    </div>
</div>

后面的代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using BookListRazor.Data;
using BookListRazor.Model;
using Microsoft.EntityFrameworkCore;

namespace BookListRazor.Pages
{
    public class NewsModel : PageModel
    {
        // for EF
        private readonly ApplicationDbContext _db;

        public IEnumerable<News> NewsList { get; set; }

        public INewsService NewsService { get; }

        //constructor
        public NewsModel(INewsService newsService, ApplicationDbContext db)
        {
            //for EF
            _db = db;

            //For Dapper
            NewsService = newsService ?? throw new ArgumentNullException(nameof(newsService));
        }


        [BindProperty]
        public IEnumerable<News> eNewsList { get; set; }

        [BindProperty]
        public int TotalRecords { get; set; }

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

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

        // public 

        public async Task OnGet()
        {

            //code block for pager to work with EF only
            //pager doesnt work with dapper
            P = 1;
            S = 3;

            //var pageQS=1;
            if (!String.IsNullOrEmpty(HttpContext.Request.Query["p"]))
            {
                P = int.Parse(HttpContext.Request.Query["p"]);
            }

            var query = await _db.News.OrderByDescending(x => x.NewsDate).Where(x => x.LanguageID == 1 && x.NewsActive==true && x.NewsVisible==true).ToListAsync();
            TotalRecords = query.Count();

            //steps for paging
            eNewsList = await _db.News.OrderByDescending(x=>x.NewsDate).Where(x =>x.LanguageID==1).Skip((P - 1) * S).Take(S).ToListAsync();


            // Dapper without pagging
           // NewsList = await NewsService.GetNews(1);
        }


    }
}

推荐答案

以下是后端分页的简化实现.

Below is a simplified implementation for the backend paging.

public IEnumerable<News> eNewsList { get; set; }

public int TotalRecords { get; set; }

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

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

public async Task OnGetAsync()
{
    var query = await NewsService.GetNews(1);

    TotalRecords = query.Count();

    eNewsList = await NewsService.GetNews(1).Skip((P - 1) * S).Take(S).ToListAsync();
}

请同时考虑以下注意事项:

Please consider below notices as well:

由于我不知道什么是NewsService.GetNews(1)，所以我想它正在返回完整的记录集.

Since I don't know what is NewsService.GetNews(1) I suppose it is returning a full set of records.

不建议返回完整的记录集，因为它将消耗大量的内存，带宽和处理时间.我建议使用某种EF linq实现，以避免处理过度问题.例如:

Returning a full set of records is not recommended because it will consume a lot of memory, bandwidth and processing time. I recommend using kind of EF linq implementations to avoid over processing issues. e.g.:

var records = dbContext.Set<News>()
                       .AsNoTracking()
                       .Where(**some search filter**);

该实现将转换为在您调用.ToList()或.ToListAsync()之前不会执行的查询.这样，您可以确保只返回请求的记录数量.

Such implementation will be converted to a query that will not be executed till you call .ToList() or .ToListAsync(). This way you make sure you are rturning only requested amount of records.

当您调用.Count()时，它将仅返回记录计数，而不会获取整个数据集.

And when you call .Count() it will return only the records count without ferching the whole dataset.

如果使用任何类型的分页，在对项目进行分页之前先对记录进行排序非常重要，这样您就可以以相同的顺序获得下一页的记录，例如

If you are using any kind of paging it is highly important that you order the records before paging the items, so you get the next page of records with the same order e.g.

var records = await NewsSerive.GetNews(1)
                    .OrderBy(x => x.Name)
                    .Skip((P - 1)*S).Take(S)
                    .ToListAsync();

3-页面大小设置

关于 PagingTagHelper 的另一条注释，如果要将页面值更改为某些内容除了默认值以外，请使用演示页面中所述的[page-size-dropdown-items]属性. a>

3 - Page size settings

Another note regarding PagingTagHelper, if you want to change the page values to something other than the default values use [page-size-dropdown-items] attribute as described in the demo page

这篇关于ASP.net Core 3.1 Razor页面分页控件不适用于dapper数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！