ASP.NET MVC + EF 利用存储过程读取大数据，1亿数据测试很OK
看到本文的标题，相信你会忍不住进来看看！
没错，本文要讲的就是这个重量级的东西，这个不仅仅支持单表查询，更能支持连接查询，
加入一个表10W数据，另一个表也是10万数据，当你用linq建立一个连接查询然后利用take,skip读取第N页数据的时候，
你的程序就挂了，因为，你很可能读取需要几十秒甚至几分钟以上。
下面来讲解一下，ASP.NET MVC + EF 利用存储过程读取大数据的详细过程。
1.首先，我们创建一个实体类PageinationInfo,主要用于分页，如下
1 public class PageinationInfo 2 { 3 /// <summary> 4 /// 要显示的表或多个表的连接 5 /// </summary> 6 public string strTable { get; set; } 7 8 /// <summary> 9 /// 要查询的字段 10 /// </summary> 11 public string strField { get; set; } 12 13 /// <summary> 14 /// 每页多少条记录 15 /// </summary> 16 public int pageSize { get; set; } 17 18 /// <summary> 19 /// 当前页 20 /// </summary> 21 public int pageIndex { get; set; } 22 23 /// <summary> 24 /// 查询条件,不需where 25 /// </summary> 26 public string strWhere { get; set; } 27 28 /// <summary> 29 /// 用于排序的主键 30 /// </summary> 31 public string strSortKey { get; set; } 32 33 /// <summary> 34 /// 用于排序，如：id desc (多个id desc,dt asc) 35 /// </summary> 36 public string strSortField { get; set; } 37 38 /// <summary> 39 /// 排序,0-顺序,1-倒序 40 /// </summary> 41 public bool strOrderBy { get; set; } 42 43 /// <summary> 44 /// 总记录数 45 /// </summary> 46 public int RecordCount { get; set; } 47 48 /// <summary> 49 /// 总页数 50 /// </summary> 51 public int PageCount { get; set; } 52 53 /// <summary> 54 /// 查询耗时,毫秒为单位 55 /// </summary> 56 public int UsedTime { get; set; } 57 58 }
2.然后我们再DAL层新建一个类 PageinationInfoService 主要用于实现分页读取数据，如下：
1 public class PageinationInfoService 2 { 3 /// <summary> 4 /// 获取分页列表 5 /// </summary> 6 /// <param name="pageinationInfo"></param> 7 /// <returns></returns> 8 public IList<Entity> GetPageinationInfoList<Entity>(PageinationInfo pageinationInfo) where Entity : class 9 { 10 dynamic result = null; 11 using (SnsLearningLogManagerDB db = new SnsLearningLogManagerDB()) 12 { 13 #region SqlParameter参数 14 SqlParameter[] paras = new SqlParameter[10]; 15 paras[0] = new SqlParameter("strTable", DbType.String); 16 paras[0].Value = pageinationInfo.strTable; 17 18 paras[1] = new SqlParameter("strField", DbType.String); 19 paras[1].Value = pageinationInfo.strField; 20 21 paras[2] = new SqlParameter("pageSize", DbType.Int16); 22 paras[2].Value = pageinationInfo.pageSize; 23 24 paras[3] = new SqlParameter("pageIndex", DbType.Int16); 25 paras[3].Value = pageinationInfo.pageIndex; 26 27 paras[4] = new SqlParameter("strWhere", DbType.String); 28 paras[4].Value = pageinationInfo.strWhere; 29 30 paras[5] = new SqlParameter("strSortKey", DbType.String); 31 paras[5].Value = pageinationInfo.strSortKey; 32 33 paras[6] = new SqlParameter("strSortField", DbType.String); 34 paras[6].Value = pageinationInfo.strSortField; 35 36 paras[7] = new SqlParameter("strOrderBy", DbType.Boolean); 37 paras[7].Value = pageinationInfo.strOrderBy; 38 39 paras[8] = new SqlParameter("RecordCount", DbType.Int16); 40 paras[8].Value = pageinationInfo.RecordCount; 41 paras[8].Direction = ParameterDirection.Output; 42 43 paras[9] = new SqlParameter("UsedTime", DbType.Int16); 44 paras[9].Value = pageinationInfo.UsedTime; 45 paras[9].Direction = ParameterDirection.Output; 46 #endregion 47 48 try 49 { 50 result = db.Database.SqlQuery<Entity>("exec LYBPager @strTable,@strField,@pageSize,@pageIndex,@strWhere,@strSortKey,@strSortField,@strOrderBy,@RecordCount output,@UsedTime output", paras).ToList(); 51 pageinationInfo.RecordCount = (int)paras[8].Value; 52 pageinationInfo.UsedTime = (int)paras[9].Value; 53 } 54 catch (Exception ex) 55 { 56 throw; 57 } 58 } 59 return result; 60 } 61 }
PageinationInfoService类中我们传入的参数是实体和PageinationInfo，实体主要是用于接收数据并封装到实体中，LYBPager 这个是数据库中存储过程的名称，而UsedTime 就是存储过程读取数据所用的时间
BLL层的东西，我就不贴出来了，你们或者用工厂模式或者用简单3层，这个都无关紧要。
3.接下来，我们在Controller里来处理我们的业务需求，贴一段自己项目的代码作为分析，如下
1 public ActionResult Index(string LabelName,string _Title,string _Content, int pageNumber = 1, int pageSize = 10) 2 { 3 #region 分页 4 SnsModels.PageinationInfo pageinationInfo = new SnsModels.PageinationInfo(); 5 pageinationInfo.pageIndex = pageNumber; 6 pageinationInfo.pageSize = pageSize; 7 pageinationInfo.RecordCount = 0; 8 pageinationInfo.strField = "*"; 9 pageinationInfo.strOrderBy = true; 10 pageinationInfo.strSortField = "ArticleID desc"; 11 pageinationInfo.strSortKey = "ArticleID"; 12 pageinationInfo.strTable = "ArticleInfo"; 13 pageinationInfo.strWhere = " 1=1"; 14 pageinationInfo.UsedTime = 0; 15 pageinationInfo.PageCount = 0; 16 #endregion 17 18 IList<SnsModels.LabelInfo> LabelInfoList = Repository.GetList<SnsModels.LabelInfo>(); 19 ViewBag.LabelInfoList = new SelectList(LabelInfoList,"LabelName","LabelName"); 20 21 #region 参数处理 22 if (LabelName != null) 23 { 24 if (!string.IsNullOrEmpty(LabelName)) 25 { 26 pageinationInfo.strWhere += " and LabelName like '%" + HttpUtility.UrlDecode(LabelName.Trim()) + "%'"; 27 ViewBag.LabelInfoList = new SelectList(LabelInfoList, "LabelName", "LabelName",LabelName.Trim()); 28 } 29 } 30 if (_Title != null) 31 { 32 if (!string.IsNullOrEmpty(_Title)) 33 { 34 pageinationInfo.strWhere += " and Title like '%" + _Title.Trim() + "%'"; 35 } 36 } 37 if (_Content != null) 38 { 39 if (!string.IsNullOrEmpty(_Content)) 40 { 41 pageinationInfo.strWhere += " and Content like '%" + _Content.Trim() + "%'"; 42 } 43 } 44 #endregion 45 46 47 IList<SnsModels.ArticleInfo> List = PageinationInfoManager.GetPageinationInfoList<SnsModels.ArticleInfo>(pageinationInfo); 48 49 #region 传值 50 ViewBag.List = List; 51 ViewBag.pageNumber = pageNumber; 52 ViewBag.pageSize = pageSize; 53 ViewBag.RecordCount = pageinationInfo.RecordCount; 54 ViewBag.LabelName =HttpUtility.UrlDecode(LabelName); 55 ViewBag._Title = _Title; 56 ViewBag._Content = _Content; 57 #endregion 58 59 return View(); 60 }
上面我们通过把参数传递到PageinationInfo，然后根据传递的参数拼凑pageinationInfo.strWhere现实了多条件查询，
pageNumber，pageSize 我们已经传递到View，你可以通过Jquery插件展示你的分页，点击一下一页的时候，跳转回控制器就行了。
4，接下来，我给出存储过程，代码如下：
USE [LearningLogManagerDB2] GO /****** Object: StoredProcedure [dbo].[LYBPager] Script Date: 07/30/2014 11:51:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --参数说明------------------------------------------------------------- /**//* @strTable --要显示的表或多个表的连接 @strField --要查询出的字段列表,*表示全部字段 @pageSize --每页显示的记录个数 @pageIndex --要显示那一页的记录 @strWhere --查询条件,不需where @strSortKey --用于排序的主键 @strSortField --用于排序，如：id desc (多个id desc,dt asc) @strOrderBy --排序,0-顺序,1-倒序 @RecordCount --查询到的总记录数 @UsedTime --耗时测试时间差 */ ALTER PROCEDURE [dbo].[LYBPager] @strTable varchar(1000) = '[dbo].[ttable]',--表名 @strField varchar(1000) = '*', --查询字段 @pageSize int = 10, --每页多少条记录 @pageIndex int = 1, --当前页 @strWhere varchar(1000) = '1=1', --查询条件 @strSortKey varchar(1000) = 'id', --主键 @strSortField varchar(500) = 'id DESC', --排序 @strOrderBy bit = 1, --是否排序 1表示排序 @RecordCount int OUTPUT, --总记录数 @UsedTime int OUTPUT --查询耗时,毫秒为单位 AS SET NOCOUNT ON Declare @sqlcount INT Declare @timediff DATETIME select @timediff=getdate() Begin Tran DECLARE @sql nvarchar(500),@where1 varchar(200),@where2 varchar(200) IF @strWhere is null or rtrim(@strWhere)='' BEGIN--没有查询条件 SET @where1=' WHERE ' SET @where2=' ' END ELSE BEGIN--有查询条件 SET @where1=' WHERE ('+@strWhere+') AND ' --本来有条件再加上此条件 SET @where2=' WHERE ('+@strWhere+') ' --原本没有条件而加上此条件 END --SET @sql='SELECT @intResult=COUNT(*) FROM '+@strTable+@where2 BEGIN SET @sql='SELECT @sqlcount=COUNT(*) FROM (select '+@strSortKey+' from '+ @strTable + @where2 +') As tmptab' END --print @sql EXEC sp_executesql @sql,N'@sqlcount int OUTPUT',@sqlcount OUTPUT --计算总记录数 SELECT @RecordCount = @sqlcount --设置总记录数 IF @pageIndex=1 --第一页 BEGIN SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+' FROM '+@strTable+@where2+'ORDER BY '+ @strSortField END Else BEGIN IF @strOrderBy=0 SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+ ' FROM '+ @strTable+@where1+@strSortKey+'>(SELECT MAX('+case when charindex('.',@strSortKey)>0 then right(@strSortKey,len(@strSortKey)-charindex('.',@strSortKey)) else @strSortKey end+') '+ ' FROM (SELECT TOP '+ CAST(@pageSize*(@pageIndex-1) AS varchar(200))+' '+@strSortKey+' FROM '+@strTable+@where2+ 'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField ELSE SET @sql='SELECT TOP '+CAST(@pageSize AS varchar(200))+' '+@strField+' FROM '+@strTable+@where1+ @strSortKey+'<(SELECT MIN('+case when charindex('.',@strSortKey)>0 then right(@strSortKey,len(@strSortKey)-charindex('.',@strSortKey)) else @strSortKey end+') '+ ' FROM (SELECT TOP '+CAST(@pageSize*(@pageIndex-1) AS varchar(200))+' '+ @strSortKey+' FROM '+@strTable+@where2+'ORDER BY '+@strSortField+') t) ORDER BY '+@strSortField+'' END print @sql --select @RecordCount EXEC(@sql) print @sql If @@Error <> 0 Begin RollBack Tran Return -1 End Else Begin Commit TRAN set @UsedTime = datediff(ms,@timediff,getdate()) --select @UsedTime --select datediff(ms,@timediff,getdate()) as 耗时 Return @sqlcount End
当然存储过程不是本人写的，我只是利用而已，也用了很久了，感觉还可以，有兴趣的可以分析分析。
在此非常感谢很多博友加入本人ASP.NET MVC QQ群，并且都很踊跃提问。
本群提供ASP.NET MVC,EF,LINQ,WEB API技术支持，不在乎人多，在乎人精。
ASP.NET MVC群 171560784
诚邀各路高手、初学者加入。
