EF 之 MVC 排序，查询，分页 Sorting, Filtering, and Paging For MVC About EF

   最近悟出来一个道理，在这儿分享给大家：学历代表你的过去，能力代表你的现在，学习代表你的将来。

   十年河东十年河西，莫欺少年穷

   学无止境，精益求精

   上篇博客我们学习了EF CodeFirst增删改查之‘CRUD’,今儿，我们来探讨下MVC下的EF 排序、查询、分页操作

   在此，本人先从分页说起

   话说，做过webForm项目的程序员用AspNetPage.DLL做过分页，做过EasyUI框架的程序员，用JS AJAX请求分页，那么，MVC 程序员用什么进行分页呢？

   当然，MVC程序亦可以使用上述方式进行分页

   但是......

   原始的分页我们要写大量的代码，或者还需要使用存储过程，在此，小弟贴一个分页的存储过程<也是一个通用的分页存储过程>，贴出这段代码的目的只有一个：让大家做一个比较。

   下面，我仅仅贴出SQL端Proc的代码

GO
/****** 对象:  StoredProcedure [dbo].[ZXL_GetPageData]    脚本日期: 11/26/2016  ******/
SET ANSI_NULLS ON
GO 
 SET QUOTED_IDENTIFIER ON 
 GO



CREATE PROCEDURE [dbo].[ZXL_GetPageData]
(
    @TableName  varchar (500), --要进行分页的表，也可以用联接，如dbo.employee或dbo.employee INNER JOIN dbo.jobs ON (dbo.employee.job_id=dbo.jobs.job_id)
    @Fields  varchar(3000)='*', --表中的字段，可以使用*代替
    @OrderField  varchar(500), --要排序的字段
    @sqlWhere varchar(500)=NULL, --WHERE子句
    @pageSize int, --分页的大小
    @pageIndex int, --要显示的页的索引
    @TotalPage int output, --页的总数
    @RecordCount int output --总记录数
)
as

begin

    Begin Tran

    Declare @sql nvarchar(4000);
    Declare @totalRecord int; --记录总数

    if (@sqlWhere IS NULL or @sqlWhere = '') set @sql = 'select @totalRecord = count(*) from ' + @TableName 
    else
       
    
       set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
    --执行sql语句得到记录总数
    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT 
    select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
    select @RecordCount=CEILING(@totalRecord)
    --根据特定的排序字段为为行分配唯一ROW_NUMBER的顺序
    if (@sqlWhere IS NULL or @sqlWhere = '')
       set @sql = 'select * from (select ROW_NUMBER() over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
    else
       set @sql = 'select * from (select ROW_NUMBER() over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere
    --确保当前页的索引在合理的范围之内

    if @PageIndex<=0 
       Set @pageIndex = 1
    if @pageIndex>@TotalPage
       Set @pageIndex = @TotalPage
    --得到当前页在整个结果集中准确的ROW_NUMBER值

    Declare @StartRecord int
    Declare @EndRecord int
    set @StartRecord = (@pageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @pageSize - 1

    --输出当前页中的数据

    set @Sql = @Sql + ') as t' + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +   Convert(varchar(50),@EndRecord)

    Exec(@Sql)
    If @@Error <> 0

       Begin
           RollBack Tran
           Return -1
       End
    Else
       Begin
           Commit Tran
           Return @totalRecord
       End    
End

   今天，我们换个口味，来讲述下另外一种全新的分页

   首先，添加NuGet引用，搜索：pagedList

   

   然后，在我们的Controller中添加：using PagedList;

   如果我们成功添加了NuGet  PagedList引用，那么，在我们的Content文件夹中就会生成一个分页的CSS文件<嘻嘻，微软不愧是世界老大哥，技术领先别人一大截，我是越来越喜欢微软了>

   截止到此，准备工作也就基本做好了，下面我从后端和前端进行说明：

   后端代码如下：

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

   参数Page是从前端发送的，代表页码，数字9代表每页的数量，在分页的过程中，我们必须得使用OrderBy进行排序，否则程序会出错。

   前端代码如下：

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

   前端没什么好说的，大家自己测试，运行结果如下

   

   至此，MVC分页也就讲完了，下面我们来探讨排序的查询的问题

   好吧，由于小弟还没吃饭，就不作说明了，直接上代码：

   前端变更如下：

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

   说明几点：

   查询使用的是表单Get请求、

   多加了两个文本框和一个sumbit按钮，用作查询

   姓名和学号做成<A>标签，用作排序，例如：点击姓名，按照姓名升序排，再次点击，按照姓名降序排，同理点击学号

   后端代码如下：

       /// <summary>
       /// 查询 排序 分页
       /// </summary>
       /// <param name="sortOrder">排序字段 默认Id desc</param>
       /// <param name="stuName">搜索框 学生姓名 模糊查询</param>
       /// <param name="stuNum">搜索框 学生学号 精确查询</param>
       /// <param name="page">页码</param>
       /// <returns></returns>
        public ActionResult Index(string sortOrder, string stuName, string stuNum, int page = 1)//查询所有学生数据
        {
            //初始化排序-默认排序
            ViewBag.NameSortParm = "NameAsc";
            ViewBag.StumSortParm = "StumAsc";
            //根据click 更改排序
            if (!string.IsNullOrEmpty(sortOrder))
            {
                if (sortOrder.Contains("Name"))
                {
                    if (sortOrder.Equals("NameAsc"))
                    {
                        sortOrder = "NameDesc";
                        ViewBag.NameSortParm = sortOrder;
                    }
                    else
                    {
                        sortOrder = "NameAsc";
                        ViewBag.NameSortParm = sortOrder;
                    }
                }
                if (sortOrder.Contains("Stum"))
                {
                    if (sortOrder.Equals("StumAsc"))
                    {
                        sortOrder = "StumDesc";
                        ViewBag.StumSortParm = sortOrder;
                    }
                    else
                    {
                        sortOrder = "StumAsc";
                        ViewBag.StumSortParm = sortOrder;
                    }
                }
            }
            //}
            //查询全部数据
            var students = from s in db.Students
                           select s;
            //根据查询条件检索
            if (!string.IsNullOrEmpty(stuName))
            {
                //根据姓名模糊查询
                students = students.Where(s => s.Name.Contains(stuName));
            }
            if (!string.IsNullOrEmpty(stuNum))
            {
                //根据学号精确查询
                students = students.Where(s => s.StudentNum==stuName);
            }
            //排序处理
            switch (sortOrder)
            {
                case "NameDesc":
                    students = students.OrderByDescending(item=>item.Name);
                    break;
                case "NameAsc":
                    students = students.OrderBy(item => item.Name);
                    break;
                case "StumAsc":
                    students = students.OrderBy(s => s.StudentNum);
                    break;
                case "StumDesc":
                    students = students.OrderByDescending(s => s.StudentNum);
                    break;
                default:
                    students = students.OrderByDescending(s => s.Id);
                    break;
            }
            return View(students.ToPagedList(page, 9));//
        }

   运行结果：

   默认排序：ID 倒序排列

   

   点击姓名，按照姓名倒序排列，如下：

   再次点击姓名，按照姓名升序排列、

   同理，点击学号，不做演示

   输入姓名，进行模糊查询

   同理，输入学号进行精确查询，不做演示！

   但是这种方式有个最大的弊端，就是每次都必须查询所有数据，当数据表数据很多时，例如有100W条记录，那么这种方式相率会非常低下。解决方法请参考我的博客：http://www.cnblogs.com/chenwolong/p/6913915.html

   吃饭喽，