基于mvc实现大数据量分页

UserInfo.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MvcDemo.Models
{
    /// <summary>
    /// 用户实体
    /// </summary>
    public class UserInfo
    {
        public string Id { get; set; }
        public string UserName { get; set; }
        public string Nation { get; set; }
        public string TrueName { get; set; }
        public DateTime Birthday { get; set; }
        public string LocalAddress { get; set; }
        public int Gender { get; set; }
    }
    /// <summary>
    /// 分页对象
    /// </summary>
    public class UserPage
    {
        public List<UserInfo> user { get; set; }
        public int TotalCount{get;set;}
    }
}

HomeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcProcPage.Service;
using MvcProcPage.Models;
using PagedList.Mvc;
using PagedList;

namespace MvcDemo.Controllers
{
    public class HomeController : Controller
    {
        //UserService ss = new UserService();
        //private List<UserInfo> list=new List<UserInfo>();

        IUserService service = new UserService();

        public ActionResult Index(int page=1)
        {
            //#region 插入10W条数据
            //for (int i = 1; i <= 100000; i++)
            //{
            //    list.Add(
            //        new UserInfo
            //        {
            //            Id = Guid.NewGuid().ToString(),
            //            UserName = "xiaoming" + i,
            //            Birthday = Convert.ToDateTime("1990-12-11"),
            //            Gender = 1,
            //            LocalAddress = "江苏省",
            //            TrueName = "小明" + i,
            //            Nation = "汉族"
            //        });
            //}
            //ss.InsertAll(list);
            //return View();
            //#endregion
            var pagelist = service.GetAllList().ToPagedList(page,10);
            return View(pagelist);
        }

        public ActionResult ProcPageIndex(int page=1)
        {
             var list = service.GetPageByProcList(page,5);
            return View();
        }

        public JsonResult GetProList(int page = 1, int pagesize = 10)
        {
            var model = service.GetPageByProcList(page, pagesize);
            return Json(model, JsonRequestBehavior.AllowGet);
        }

        public ActionResult TestLog()
        {
            int result = 0;
            int x = 1, y = 0;
            result = x / y;
            return View();
        }
    }
}

Service文件夹

IUserService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MvcProcPage.Models;
namespace MvcDemo.Service
{
    interface IUserService
    {
        /// <summary>
        /// 查询所有用户
        /// </summary>
        /// <returns></returns>
        List<UserInfo> GetAllList();
        /// <summary>
        /// 采用存储过程分页
        /// </summary>
        /// <param name="page"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        UserPage GetPageByProcList(int page = 1, int pageSize = 10);
        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        UserInfo GetUserById(int id);
        /// <summary>
        /// 新增单个
        /// </summary>
        /// <param name="model"></param>
        void InsertSingle(UserInfo model);
        /// <summary>
        /// 批量新增
        /// </summary>
        /// <param name="list"></param>
        void InsertAll(List<UserInfo> list);
        /// <summary>
        /// 删除单个
        /// </summary>
        /// <param name="model"></param>
        void DeleteSingle(UserInfo model);
        /// <summary>
        /// 删除所有
        /// </summary>
        void DeleteAll();
        /// <summary>
        /// 修改单个
        /// </summary>
        /// <param name="model"></param>
        void UpdateSingle(UserInfo model);
    }
}

UserService.cs

using System.Collections.Generic;
using MvcProcPage.Models;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Dapper;
using DapperExtensions;
namespace MvcDemo.Service
{
    public class UserService:IUserService
    {
        public static string constr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        IDbConnection conn = new SqlConnection(constr);
        /// <summary>
        /// 查询所有用户
        /// </summary>
        /// <returns></returns>
        public List<UserInfo> GetAllList()
        {
            var list = new List<UserInfo>();
            //string sql = @"select Id,UserName,Nation,TrueName,Birthday,LocalAddressGender from UserInfo";
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                //标准写法
                //list = conn.Query<UserInfo>(sql,commandType: CommandType.Text).AsList();
                //dapper扩展写法
                list = conn.GetList<UserInfo>().AsList();
                conn.Close();
            }
            return list;
        }


        /// <summary>
        /// 采用存储过程分页
        /// </summary>
        /// <param name="page"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public UserPage GetPageByProcList(int page=1,int pageSize=10)
        {
            UserPage model = new UserPage();
            var list = new List<UserInfo>();
            //string sql = @"select Id,UserName,Nation,TrueName,Birthday,LocalAddressGender from UserInfo";
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                DynamicParameters parm = new DynamicParameters();
                parm.Add("viewName", "UserInfo");
                parm.Add("fieldName", "*");
                parm.Add("keyName", "Id");
                parm.Add("pageSize", pageSize);
                parm.Add("pageNo", page);
                parm.Add("orderString", "Id");
                parm.Add("recordTotal", 0, DbType.Int32, ParameterDirection.Output);
                //参数名得和存储过程的变量名相同（参数可以跳跃传，键值对方式即可）
                //强类型
                //list = conn.Query<UserInfo>("P_GridViewPager", new { viewName = "Edu_StudentSelectedCourse", fieldName = "*", keyName = "Id", pageSize = 20, pageNo = 1, orderString = "id" }, commandType: CommandType.StoredProcedure).ToList();
                //标准写法
                //list = conn.Query<UserInfo>(sql,commandType: CommandType.Text).AsList();
                //dapper扩展写法
                //list = conn.GetList<UserInfo>().AsList();
                list = conn.Query<UserInfo>("ProcViewPager", parm, commandType: CommandType.StoredProcedure).AsList();
                int totalCount = parm.Get<int>("@recordTotal");//返回总页数
                model.user = list;
                model.TotalCount = totalCount;
                conn.Close();
            }
            return model;
        }
        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserInfo GetUserById(int id)
        {
            UserInfo model = new UserInfo();
            string sql = @"select Id,UserName,Nation,TrueName,Birthday,LocalAddressGender from UserInfo where Id=@id";
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                //参数名得和存储过程的变量名相同（参数可以跳跃传，键值对方式即可）
                //动态类型
                //var list = conn.Query("usp_test", new { aId = 11 }, commandType: CommandType.StoredProcedure);
                //强类型
                //list = conn.Query<UserInfo>("P_GridViewPager", new { viewName = "Edu_StudentSelectedCourse", fieldName = "*", keyName = "Id", pageSize = 20, pageNo = 1, orderString = "id" }, commandType: CommandType.StoredProcedure).ToList();

                //model = conn.QueryFirst<UserInfo>(sql, commandType: CommandType.Text);
                model = conn.Get<UserInfo>(id);
                conn.Close();
            }
            return model;
        }
        /// <summary>
        /// 新增单个
        /// </summary>
        /// <param name="model"></param>
        public void InsertSingle(UserInfo model)
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                conn.Insert<UserInfo>(model);
            }
        }
        /// <summary>
        /// 批量新增
        /// </summary>
        /// <param name="list"></param>
        public void InsertAll(List<UserInfo> list)
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                conn.Insert<UserInfo>(list);           
            }
        }
        /// <summary>
        /// 删除单个
        /// </summary>
        /// <param name="model"></param>
        public void DeleteSingle(UserInfo model)
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                conn.Delete<UserInfo>(model);
            }
        }

        /// <summary>
        /// 删除所有
        /// </summary>
        public void DeleteAll()
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                conn.Delete<UserInfo>(conn.GetList<UserInfo>());
            }
        }
        /// <summary>
        /// 修改单个
        /// </summary>
        /// <param name="model"></param>
        public void UpdateSingle(UserInfo model)
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                conn.Update<UserInfo>(model);
            }
        }

    }
}

Index.cshtml

@{
    Layout = null;    
}
@using PagedList.Mvc;
@using MvcDemo.Models;
@model PagedList.IPagedList<UserInfo>
<link href="~/Content/bootstrap.css" rel="stylesheet" />
<link href="~/Content/PagedList.css" rel="stylesheet" />
<div class="well">
    <table class="table">
        <thead>
            <tr>
                <th>用户名</th>
                <th>真实姓名</th>              
                <th>出生日期</th>
                <th>地址</th>
            </tr>
        </thead>
        <tbody>
            @if (Model != null && Model.Any())
            {
                foreach (var item in Model)
                {
                    <tr>
                        <td>@item.UserName </td>
                        <td>@item.TrueName </td>
                        <td>@item.Birthday </td>
                        <td>@item.LocalAddress </td>
                    </tr>

                }

            }
        </tbody>

    </table>

</div>
<div class="pagination">
    @Html.PagedListPager(Model, page => Url.Action("Index", new { page }))
</div>
<script src="~/Scripts/bootstrap.js"></script>

ProcPageIndex.cshtml

@{
    Layout = null;
}
<link href="~/Content/bootstrap.css" rel="stylesheet" />
<link href="~/Content/PagedList.css" rel="stylesheet" />
<link href="~/Scripts/pagination.css" rel="stylesheet" />
<div class="well">
    <table class="table">
        <thead>
            <tr>
                <th>用户名</th>
                <th>真实姓名</th>
                <th>出生日期</th>
                <th>地址</th>
            </tr>
        </thead>
        <tbody id="tbodylist"></tbody>

    </table>

</div>
<div id="Pagination" class="pagination">

</div>
<script src="~/Scripts/jquery-1.9.1.min.js"></script>
<script src="~/Scripts/jquery.pagination.js"></script>
<script src="~/Scripts/bootstrap.js"></script>
<script type="text/javascript">
    //分页查询开始
    $(document).ready(function () {
        getDataList(0, null);
    });

    var pagesize = 10;
    var page = 1;
    var initFlag = true;

    function getDataList(currPage, jg) {

        $.ajax({
            url: "/Home/GetProList",
            type: "get",
            dataType: 'json',
            data: { pagesize: pagesize, page: currPage + 1 },
            contentType: "application/x-www-form-urlencoded; charset=utf-8",
            success: function (response) {            
                if (response.user != null && response.user != "" && response.TotalCount != undefined && response.TotalCount > 0) {
                    if (initFlag) {
                        $("#Pagination").pagination(
                                response.TotalCount,
                                {
                                    items_per_page: pagesize,
                                    num_edge_entries: 1,
                                    num_display_entries: 8,
                                    callback: getDataList//回调函数
                                });
                        initFlag = false;
                    }
                    $("#tbodylist").html("");
                    loadDataList(response.user);
                } else {

                }

            }
        });
    }

    function loadDataList(listdata) {    
        var tbody = "";
        $(listdata).each(function (i, n) { 
            //表格
            tbody += "<tr>" +
                            "<td>" + n.UserName + "</td>" +
                            "<td>" + n.TrueName + "</td>" +
                            "<td>" + formatDate(n.Birthday) + "</td>" +
                            "<td>" + n.LocalAddress + "</td>" +
                       "</tr>";          
        });

        $("#tbodylist").html(tbody);

    }
    //格式日期
    function formatDate(NewDtime) 
    {
        var dt = new Date(parseInt(NewDtime.slice(6, 19)));
        var year = dt.getFullYear();
        var month = dt.getMonth() + 1;
        var date = dt.getDate();
        var hour = dt.getHours();
        var minute = dt.getMinutes();
        var second = dt.getSeconds();
        return year + "-" + month + "-" + date ;
    }
</script>

TestLog.cshtml

@{
    Layout = null;
}

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>TestLog</title>
</head>
<body>
    <div> 
    </div>
</body>
</html>

FilterConfig.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace MvcDemo
{
    public class FilterConfig
    {
        public static void RegisterGlobalFilters(GlobalFilterCollection filters)
        {
            //filters.Add(new HandleErrorAttribute()); //注释掉系统默认的
            filters.Add(new MyErrorAttribute()); //添加我刚才自定义的
        }
    }
}

MyErrorAttribute.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace MvcDemo
{
    public class MyErrorAttribute:HandleErrorAttribute
    {
        public static Queue<Exception> ExceptionQueue = new Queue<Exception>();
        public override void OnException(ExceptionContext filterContext)
        {
            ExceptionQueue.Enqueue(filterContext.Exception);//加入异常队列
            //出现异常的时候可以跳转到异常处理的页面
            base.OnException(filterContext);
        }
    }
}

Global.asax

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;
using System.Threading;
using log4net;
namespace MvcDemo
{
    public class MvcApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            //log4初始化
            log4net.Config.XmlConfigurator.Configure();            
            AreaRegistration.RegisterAllAreas();      
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            ThreadPool.QueueUserWorkItem(o =>
            {
                while (true)
                {
                    if (MyErrorAttribute.ExceptionQueue.Count > 0)
                    {
                        Exception ex = MyErrorAttribute.ExceptionQueue.Dequeue();
                        if (ex != null)
                        {
                            ILog logger = LogManager.GetLogger("testError");
                            logger.Error(ex.ToString());//将异常信息写入log4
                        }
                        else
                        {
                            Thread.Sleep(50);
                        }
                    }
                    else
                    {
                        Thread.Sleep(50);
                    }
                }
            });
        }
    }
}

Web.config

<?xml version="1.0" encoding="utf-8"?>

<configuration>
    <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
  </configSections>
<log4net>
    <!-- OFF, FATAL, ERROR, WARN, INFO, DEBUG, ALL -->
    <!-- Set root logger level to ERROR and its appenders -->
    <root>
      <level value="ALL"/>
      <appender-ref ref="SysAppender"/>
    </root>

    <!-- Print only messages of level DEBUG or above in the packages -->
    <logger name="WebLogger">
      <!--这里进一步限制了日志级别，只有在大于等于DEBUG情况下才会记录日志-->
      <!--<level value="DEBUG"/>-->
    <level value="DEBUG"/>
    </logger>
    <!--指定日志记录的方式：以滚动文件的方式-->
    <appender name="SysAppender" type="log4net.Appender.RollingFileAppender,log4net" >
      <!--指定日志存放的路径，这里放置到App_Data目录是为了安全-->
      <param name="File" value="App_Data/" />
      <!--日志以追加的形式记录-->
      <param name="AppendToFile" value="true" />
      <param name="RollingStyle" value="Date" />
      <!--设置日志文件名称的生成规则-->
      <param name="DatePattern" value="&quot;Logs_&quot;yyyyMMdd&quot;.txt&quot;" />
      <!--日志名称是否静态：否-->
      <param name="StaticLogFileName" value="false" />
      <!--日志内容格式和布局设置-->
      <layout type="log4net.Layout.PatternLayout,log4net">
        <param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" />
        <param name="Header" value="----------------------header-------------------------" />
        <param name="Footer" value="----------------------footer--------------------------" />
      </layout>
    </appender>
    <appender name="consoleApp" type="log4net.Appender.ConsoleAppender,log4net">
      <layout type="log4net.Layout.PatternLayout,log4net">
        <param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" />
      </layout>
    </appender>
  </log4net>
  <appSettings>
    <add key="webpages:Version" value="3.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
  </appSettings>
  <connectionStrings>
    <add name="connStr" connectionString="Data Source=WU-PC;database=MvcPageDB;uid=sa;pwd=123456;" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.5.2" />
    <httpRuntime targetFramework="4.5.2" />
    <httpModules>
      <add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule, Microsoft.AI.Web" />
    </httpModules>
  </system.web>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-5.2.3.0" newVersion="5.2.3.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <system.codedom>
    <compilers>
      <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:6 /nowarn:1659;1699;1701" />
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:14 /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+" />
    </compilers>
  </system.codedom>
  <system.webServer>
    <validation validateIntegratedModeConfiguration="false" />
    <modules>
      <remove name="ApplicationInsightsWebTracking" />
      <add name="ApplicationInsightsWebTracking" type="Microsoft.ApplicationInsights.Web.ApplicationInsightsHttpModule, Microsoft.AI.Web" preCondition="managedHandler" />
    </modules>
  </system.webServer>

</configuration>

Sql脚本:

USE [MvcPageDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ProcViewPager] (
    @recordTotal INT OUTPUT,            --输出记录总数
    @viewName VARCHAR(800),        --表名
    @fieldName VARCHAR(800) = '*',        --查询字段
    @keyName VARCHAR(200) = 'Id',            --索引字段
    @pageSize INT = 20,                    --每页记录数
    @pageNo INT =1,                    --当前页
    @orderString VARCHAR(200),        --排序条件
    @whereString VARCHAR(800) = '1=1'        --WHERE条件
)
AS
BEGIN
     DECLARE @beginRow INT
     DECLARE @endRow INT
     DECLARE @tempLimit VARCHAR(200)
     DECLARE @tempCount NVARCHAR(1000)
     DECLARE @tempMain VARCHAR(1000)
     --declare @timediff datetime 

     set nocount on
     --select @timediff=getdate() --记录时间

     SET @beginRow = (@pageNo - 1) * @pageSize    + 1
     SET @endRow = @pageNo * @pageSize
     SET @tempLimit = 'rows BETWEEN ' + CAST(@beginRow AS VARCHAR) +' AND '+CAST(@endRow AS VARCHAR)

     --输出参数为总记录数
     SET @tempCount = 'SELECT @recordTotal = COUNT(*) FROM (SELECT '+@keyName+' FROM '+@viewName+' WHERE '+@whereString+') AS my_temp'
     EXECUTE sp_executesql @tempCount,N'@recordTotal INT OUTPUT',@recordTotal OUTPUT

     --主查询返回结果集
     SET @tempMain = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (order by '+@orderString+') AS rows ,'+@fieldName+' FROM '+@viewName+' WHERE '+@whereString+') AS main_temp WHERE '+@tempLimit

     --PRINT @tempMain
     EXECUTE (@tempMain)
     --select datediff(ms,@timediff,getdate()) as 耗时 

     set nocount off
END


GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserInfo](
    [Id] [nvarchar](36) NOT NULL,
    [UserName] [nvarchar](50) NULL,
    [Nation] [nvarchar](50) NULL,
    [TrueName] [nvarchar](200) NULL,
    [Birthday] [datetime] NULL,
    [LocalAddress] [nvarchar](500) NULL,
    [Gender] [int] NULL,
 CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[UserInfo] ADD  CONSTRAINT [DF_UserInfo_Gender]  DEFAULT ((0)) FOR [Gender]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'民族' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UserInfo', @level2type=N'COLUMN',@level2name=N'Nation'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'真实姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UserInfo', @level2type=N'COLUMN',@level2name=N'TrueName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'出生日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UserInfo', @level2type=N'COLUMN',@level2name=N'Birthday'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UserInfo', @level2type=N'COLUMN',@level2name=N'LocalAddress'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 男 1 女' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UserInfo', @level2type=N'COLUMN',@level2name=N'Gender'
GO

运行结果如图: