基于mvc实现大数据量分页
来源：互联网 发布：网络上gem是什么意思 编辑：程序博客网 时间：2024/06/14 08:04
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=""Logs_"yyyyMMdd".txt"" /> <!--日志名称是否静态：否--> <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=\"Web\" /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]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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条件)ASBEGIN 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 offENDGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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]GOALTER TABLE [dbo].[UserInfo] ADD CONSTRAINT [DF_UserInfo_Gender] DEFAULT ((0)) FOR [Gender]GOEXEC 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'GOEXEC 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'GOEXEC 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'GOEXEC 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'GOEXEC 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运行结果如图:
阅读全文
0 0
- 基于mvc实现大数据量分页
- MySQL大数据量快速分页实现
- MySQL大数据量快速分页实现
- MySQL大数据量快速分页实现
- 大数据量分页（海量数据分页）的高效实现
- 大数据量分页
- 大数据量分页显示
- Displaytag1.1支持在外部实现大数据量分页
- Oracle大数据量分页显示的初步实现
- 大数据量下的分页
- 大数据量下的分页
- 大数据量下的分页
- Jax的大数据量分页
- Jax的大数据量分页
- 大数据量下的分页
- 大数据量下的分页
- 大数据量下的分页
- 大数据量下的分页
- POJ1000 A+B 问题
- 1002. A+B for Polynomials (25)
- 北京非法集资辩护律师谈监护的注意问题
- 面试知识点整理-OS部分
- Android
- 基于mvc实现大数据量分页
- Java 数据库连接池的实现
- RecyclerView---点击事件
- .net文件流操作
- POJ
- 论文可能用到的
- mysql事物的隔离级别
- maven搭建web项目
- Ubuntu16.04系统中创建新用户
