Asp.Net MVC分页PagedList

一、安装PagedList

mvc网站右键->管理nuget程序包->安装下边两个组件

二、单表查询分页

model

public  class admin
    {
        public int ID { get; set; }
        public string username { get; set; }
        public string password { get; set; }
        public string name { get; set; }
    }

 

controller

public ActionResult List(int page = 1)
        {
            adminBLL bll = new adminBLL();
            int pageNumber = page;
            int pageSize = 2;
            string sname = Request["sname"];//搜索

            var sql = bll.FindList();
            if (!string.IsNullOrEmpty(sname))
            {
                sql = sql.Where(m => m.name.Contains(sname));
            }
            var list = sql.OrderByDescending(m => m.ID);
            var listPage = list.ToPagedList(pageNumber, pageSize); 
            return View(listPage);
        }

 

view

@model PagedList.IPagedList<cms.Model.admin>
@using PagedList.Mvc;
<link href="/Content/PagedList.css" rel="stylesheet" type="text/css" />
<table class="table">
                        <tr><th>选择</th><th>姓名</th><th>用户名</th><th>操作</th></tr>
                        @foreach (var item in Model)
                        {
                            <tr>
                                <td>
                                    <input type="checkbox" value="@item.ID" class="ckID" />
                                </td>
                                <td>

                                    @Html.DisplayFor(modelItem => item.name)
                                </td>
                                <td>
                                    @Html.DisplayFor(modelItem => item.username)
                                </td>
                               
                                <td>
                                    @Html.ActionLink("编辑", "Edit", new { id = item.ID }, new { @class = "agreen" })
                                    @Html.ActionLink("删除", "Delete", new { id = item.ID }, new { @class = "ared", @onclick = "return confirm('您确认要删除该信息吗？')" })
                                </td>
                            </tr>
                        }
                    </table>
<div class="pagedList" style="margin:0 auto;text-align:center">
                        共 @Model.TotalItemCount 条，
                        每页 @Model.PageSize 条，
                        共 @Model.PageCount 页，
                        当前第 @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) 页


                        @Html.PagedListPager(Model, page => Url.Action("List", new { page, sname = Request["sname"] }), new PagedListRenderOptions() { LinkToFirstPageFormat = "首页", LinkToNextPageFormat = "下一页", LinkToPreviousPageFormat = "上一页", LinkToLastPageFormat = "末页", DisplayItemSliceAndTotal = false, MaximumPageNumbersToDisplay = 5 })
                    </div>

 

 三、多表查询分页

Model

public class news
    {
        public int ID { get; set; }
        public string title { get; set; }
        public int classID { get; set; }
    }
 public class newsClass
    {
        public int ID { get; set; }
        public string name { get; set; }
    }

ViewModel

public class newsList
    {
        public news data { get; set; }
        public string cname { get; set; }
    }

 

controller

public InewsBLL bllNews { get; set; }
public InewsClassBLL bllNewsClass { get; set; }
public ActionResult List(int page = 1)
        {
            int pageNumber = page;
            int pageSize = 2;

            string sname = Request["sname"];//搜索
            var sql = from n in bllNews.FindList()
                      join c in bllNewsClass.FindList() on n.classID equals c.ID
                      select new newsList() { data=n,cname=c.name };
            if (!string.IsNullOrEmpty(sname))
            {
                sql= sql.Where(m=>m.data.title.Contains(sname));
            }
            var list = sql.OrderByDescending(m => m.data.ID);
            var listPage = list.ToPagedList(pageNumber, pageSize);
            return View(listPage);
        }

view

@model PagedList.IPagedList<cms.Web.ViewModels.newsList>
@using PagedList.Mvc;
<link href="/Content/PagedList.css" rel="stylesheet" type="text/css" />
<table class="table">
                        <tr><th>选择</th><th>标题</th><th>分类</th><th>时间</th><th>操作</th></tr>
                        @foreach (var item in Model)
                        {
                            <tr>
                                <td>
                                    <input type="checkbox" value="@item.data.ID" class="ckID" />
                                </td>
                                <td>
                                    @item.data.title
                                </td>
                                <td>
                                    @item.cname
                                </td>
                                
                                <td>
                                    @Html.DisplayFor(modelItem => item.data.times)
                                </td>
                               
                                <td>
                                    @Html.ActionLink("编辑", "Edit", new { id = item.data.ID }, new { @class = "agreen" })
                                    @Html.ActionLink("删除", "Delete", new { id = item.data.ID }, new { @class = "ared", @onclick = "return confirm('您确认要删除该信息吗？')" })
                                </td>
                            </tr>
                        }
                    </table>
<div class="pagedList" style="margin:0 auto;text-align:center">
                        共 @Model.TotalItemCount 条，
                        每页 @Model.PageSize 条，
                        共 @Model.PageCount 页，
                        当前第 @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) 页


                        @Html.PagedListPager(Model, page => Url.Action("List", new { page, sname = Request["sname"] }), new PagedListRenderOptions() { LinkToFirstPageFormat = "首页", LinkToNextPageFormat = "下一页", LinkToPreviousPageFormat = "上一页", LinkToLastPageFormat = "末页", DisplayItemSliceAndTotal = false, MaximumPageNumbersToDisplay = 5 })
                    </div>

 

四、分析

使用MiniProfiler检测发现方法ToPagedList(pageNumber, pageSize)一共生成2条sql

第一条是统计总数，

第二条是获取分页数据，生成的sql是用top+row_number()分页的，例如访问第3页时，它sql只获取第3页的几条数据，请放心使用

注意：调用ToPagedList()方法前一定不要调用ToList()，不然查询的就是全表

 

五、优化

1、获取分页数据时需要什么字段就只获取什么字段

2、把视图中的分页html放到分部视图中，所有列表页直接调用，方便后期维护

分部视图：page.cshtml

@model PagedList.IPagedList
@using PagedList.Mvc;

<link href="/Content/PagedList.css" rel="stylesheet" type="text/css" />
<div class="pagedList" style="margin:0 auto;text-align:center">
    共 @Model.TotalItemCount 条，
    每页 @Model.PageSize 条，
    共 @Model.PageCount 页，
    当前第 @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) 页


    @Html.PagedListPager(Model, page => Url.Action("List", new { page, sname = Request["sname"] }), new PagedListRenderOptions() { LinkToFirstPageFormat = "首页", LinkToNextPageFormat = "下一页", LinkToPreviousPageFormat = "上一页", LinkToLastPageFormat = "末页", DisplayItemSliceAndTotal = false, MaximumPageNumbersToDisplay = 5 })

</div>

视图中调用

@Html.Partial("page", Model)