首页 > 代码库 > 【原创】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 GO10 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 AS22 BEGIN23 DECLARE @beginRow INT24 DECLARE @endRow INT25 DECLARE @tempLimit VARCHAR(200)26 DECLARE @tempCount NVARCHAR(1000)27 DECLARE @tempMain VARCHAR(1000)28 --declare @timediff datetime 29 30 set nocount on31 --select @timediff=getdate() --记录时间32 33 SET @beginRow = (@pageNo - 1) * @pageSize + 134 SET @endRow = @pageNo * @pageSize35 SET @tempLimit = ‘rows BETWEEN ‘ + CAST(@beginRow AS VARCHAR) +‘ AND ‘+CAST(@endRow AS VARCHAR)36 37 --输出参数为总记录数38 SET @tempCount = ‘SELECT @recordTotal = COUNT(*) FROM (SELECT ‘+@keyName+‘ FROM ‘+@viewName+‘ WHERE ‘+@whereString+‘) AS my_temp‘39 EXECUTE sp_executesql @tempCount,N‘@recordTotal INT OUTPUT‘,@recordTotal OUTPUT40 41 --主查询返回结果集42 SET @tempMain = ‘SELECT * FROM (SELECT ROW_NUMBER() OVER (order by ‘+@orderString+‘) AS rows ,‘+@fieldName+‘ FROM ‘+@viewName+‘ WHERE ‘+@whereString+‘) AS main_temp WHERE ‘+@tempLimit43 44 --PRINT @tempMain45 EXECUTE (@tempMain)46 --select datediff(ms,@timediff,getdate()) as 耗时 47 48 set nocount off49 END50 51 52 GO
Dapper
1 /// <summary> 2 /// 查询所有用户 3 /// </summary> 4 /// <returns></returns> 5 public List<UserInfo> GetAllList() 6 { 7 var list = new List<UserInfo>(); 8 //string sql = @"select Id,UserName,Nation,TrueName,Birthday,LocalAddressGender from UserInfo"; 9 using (SqlConnection conn = new SqlConnection(constr))10 {11 conn.Open();12 //标准写法13 //list = conn.Query<UserInfo>(sql,commandType: CommandType.Text).AsList();14 //dapper扩展写法15 list = conn.GetList<UserInfo>().AsList();16 conn.Close();17 }18 return list;19 }
Dapper分页
1 /// <summary> 2 /// 采用存储过程分页 3 /// </summary> 4 /// <param name="page"></param> 5 /// <param name="pageSize"></param> 6 /// <returns></returns> 7 public UserPage GetPageByProcList(int page=1,int pageSize=10) 8 { 9 UserPage model = new UserPage();10 var list = new List<UserInfo>();11 //string sql = @"select Id,UserName,Nation,TrueName,Birthday,LocalAddressGender from UserInfo";12 using (SqlConnection conn = new SqlConnection(constr))13 {14 conn.Open();15 DynamicParameters parm = new DynamicParameters();16 parm.Add("viewName", "UserInfo");17 parm.Add("fieldName", "*");18 parm.Add("keyName", "Id");19 parm.Add("pageSize", pageSize);20 parm.Add("pageNo", page);21 parm.Add("orderString", "Id");22 parm.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();30 list = conn.Query<UserInfo>("ProcViewPager", parm, commandType: CommandType.StoredProcedure).AsList();31 int totalCount = parm.Get<int>("@recordTotal");//返回总页数32 model.user = list;33 model.TotalCount = totalCount;34 conn.Close();35 }36 return model;37 }
1 public 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 UserInfo10 // {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 #endregion22 var pagelist = service.GetAllList().ToPagedList(page,10);23 return View(pagelist);24 }25 public ActionResult ProcPageIndex(int page=1)26 {27 var list = service.GetPageByProcList(page,5);28 return View();29 }30 public JsonResult GetProList(int page=1,int pagesize=10)31 {32 var model = service.GetPageByProcList(page, pagesize);33 return Json(model, JsonRequestBehavior.AllowGet);34 }
View
1 @{ 2 Layout = null; 3 } 4 <link href="~/Content/bootstrap.css" rel="stylesheet" /> 5 <link href="~/Content/PagedList.css" rel="stylesheet" /> 6 <link href="~/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="~/Scripts/jquery-1.9.1.min.js"></script>26 <script src="~/Scripts/jquery.pagination.js"></script>27 <script src="~/Scripts/bootstrap.js"></script>28 <script type="text/javascript">29 //分页查询开始30 $(document).ready(function () {31 getDataList(0, null);32 });33 34 var pagesize = 50;35 var page = 1;36 var initFlag = true;37 38 function getDataList(currPage, jg) {39 40 $.ajax({41 url: "/Home/GetProList",42 type: "get",43 dataType: ‘json‘,44 data: { pagesize: pagesize, page: currPage + 1 },45 contentType: "application/x-www-form-urlencoded; charset=utf-8",46 success: function (response) { 47 if (response.user != null && response.user != "" && response.TotalCount != undefined && response.TotalCount > 0) {48 if (initFlag) {49 $("#Pagination").pagination(50 response.TotalCount,51 {52 items_per_page: pagesize,53 num_edge_entries: 1,54 num_display_entries: 8,55 callback: getDataList//回调函数56 });57 initFlag = false;58 }59 $("#tbodylist").html("");60 loadDataList(response.user);61 } else {62 63 }64 65 }66 });67 }68 69 function loadDataList(listdata) {71 var tbody = "";72 $(listdata).each(function (i, n) {73 //表格74 tbody += "<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>
截图
【原创】10万条数据采用存储过程分页实现（Mvc+Dapper+存储过程）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。