EF 利用PagedList进行分页并结合查询 方法2
微软提供了PagedList分页，相信大家在网上也能搜索一大堆关于pagedList用法的博客，论坛。但是，在使用的过程中一不小心，就会掉入pagedList某种常规用法的陷阱。
我所说的某种常规用法是指如下方法(也可以参考我的博客：PagedList 分页用法)：
代码如下：
using System; using System.Collections.Generic; using System.Web; using System.Web.Mvc; using System.Linq; using EF_Test.DAL; using System.Data; using PagedList; namespace EF_Test.Controllers { public class HomeController : Controller { private StudentContext db = new StudentContext(); /// <summary> /// 简单分页演示 /// </summary> /// <param name="page">页码</param> /// <returns></returns> public ActionResult Index2(int page = 1)//查询所有学生数据 { return View(db.Students.OrderBy(item => item.Id).ToPagedList(page, 9)); } } }
前端HTML
@model PagedList.IPagedList<EF_Test.DAL.Student> @using PagedList.Mvc @{ ViewBag.Title = "Index"; Layout = "~/Views/Shared/_Layout.cshtml"; } @section css{ <link href="~/Content/PagedList.css" rel="stylesheet" /> <style type="text/css"> body { font-size: 12px; font-family: "微软雅黑"; color: #555; position: relative; background: #fff; } a { text-decoration: none; color: #555; } #tbList { border: 1px solid none; width: 800px; margin: 10px auto; border-collapse: collapse; } #tbList th, td { border: 1px solid #ccc; padding: 5px; text-align: center; } tfoot tr td { border: none; } </style> } @using (Html.BeginForm("Index", "Home", FormMethod.Get)) { <div style="text-align: center;"> <h1>Mvc分页例子</h1> <table id="tbList"> <tbody> @if (Model.Count() != 0) { <tr> <th>姓名 </th> <th>性别 </th> <th>学号 </th> </tr> foreach (var item in Model) { <tr style="text-align: center;"> <td> @Html.DisplayFor(modelItem => item.Name) </td> <td> @Html.DisplayFor(modelItem => item.Sex) </td> <td> @Html.DisplayFor(modelItem => item.StudentNum) </td> </tr> } } </tbody> <tfoot> <tr> <td colspan="5"> <div class=""> @if (Model != null) { <span style="height: 20px; line-height: 20px;">共 @Model.TotalItemCount.ToString() 条记录，当前第 @Model.PageNumber 页/共 @Model.PageCount 页 </span> @Html.PagedListPager(Model, page => Url.Action("Index", new { page }), new PagedListRenderOptions() { LinkToFirstPageFormat = "首页", LinkToNextPageFormat = "下一页", LinkToPreviousPageFormat = "上一页", LinkToLastPageFormat = "末页", DisplayItemSliceAndTotal = false, MaximumPageNumbersToDisplay = 3 }) } </div> </td> </tr> </tfoot> </table> </div> }
上述的用法很简单，直接查询所有数据，然后利用pagedList提供的HTML helper 进行分页。
其效果图也不错，如下：
上述中红色字体提到：该用法需要一次性查询表中所有数据，试问：如果您的数据表中有百万甚至千万条数据，那么这种用法效率是不是将会很低？
说来也惭愧，当初用的时候，我也想到了这个弊端，但是一直没去想办法解决这个问题。
还好，pagedList还提供了另外一种方法：StaticPagedList 方法
StaticPagedList 方法需要提供四个参数，分别为：数据源 当前页码 每页条数 以及总记录数
如上述所言，我们在查询的过程中不能一次性查询所有数据，因为这样做效率很低。而现在我们要做的就是查询当前页码的 10 条数据(假设每页展示十条数据)及返回数据表中的总记录数。
那么我们该怎么做呢？
方法其实很多，在我的项目中，我用到一个存储过程<不管你用什么，你现在要做的就是返回：当前页码的 10 条数据，及数据表总记录条数>
我用到的存储过程为：请参考我的上篇博客
有了存储过程，我们就要用EF执行这个存储过程，怎么执行呢？
接口层：
IEnumerable<StudentModel> GetPagePro(string tableName, string fields, string orderField, string sqlWhere, int pageSize, int pageIndex, out int totalPage, out int RecordCount);
执行层：继承接口
/// <summary> /// EF执行存储过程 /// </summary> /// <param name="tableName">表名</param> /// <param name="fields">所要查询的字段</param> /// <param name="orderField">排序字段</param> /// <param name="sqlWhere">条件语句 where</param> /// <param name="pageSize">页容量</param> /// <param name="pageIndex">页码</param> /// <param name="totalPage">out参数 总分页数量</param> /// <param name="RecordCount">out 参数 总记录数</param> /// <returns></returns> public IEnumerable<StudentModel> GetPagePro(string tableName, string fields, string orderField, string sqlWhere, int pageSize, int pageIndex, out int totalPage, out int RecordCount) { using (StudentEntities context = new StudentEntities()) { SqlParameter[] parameters = { new SqlParameter("@TableName", SqlDbType.NText), new SqlParameter("@Fields", SqlDbType.NText), new SqlParameter("@OrderField", SqlDbType.NText), new SqlParameter("@sqlWhere", SqlDbType.NText), new SqlParameter("@pageSize", SqlDbType.Int), new SqlParameter("@pageIndex", SqlDbType.Int), new SqlParameter("@TotalPage", SqlDbType.Int), new SqlParameter("@RecordCount", SqlDbType.Int) }; parameters[0].Value = tableName; parameters[1].Value = fields; parameters[2].Value = orderField; parameters[3].Value = sqlWhere; parameters[4].Value = pageSize; parameters[5].Value = pageIndex; parameters[6].Direction = ParameterDirection.Output; parameters[7].Direction = ParameterDirection.Output; var data = context.Database.SqlQuery<StudentModel>("exec [ZXL_GetPageData] @TableName,@Fields,@OrderField,@sqlWhere,@pageSize,@pageIndex,@TotalPage out,@RecordCount out", parameters).ToList(); int count = data.Count; // string n6 = parameters[6].Value.ToString(); string n7 = parameters[7].Value.ToString(); // totalPage = !string.IsNullOrEmpty(n6) ? int.Parse(n6) : 0; RecordCount = !string.IsNullOrEmpty(n7) ? int.Parse(n7) : 0; return data; } }
实体Model层：
public class StudentModel { public int Id { get; set; } public string StuNum { get; set; } public string deptNum { get; set; } public string StuName { get; set; } public string StuSex { get; set; } public Nullable<System.DateTime> AddTime { get; set; } }
控制器代码：
public ActionResult Index(int page=1)//查询所有学生数据 { int totalPage=0; int recordCount=0; var data = studentdb.GetPagePro("Student", "*", "Id", "", 10, page, out totalPage, out recordCount); var studentList = new StaticPagedList<StudentModel>(data,page,10,recordCount); return View(studentList);// }
UI/View层
@model PagedList.StaticPagedList<Test.Model.StudentModel> @using PagedList.Mvc @using PagedList @{ ViewBag.Title = "Index"; Layout = null; } <link href="~/Content/PagedList.css" rel="stylesheet" /> <style type="text/css"> body { font-size: 12px; font-family: "微软雅黑"; color: #555; position: relative; background: #fff; } a { text-decoration: none; color: #555; } #tbList { border: 1px solid none; width: 800px; margin: 10px auto; border-collapse: collapse; } #tbList th, td { border: 1px solid #ccc; padding: 5px; text-align: center; } tfoot tr td { border: none; } </style> @using (Html.BeginForm("Index", "Home", FormMethod.Get)) { <div style="text-align: center;"> <h1>Mvc分页例子</h1> <table id="tbList"> @* <thead> <tr> <th> <input id="StuName" name="StuName" type="text" placeholder="请输入姓名" /> </th> <th> <input id="StuNum" name="StuNum" type="text" placeholder="请输入学号" /> </th> <th> <input id="Submit1" type="submit" value="submit" /> </th> </tr> </thead>*@ <tbody> @if (Model.Count() != 0) { <tr> <th>姓名 </th> <th>性别 </th> <th>学号 </th> </tr> foreach (var item in Model) { <tr style="text-align: center;"> <td> @Html.DisplayFor(modelItem => item.StuName) </td> <td> @Html.DisplayFor(modelItem => item.StuSex) </td> <td> @Html.DisplayFor(modelItem => item.StuNum) </td> </tr> } } </tbody> <tfoot> <tr> <td colspan="5"> <div class=""> @if (Model != null) { <span style="height: 20px; line-height: 20px;">共 @Model.TotalItemCount.ToString() 条记录，当前第 @Model.PageNumber 页/共 @Model.PageCount 页 </span> @Html.PagedListPager(Model, page => Url.Action("Index", new { page }), new PagedListRenderOptions() { LinkToFirstPageFormat = "首页", LinkToNextPageFormat = "下一页", LinkToPreviousPageFormat = "上一页", LinkToLastPageFormat = "末页", DisplayItemSliceAndTotal = false, MaximumPageNumbersToDisplay = 3 }) } </div> </td> </tr> </tfoot> </table> </div> }
上述代码已经很齐全了，大家可以自行尝试，需要说明两点：
控制器代码：
view层HTML代码：
至此，整个pagedList分页就完毕了。
这样查询提升了效率。
我的分页效果图如下：
由图可知，我的数据表共有：151303条记录，如果采用每次都加载所有数据，效率是何其低可想而知。
呵呵，截止到这儿，pagedlist分页也就讲完了！
现在，我们提出新的要求：结合查询，根据学生姓名和学号进行模糊查询
其后端变更如下：
public ActionResult Index(int page = 1, string StuName = "", string StuNum = "",string sortOrder="")//查询所有学生数据 { string where = string.Empty; if (!string.IsNullOrEmpty(StuName)) { ViewBag.StuName = StuName; where += " and StuName like '%" + StuName + "%'"; } if (!string.IsNullOrEmpty(StuNum)) { ViewBag.StuNum = StuNum; where += " and StuNum like '%" + StuNum + "%'"; } int totalPage = 0; int recordCount = 0; var data = model.GetPagePro("Student", "*", "Id", " 1=1 " + where, 10, page, out totalPage, out recordCount); var studentList = new StaticPagedList<StudentModel>(data, page, 10, recordCount); return View(studentList);// }
前端如下：
@model PagedList.StaticPagedList<Test.Model.StudentModel> @using PagedList.Mvc @using PagedList @{ ViewBag.Title = "Index"; Layout = null; } <link href="~/Content/PagedList.css" rel="stylesheet" /> <style type="text/css"> body { font-size: 12px; font-family: "微软雅黑"; color: #555; position: relative; background: #fff; } a { text-decoration: none; color: #555; } #tbList { border: 1px solid none; width: 800px; margin: 10px auto; border-collapse: collapse; } #tbList th, td { border: 1px solid #ccc; padding: 5px; text-align: center; } tfoot tr td { border: none; } </style> @using (Html.BeginForm("Index", "Home", FormMethod.Get)) { <div style="text-align: center;"> <h1>Mvc分页例子</h1> <table id="tbList"> <thead> <tr> <th> <input id="StuName" name="StuName" type="text" placeholder="请输入姓名" value="@ViewBag.StuName" /> </th> <th> <input id="StuNum" name="StuNum" type="text" placeholder="请输入学号" value="@ViewBag.StuNum" /> </th> <th> <input id="Submit1" type="submit" value="submit" /> </th> </tr> </thead> <tbody> @if (Model.Count() != 0) { <tr> <th>姓名 </th> <th>性别 </th> <th>学号 </th> </tr> foreach (var item in Model) { <tr style="text-align: center;"> <td> @Html.DisplayFor(modelItem => item.StuName) </td> <td> @Html.DisplayFor(modelItem => item.StuSex) </td> <td> @Html.DisplayFor(modelItem => item.StuNum) </td> </tr> } } </tbody> <tfoot> <tr> <td colspan="5"> <div class=""> @if (Model != null) { <span style="height: 20px; line-height: 20px;">共 @Model.TotalItemCount.ToString() 条记录，当前第 @Model.PageNumber 页/共 @Model.PageCount 页 </span> @Html.PagedListPager(Model, page => Url.Action("Index", new { page,StuName=ViewBag.StuName,StuNum=ViewBag.StuNum }), new PagedListRenderOptions() { LinkToFirstPageFormat = "首页", LinkToNextPageFormat = "下一页", LinkToPreviousPageFormat = "上一页", LinkToLastPageFormat = "末页", DisplayItemSliceAndTotal = false, MaximumPageNumbersToDisplay = 3 }) } </div> </td> </tr> </tfoot> </table> </div> }
上图为变更处、
运行效果：
@陈卧龙的博客
