原创10万条数据采用存储过程分页实现（Mvc+Dapper+存储过程） _原创

笛里谁知壮士心，沙头空照征人骨。这篇文章主要讲述原创10万条数据采用存储过程分页实现（Mvc+Dapper+存储过程）相关的知识，希望能为你提供帮助。
有时候大数据量进行查询操作的时候，查询速度很大强度上可以影响用户体验，因此自己简单写了一个demo，简单总结记录一下：
技术：Mvc4+Dapper+Dapper扩展+Sqlserver
目前主要实现了两种分页：一种采用 PagedList.Mvc 实现的分页
两外一种采用 ajax异步加载分页采用比较常用的jquery.pagination 分页插件。
功能相对比较简单仅供学习交流。
通用存储过程

1 USE [MvcProcPageDB] 2 GO 3 4 /****** Object:StoredProcedure [dbo].[ProcViewPager]Script Date: 2017/4/23 16:41:16 ******/ 5 SET ANSI_NULLS ON 6 GO 7 8 SET QUOTED_IDENTIFIER ON 9 GO 10 11 CREATE PROCEDURE [dbo].[ProcViewPager] ( 12@recordTotal INT OUTPUT,--输出记录总数 13@viewName VARCHAR(800),--表名 14@fieldName VARCHAR(800) = \'*\',--查询字段 15@keyName VARCHAR(200) = \'Id\',--索引字段 16@pageSize INT = 20,--每页记录数 17@pageNo INT =1,--当前页 18@orderString VARCHAR(200),--排序条件 19@whereString VARCHAR(800) = \'1=1\'--WHERE条件 20 ) 21 AS 22 BEGIN 23DECLARE @beginRow INT 24DECLARE @endRow INT 25DECLARE @tempLimit VARCHAR(200) 26DECLARE @tempCount NVARCHAR(1000) 27DECLARE @tempMain VARCHAR(1000) 28--declare @timediff datetime 29 30set nocount on 31--select @timediff=getdate() --记录时间 32 33SET @beginRow = (@pageNo - 1) * @pageSize+ 1 34SET @endRow = @pageNo * @pageSize 35SET @tempLimit = \'rows BETWEEN \' + CAST(@beginRow AS VARCHAR) +\' AND \'+CAST(@endRow AS VARCHAR) 36 37--输出参数为总记录数 38SET @tempCount = \'SELECT @recordTotal = COUNT(*) FROM (SELECT \'+@keyName+\' FROM \'+@viewName+\' WHERE \'+@whereString+\') AS my_temp\' 39EXECUTE sp_executesql @tempCount,N\'@recordTotal INT OUTPUT\',@recordTotal OUTPUT 40 41--主查询返回结果集 42SET @tempMain = \'SELECT * FROM (SELECT ROW_NUMBER() OVER (order by \'+@orderString+\') AS rows ,\'+@fieldName+\' FROM \'+@viewName+\' WHERE \'+@whereString+\') AS main_temp WHERE \'+@tempLimit 43 44--PRINT @tempMain 45EXECUTE (@tempMain) 46--select datediff(ms,@timediff,getdate()) as 耗时 47 48set nocount off 49 END 50 51 52 GO

Dapper

1/// < summary> 2/// 查询所有用户 3/// < /summary> 4/// < returns> < /returns> 5public List< UserInfo> GetAllList() 6{ 7var list = new List< UserInfo> (); 8//string sql = @"select Id,UserName,Nation,TrueName,Birthday,LocalAddressGender from UserInfo"; 9using (SqlConnection conn = new SqlConnection(constr)) 10{ 11conn.Open(); 12//标准写法 13//list = conn.Query< UserInfo> (sql,commandType: CommandType.Text).AsList(); 14//dapper扩展写法 15list = conn.GetList< UserInfo> ().AsList(); 16conn.Close(); 17} 18return list; 19}

Dapper分页

1 /// < summary> 2/// 采用存储过程分页 3/// < /summary> 4/// < param name="page"> < /param> 5/// < param name="pageSize"> < /param> 6/// < returns> < /returns> 7public UserPage GetPageByProcList(int page=1,int pageSize=10) 8{ 9UserPage model = new UserPage(); 10var list = new List< UserInfo> (); 11//string sql = @"select Id,UserName,Nation,TrueName,Birthday,LocalAddressGender from UserInfo"; 12using (SqlConnection conn = new SqlConnection(constr)) 13{ 14conn.Open(); 15DynamicParameters parm = new DynamicParameters(); 16parm.Add("viewName", "UserInfo"); 17parm.Add("fieldName", "*"); 18parm.Add("keyName", "Id"); 19parm.Add("pageSize", pageSize); 20parm.Add("pageNo", page); 21parm.Add("orderString", "Id"); 22parm.Add("recordTotal", 0, DbType.Int32, ParameterDirection.Output); 23//参数名得和存储过程的变量名相同（参数可以跳跃传，键值对方式即可） 24//强类型 25//list = conn.Query< UserInfo> ("P_GridViewPager", new { viewName = "Edu_StudentSelectedCourse", fieldName = "*", keyName = "Id", pageSize = 20, pageNo = 1, orderString = "id" }, commandType: CommandType.StoredProcedure).ToList(); 26//标准写法 27//list = conn.Query< UserInfo> (sql,commandType: CommandType.Text).AsList(); 28//dapper扩展写法 29//list = conn.GetList< UserInfo> ().AsList(); 30list = conn.Query< UserInfo> ("ProcViewPager", parm, commandType: CommandType.StoredProcedure).AsList(); 31int totalCount = parm.Get< int> ("@recordTotal"); //返回总页数 32model.user = list; 33model.TotalCount = totalCount; 34conn.Close(); 35} 36return model; 37}

1public ActionResult Index(int page=1) 2{ 3 4#region 插入10条数据 5 6//for (int i = 1; i < = 100000; i++) 7//{ 8//list.Add( 9//new UserInfo 10//{ 11//Id = Guid.NewGuid().ToString(), 12//UserName = "xiaoming" + i, 13//Birthday = Convert.ToDateTime("1987-12-11"), 14//Gender = 1, 15//LocalAddress = "河南省", 16//TrueName = "小明" + i, 17//Nation = "汉族" 18//}); 19//} 20//ss.InsertAll(list); 21#endregion 22var pagelist = service.GetAllList().ToPagedList(page,10); 23return View(pagelist); 24} 25public ActionResult ProcPageIndex(int page=1) 26{ 27var list = service.GetPageByProcList(page,5); 28return View(); 29} 30public JsonResult GetProList(int page=1,int pagesize=10) 31{ 32var model = service.GetPageByProcList(page, pagesize); 33return Json(model, JsonRequestBehavior.AllowGet); 34}

View

1 @{ 2Layout = null; 3 } 4 < link href="https://www.songbingjia.com/android/~/Content/bootstrap.css" rel="stylesheet" /> 5 < link href="https://www.songbingjia.com/android/~/Content/PagedList.css" rel="stylesheet" /> 6 < link href="https://www.songbingjia.com/android/~/Scripts/pagination.css" rel="stylesheet" /> 7 < div class="well"> 8< table class="table"> 9< thead> 10< tr> 11< th> 用户名< /th> 12< th> 真实姓名< /th> 13< th> 出生日期< /th> 14< th> 地址< /th> 15< /tr> 16< /thead> 17< tbody id="tbodylist"> < /tbody> 18 19< /table> 20 21 < /div> 22 < div id="Pagination" class="pagination"> 23 24 < /div> 25 < script src="https://www.songbingjia.com/android/~/Scripts/jquery-1.9.1.min.js"> < /script> 26 < script src="https://www.songbingjia.com/android/~/Scripts/jquery.pagination.js"> < /script> 27 < script src="https://www.songbingjia.com/android/~/Scripts/bootstrap.js"> < /script> 28 < script type="text/javascript"> 29//分页查询开始 30$(document).ready(function () { 31getDataList(0, null); 32}); 33 34var pagesize = 50; 35var page = 1; 36var initFlag = true; 37 38function getDataList(currPage, jg) { 39 40$.ajax({ 41url: "/Home/GetProList", 42type: "get", 43dataType: \'json\', 44data: { pagesize: pagesize, page: currPage + 1 }, 45contentType: "application/x-www-form-urlencoded; charset=utf-8", 46success: function (response) { 47if (response.user != null & & response.user != "" & & response.TotalCount != undefined & & response.TotalCount > 0) { 48if (initFlag) { 49$("#Pagination").pagination( 50response.TotalCount, 51{ 52items_per_page: pagesize, 53num_edge_entries: 1, 54num_display_entries: 8, 55callback: getDataList//回调函数 56}); 57initFlag = false; 58} 59$("#tbodylist").html(""); 60loadDataList(response.user); 61} else { 62 63} 64 65} 66}); 67} 68 69function loadDataList(listdata) {71var tbody = ""; 72$(listdata).each(function (i, n) { 73//表格 74tbody += "< tr> " + 75"< td> " + n.UserName + "< /td> " + 76"< td> " + n.TrueName + "< /td> " + 77"< td> " + n.Birthday + "< /td> " + 78"< td> " + n.LocalAddress + "< /td> " + 79"< /tr> "; 80}); 81$("#tbodylist").html(html); 82 83}84//分页查询结束 85 < /script>

截图