你的位置:首页 > ASP.net教程

[ASP.net教程]【无私分享:从入门到精通ASP.NET MVC】从0开始,一起搭框架、做项目(3)公共基础数据操作类 RepositoryBase


声明:本系列为原创,分享本人现用框架,未经本人同意,禁止转载!http://yuangang.cnblogs.com

跟着做完这个项目,你就有一套自己的MVC面向接口,依赖注入的简洁的系统了,希望大家好好一步一步做。所有的技术和项目,都毫无保留的提供,希望大家能自己跟着做一套,所以不提供Demo下载!还有,请大家放心,只要大家喜欢,有人需要,绝对不会烂尾,我会坚持写完~

在这里,我首先要感谢 @清竹o

 

索引

 【无私分享:从入门到精通ASP.NET MVC】从0开始,一起搭框架、做项目(1)搭建MVC环境 注册区域

 【无私分享:从入门到精通ASP.NET MVC】从0开始,一起搭框架、做项目(2)创建数据库和数据模型

简述

今天我们写一个基础数据的操作类,如果里面有大家不理解的地方,可采取两种方式,第一:提出来,第二:会用就行。这个类呢我一般不去修改它,因为基础操作类,大家也可以直接拷贝到自己的项目中。

项目准备

我们用的工具是:VS 2013 + SqlServer 2012 + IIS6

希望大家对ASP.NET MVC有一个初步的理解,理论性的东西我们不做过多解释,有些地方不理解也没关系,会用就行了,用的多了,用的久了,自然就理解了。

项目开始

一、创建接口 IRepository

我们在应用服务的Service类库下面新建一个接口 IRepository

右击Service类库→添加→新建项→接口  名称 IRepository

 

二、基础操作的接口方法

不了解接口的朋友需要注意下,接口是个规范,是不需要方法体的,说的通俗点,有了接口如果被继承肯定会有实现,这里你只需要写方法就行了,具体的方法是如何实现的我们不管,爱怎么实现怎么实现,我只是告诉你,你要是继承我这个接口,那么我这些方法你必须实现,而且要符合我规范。这就跟领导一条批示:你给我做个上传图片的方法,返回上传成功或失败。领导就是接口,你是实现类,你在这个领导下面干活,就相当于你继承了领导,那么你必须要完成这个图片上传方法并且返回上传结果,至于你是用网上的上传方法呀还是自己写个webservice啊还是用jquery插件啊随你,领导不关心你用什么,他只关心你按照他的要求去实现这个方法。这样不知道大家理解接口了不?

 

我们这个接口是公共基础数据操作类,所以要有数据模型啊,模型说白了就是类,因为是通用的,所以我们这里写 T 并且标识 T 是个 Class

我们的Service类库呢,如果按照三层架构来讲,应该是数据管理层,既然是数据管理层,那么我们就是操作数据模型的,我们添加Domain 数据模型的引用,并且我们可能用到一些公共的方法,所以我们也添加对Common公共帮助类库的引用

 

上面我们讲了,这是数据管理层,我们是要对数据进行操作的,公用类库呢,我们提供多种数据管理的方式,我们再添加两个库引用 EntityFramework和EntityFramework.SqlServer

 

我们先来声明几个数据对象操作

 

下面,我们写几个接口方法

主要有 单模型操作  多模型操作 存储过程操作 查询多条数据 分页查询 ADO.NET增删改查

 

有朋友看到这会骂街了,我擦,能不能直接贴代码,截图干嘛~~ 不要急,不要急,一定要有耐心~~

我是先贴图,给大家有个大致的了解,代码肯定会贴出来的~~

 

单模型操作

 

 1 #region 单模型操作 2     /// <summary> 3     /// 获取实体 4     /// </summary> 5     /// <param name="id">主键</param> 6     /// <returns>实体</returns> 7     T Get(Expression<Func<T, bool>> predicate); 8     /// <summary> 9     /// 插入实体10     /// </summary>11     /// <param name="entity">实体</param>12     /// <returns>ID</returns>13     bool Save(T entity);14 15     /// <summary>16     /// 修改实体17     /// </summary>18     /// <param name="entity">实体</param>19     bool Update(T entity);20     /// <summary>21     /// 修改或保存实体22     /// </summary>23     /// <param name="entity">实体</param>24     bool SaveOrUpdate(T entity, bool isEdit);25 26     /// <summary>27     /// 删除实体28     /// </summary>29     int Delete(Expression<Func<T, bool>> predicate = null);30 31     /// <summary>32     /// 执行SQL删除33     /// </summary>34     int DeleteBySql(string sql, params DbParameter[] para);35 36     /// <summary>37     /// 根据属性验证实体对象是否存在38     /// </summary>39     bool IsExist(Expression<Func<T, bool>> predicate);40 41     /// <summary>42     /// 根据SQL验证实体对象是否存在43     /// </summary>44     bool IsExist(string sql, params DbParameter[] para);45     #endregion

View Code

 

 

多模型操作

 

 1 #region 多模型操作 2     /// <summary> 3     /// 增加多模型数据,指定独立模型集合 4     /// </summary> 5     int SaveList<T1>(List<T1> t) where T1 : class; 6     /// <summary> 7     /// 增加多模型数据,与当前模型一致 8     /// </summary> 9     int SaveList(List<T> t);10     /// <summary>11     /// 更新多模型,指定独立模型集合12     /// </summary>13     int UpdateList<T1>(List<T1> t) where T1 : class;14     /// <summary>15     /// 更新多模型,与当前模型一致16     /// </summary>17     int UpdateList(List<T> t);18     /// <summary>19     /// 批量删除数据,当前模型20     /// </summary>21     int DeleteList(List<T> t);22     /// <summary>23     /// 批量删除数据,独立模型24     /// </summary>25     int DeleteList<T1>(List<T1> t) where T1 : class;26     #endregion

View Code

 

 

存储过程操作

 

 1 #region 存储过程操作 2     /// <summary> 3     /// 执行增删改存储过程 4     /// </summary> 5     object ExecuteProc(string procname, params DbParameter[] parameter); 6     /// <summary> 7     /// 执行查询的存储过程 8     /// </summary> 9     object ExecuteQueryProc(string procname, params DbParameter[] parameter);10     #endregion

View Code

 

 

查询多条数据

 

 1 #region 查询多条数据 2     /// <summary> 3     /// 获取集合 IQueryable 4     /// </summary> 5     IQueryable<T> LoadAll(Expression<Func<T, bool>> predicate); 6     /// <summary> 7     /// 获取集合 IList 8     /// </summary> 9     List<T> LoadListAll(Expression<Func<T, bool>> predicate);10     /// <summary>11     /// 获取DbQuery的列表12     /// </summary>13     DbQuery<T> LoadQueryAll(Expression<Func<T, bool>> predicate);14     /// <summary>15     /// 获取IEnumerable列表16     /// </summary>17     IEnumerable<T> LoadEnumerableAll(string sql, params DbParameter[] para);18     /// <summary>19     /// 获取数据动态集合20     /// </summary>21     System.Collections.IEnumerable LoadEnumerable(string sql, params DbParameter[] para);22     /// <summary>23     /// 采用SQL进行数据的查询,并转换24     /// </summary>25     List<T> SelectBySql(string sql, params DbParameter[] para);26     List<T1> SelectBySql<T1>(string sql, params DbParameter[] para);27     /// <summary>28     /// 可指定返回结果、排序、查询条件的通用查询方法,返回实体对象29     /// </summary>30     /// <typeparam name="TEntity">实体对象</typeparam>31     /// <typeparam name="TOrderBy">排序字段类型</typeparam>32     /// <typeparam name="TResult">数据结果,一般为object</typeparam>33     /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>34     /// <param name="orderby">排序字段</param>35     /// <param name="selector">返回结果(必须是模型中存在的字段)</param>36     /// <param name="IsAsc">排序方向,true为正序false为倒序</param>37     /// <returns>实体集合</returns>38     List<TResult> QueryEntity<TEntity, TOrderBy, TResult>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Expression<Func<TEntity, TResult>> selector, bool IsAsc)39       where TEntity : class40       where TResult : class;41     /// <summary>42     /// 可指定返回结果、排序、查询条件的通用查询方法,返回Object对象43     /// </summary>44     /// <typeparam name="TEntity">实体对象</typeparam>45     /// <typeparam name="TOrderBy">排序字段类型</typeparam>46     /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>47     /// <param name="orderby">排序字段</param>48     /// <param name="selector">返回结果(必须是模型中存在的字段)</param>49     /// <param name="IsAsc">排序方向,true为正序false为倒序</param>50     /// <returns>自定义实体集合</returns>51     List<object> QueryObject<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc)52       where TEntity : class;53     /// <summary>54     /// 可指定返回结果、排序、查询条件的通用查询方法,返回动态类对象55     /// </summary>56     /// <typeparam name="TEntity">实体对象</typeparam>57     /// <typeparam name="TOrderBy">排序字段类型</typeparam>58     /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>59     /// <param name="orderby">排序字段</param>60     /// <param name="selector">返回结果(必须是模型中存在的字段)</param>61     /// <param name="IsAsc">排序方向,true为正序false为倒序</param>62     /// <returns>动态类对象</returns>63     dynamic QueryDynamic<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc)64       where TEntity : class;65     #endregion

View Code

 

 

分页查询

 

 1  #region 分页查询 2  3     /// <summary> 4     /// 通过SQL分页 5     /// </summary> 6     /// <param name="sql"></param> 7     /// <param name="parameters"></param> 8     /// <param name="page"></param> 9     /// <returns></returns>10     IList<T1> PageByListSql<T1>(string sql, IList<DbParameter> parameters, PageCollection page);11     IList<T> PageByListSql(string sql, IList<DbParameter> parameters, PageCollection page);12     /// <summary>13     /// 通用EF分页,默认显示20条记录14     /// </summary>15     /// <typeparam name="TEntity">实体模型</typeparam>16     /// <typeparam name="TOrderBy">排序类型</typeparam>17     /// <param name="index">当前页</param>18     /// <param name="pageSize">显示条数</param>19     /// <param name="where">过滤条件</param>20     /// <param name="orderby">排序字段</param>21     /// <param name="selector">结果集合</param>22     /// <param name="isAsc">排序方向true正序 false倒序</param>23     /// <returns>自定义实体集合</returns>24     PageInfo<object> Query<TEntity, TOrderBy>25       (int index, int pageSize,26       Expression<Func<TEntity, bool>> where,27       Expression<Func<TEntity, TOrderBy>> orderby,28       Func<IQueryable<TEntity>, List<object>> selector,29       bool IsAsc)30       where TEntity : class;31     /// <summary>32     /// 对IQueryable对象进行分页逻辑处理,过滤、查询项、排序对IQueryable操作33     /// </summary>34     /// <param name="t">Iqueryable</param>35     /// <param name="index">当前页</param>36     /// <param name="PageSize">每页显示多少条</param>37     /// <returns>当前IQueryable to List的对象</returns>38     Common.PageInfo<T> Query(IQueryable<T> query, int index, int PageSize);39     /// <summary>40     /// 普通SQL查询分页方法41     /// </summary>42     /// <param name="index">当前页</param>43     /// <param name="pageSize">显示行数</param>44     /// <param name="tableName">表名/视图</param>45     /// <param name="field">获取项</param>46     /// <param name="filter">过滤条件</param>47     /// <param name="orderby">排序字段+排序方向</param>48     /// <param name="group">分组字段</param>49     /// <returns>结果集</returns>50     Common.PageInfo Query(int index, int pageSize, string tableName, string field, string filter, string orderby, string group, params DbParameter[] para);51     /// <summary>52     /// 简单的Sql查询分页53     /// </summary>54     /// <param name="index"></param>55     /// <param name="pageSize"></param>56     /// <param name="sql"></param>57     /// <returns></returns>58     Common.PageInfo Query(int index, int pageSize, string sql, string orderby, params DbParameter[] para);59     /// <summary>60     /// 多表联合分页算法61     /// </summary>62     PageInfo Query(IQueryable query, int index, int pagesize);63     #endregion

View Code

 

 

ADO.NET增删改查

 

 1  #region ADO.NET增删改查方法 2     /// <summary> 3     /// 执行增删改方法,含事务处理 4     /// </summary> 5     object ExecuteSqlCommand(string sql, params DbParameter[] para); 6     /// <summary> 7     /// 执行多条SQL,增删改方法,含事务处理 8     /// </summary> 9     object ExecuteSqlCommand(Dictionary<string, object> sqllist);10     /// <summary>11     /// 执行查询方法,返回动态类,接收使用var,遍历时使用dynamic类型12     /// </summary>13     object ExecuteSqlQuery(string sql, params DbParameter[] para);14     #endregion

View Code

 

 

我们引用的类库和解决方案

 

1 using Common;2 using System;3 using System.Collections.Generic;4 using System.Data.Common;5 using System.Linq;6 using System.Text;7 using System.Data.Entity.Infrastructure;8 using System.Data.Entity;9 using System.Linq.Expressions;

View Code

 

 

这里是完整的IRepository.cs

 

 1 using Common; 2 using System; 3 using System.Collections.Generic; 4 using System.Data.Common; 5 using System.Linq; 6 using System.Text; 7 using System.Data.Entity.Infrastructure; 8 using System.Data.Entity; 9 using System.Linq.Expressions; 10  11 namespace Service 12 { 13   /// <summary> 14   /// 所有的数据操作基类接口 15   /// add yuangang by 2016-05-09 16   /// </summary> 17   public interface IRepository<T> where T:class 18   { 19     #region 数据对象操作 20     /// <summary> 21     /// 数据上下文 22     /// </summary> 23     DbContext Context { get; } 24     /// <summary> 25     /// 数据上下文 26     /// </summary> 27     Domain.MyConfig Config { get; } 28     /// <summary> 29     /// 数据模型操作 30     /// </summary> 31     DbSet<T> dbSet { get; } 32     /// <summary> 33     /// EF事务 34     /// </summary> 35     DbContextTransaction Transaction { get; set; } 36     /// <summary> 37     /// 事务提交结果 38     /// </summary> 39     bool Committed { get; set; } 40     /// <summary> 41     /// 提交事务 42     /// </summary> 43     void Commit(); 44     /// <summary> 45     /// 回滚事务 46     /// </summary> 47     void Rollback(); 48     #endregion 49  50     #region 单模型操作 51     /// <summary> 52     /// 获取实体 53     /// </summary> 54     /// <param name="id">主键</param> 55     /// <returns>实体</returns> 56     T Get(Expression<Func<T, bool>> predicate); 57     /// <summary> 58     /// 插入实体 59     /// </summary> 60     /// <param name="entity">实体</param> 61     /// <returns>ID</returns> 62     bool Save(T entity); 63  64     /// <summary> 65     /// 修改实体 66     /// </summary> 67     /// <param name="entity">实体</param> 68     bool Update(T entity); 69     /// <summary> 70     /// 修改或保存实体 71     /// </summary> 72     /// <param name="entity">实体</param> 73     bool SaveOrUpdate(T entity, bool isEdit); 74  75     /// <summary> 76     /// 删除实体 77     /// </summary> 78     int Delete(Expression<Func<T, bool>> predicate = null); 79  80     /// <summary> 81     /// 执行SQL删除 82     /// </summary> 83     int DeleteBySql(string sql, params DbParameter[] para); 84  85     /// <summary> 86     /// 根据属性验证实体对象是否存在 87     /// </summary> 88     bool IsExist(Expression<Func<T, bool>> predicate); 89  90     /// <summary> 91     /// 根据SQL验证实体对象是否存在 92     /// </summary> 93     bool IsExist(string sql, params DbParameter[] para); 94     #endregion 95  96     #region 多模型操作 97     /// <summary> 98     /// 增加多模型数据,指定独立模型集合 99     /// </summary>100     int SaveList<T1>(List<T1> t) where T1 : class;101     /// <summary>102     /// 增加多模型数据,与当前模型一致103     /// </summary>104     int SaveList(List<T> t);105     /// <summary>106     /// 更新多模型,指定独立模型集合107     /// </summary>108     int UpdateList<T1>(List<T1> t) where T1 : class;109     /// <summary>110     /// 更新多模型,与当前模型一致111     /// </summary>112     int UpdateList(List<T> t);113     /// <summary>114     /// 批量删除数据,当前模型115     /// </summary>116     int DeleteList(List<T> t);117     /// <summary>118     /// 批量删除数据,独立模型119     /// </summary>120     int DeleteList<T1>(List<T1> t) where T1 : class;121     #endregion122 123     #region 存储过程操作124     /// <summary>125     /// 执行增删改存储过程126     /// </summary>127     object ExecuteProc(string procname, params DbParameter[] parameter);128     /// <summary>129     /// 执行查询的存储过程130     /// </summary>131     object ExecuteQueryProc(string procname, params DbParameter[] parameter);132     #endregion133 134     #region 查询多条数据135     /// <summary>136     /// 获取集合 IQueryable137     /// </summary>138     IQueryable<T> LoadAll(Expression<Func<T, bool>> predicate);139     /// <summary>140     /// 获取集合 IList141     /// </summary>142     List<T> LoadListAll(Expression<Func<T, bool>> predicate);143     /// <summary>144     /// 获取DbQuery的列表145     /// </summary>146     DbQuery<T> LoadQueryAll(Expression<Func<T, bool>> predicate);147     /// <summary>148     /// 获取IEnumerable列表149     /// </summary>150     IEnumerable<T> LoadEnumerableAll(string sql, params DbParameter[] para);151     /// <summary>152     /// 获取数据动态集合153     /// </summary>154     System.Collections.IEnumerable LoadEnumerable(string sql, params DbParameter[] para);155     /// <summary>156     /// 采用SQL进行数据的查询,并转换157     /// </summary>158     List<T> SelectBySql(string sql, params DbParameter[] para);159     List<T1> SelectBySql<T1>(string sql, params DbParameter[] para);160     /// <summary>161     /// 可指定返回结果、排序、查询条件的通用查询方法,返回实体对象162     /// </summary>163     /// <typeparam name="TEntity">实体对象</typeparam>164     /// <typeparam name="TOrderBy">排序字段类型</typeparam>165     /// <typeparam name="TResult">数据结果,一般为object</typeparam>166     /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>167     /// <param name="orderby">排序字段</param>168     /// <param name="selector">返回结果(必须是模型中存在的字段)</param>169     /// <param name="IsAsc">排序方向,true为正序false为倒序</param>170     /// <returns>实体集合</returns>171     List<TResult> QueryEntity<TEntity, TOrderBy, TResult>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Expression<Func<TEntity, TResult>> selector, bool IsAsc)172       where TEntity : class173       where TResult : class;174     /// <summary>175     /// 可指定返回结果、排序、查询条件的通用查询方法,返回Object对象176     /// </summary>177     /// <typeparam name="TEntity">实体对象</typeparam>178     /// <typeparam name="TOrderBy">排序字段类型</typeparam>179     /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>180     /// <param name="orderby">排序字段</param>181     /// <param name="selector">返回结果(必须是模型中存在的字段)</param>182     /// <param name="IsAsc">排序方向,true为正序false为倒序</param>183     /// <returns>自定义实体集合</returns>184     List<object> QueryObject<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc)185       where TEntity : class;186     /// <summary>187     /// 可指定返回结果、排序、查询条件的通用查询方法,返回动态类对象188     /// </summary>189     /// <typeparam name="TEntity">实体对象</typeparam>190     /// <typeparam name="TOrderBy">排序字段类型</typeparam>191     /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>192     /// <param name="orderby">排序字段</param>193     /// <param name="selector">返回结果(必须是模型中存在的字段)</param>194     /// <param name="IsAsc">排序方向,true为正序false为倒序</param>195     /// <returns>动态类对象</returns>196     dynamic QueryDynamic<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc)197       where TEntity : class;198     #endregion199 200     #region 分页查询201 202     /// <summary>203     /// 通过SQL分页204     /// </summary>205     /// <param name="sql"></param>206     /// <param name="parameters"></param>207     /// <param name="page"></param>208     /// <returns></returns>209     IList<T1> PageByListSql<T1>(string sql, IList<DbParameter> parameters, PageCollection page);210     IList<T> PageByListSql(string sql, IList<DbParameter> parameters, PageCollection page);211     /// <summary>212     /// 通用EF分页,默认显示20条记录213     /// </summary>214     /// <typeparam name="TEntity">实体模型</typeparam>215     /// <typeparam name="TOrderBy">排序类型</typeparam>216     /// <param name="index">当前页</param>217     /// <param name="pageSize">显示条数</param>218     /// <param name="where">过滤条件</param>219     /// <param name="orderby">排序字段</param>220     /// <param name="selector">结果集合</param>221     /// <param name="isAsc">排序方向true正序 false倒序</param>222     /// <returns>自定义实体集合</returns>223     PageInfo<object> Query<TEntity, TOrderBy>224       (int index, int pageSize,225       Expression<Func<TEntity, bool>> where,226       Expression<Func<TEntity, TOrderBy>> orderby,227       Func<IQueryable<TEntity>, List<object>> selector,228       bool IsAsc)229       where TEntity : class;230     /// <summary>231     /// 对IQueryable对象进行分页逻辑处理,过滤、查询项、排序对IQueryable操作232     /// </summary>233     /// <param name="t">Iqueryable</param>234     /// <param name="index">当前页</param>235     /// <param name="PageSize">每页显示多少条</param>236     /// <returns>当前IQueryable to List的对象</returns>237     Common.PageInfo<T> Query(IQueryable<T> query, int index, int PageSize);238     /// <summary>239     /// 普通SQL查询分页方法240     /// </summary>241     /// <param name="index">当前页</param>242     /// <param name="pageSize">显示行数</param>243     /// <param name="tableName">表名/视图</param>244     /// <param name="field">获取项</param>245     /// <param name="filter">过滤条件</param>246     /// <param name="orderby">排序字段+排序方向</param>247     /// <param name="group">分组字段</param>248     /// <returns>结果集</returns>249     Common.PageInfo Query(int index, int pageSize, string tableName, string field, string filter, string orderby, string group, params DbParameter[] para);250     /// <summary>251     /// 简单的Sql查询分页252     /// </summary>253     /// <param name="index"></param>254     /// <param name="pageSize"></param>255     /// <param name="sql"></param>256     /// <returns></returns>257     Common.PageInfo Query(int index, int pageSize, string sql, string orderby, params DbParameter[] para);258     /// <summary>259     /// 多表联合分页算法260     /// </summary>261     PageInfo Query(IQueryable query, int index, int pagesize);262     #endregion263 264     #region ADO.NET增删改查方法265     /// <summary>266     /// 执行增删改方法,含事务处理267     /// </summary>268     object ExecuteSqlCommand(string sql, params DbParameter[] para);269     /// <summary>270     /// 执行多条SQL,增删改方法,含事务处理271     /// </summary>272     object ExecuteSqlCommand(Dictionary<string, object> sqllist);273     /// <summary>274     /// 执行查询方法,返回动态类,接收使用var,遍历时使用dynamic类型275     /// </summary>276     object ExecuteSqlQuery(string sql, params DbParameter[] para);277     #endregion278   }279 }

View Code

 

 

注意:在分页查询中,我们引用了Common公共类库的一个公共类 PageCollection.cs

 

这个类库在C#公共帮助类里面  连接:【C#公共帮助类】 分页逻辑处理类

 

三、基础操作的实现类

我们在Service类库下新建一个公共基础数据操作类 RepositoryBase.cs 继承接口 IRepository

 

因为这个实现类呢,东西比较多,如果没有什么问题,大家可以直接拿来用,鉴于时间的关系(还在上班,大家理解~~),我就直接把代码贴出来了,这篇文章写完不是结束,我会抽空再回来简单介绍一下这个实现类的一些方法和原理,同时,如果网友提出了问题或是整改,我也会回来整理修改。

 

固定公用帮助,含事务

 

 1 #region 固定公用帮助,含事务 2  3     private DbContext context = new MyConfig().db; 4     /// <summary> 5     /// 数据上下文--->根据Domain实体模型名称进行更改 6     /// </summary> 7     public DbContext Context 8     { 9       get10       {11         context.Configuration.ValidateOnSaveEnabled = false;12         return context;13       }14     }15     /// <summary>16     /// 数据上下文--->拓展属性17     /// </summary>18     public MyConfig Config19     {20       get21       {22         return new MyConfig();23       }24     }25     /// <summary>26     /// 公用泛型处理属性27     /// 注:所有泛型操作的基础28     /// </summary>29     public DbSet<T> dbSet30     {31       get { return this.Context.Set<T>(); }32     }33     /// <summary>34     /// 事务35     /// </summary>36     private DbContextTransaction _transaction = null;37     /// <summary>38     /// 开始事务39     /// </summary>40     public DbContextTransaction Transaction41     {42       get43       {44         if (this._transaction == null)45         {46           this._transaction = this.Context.Database.BeginTransaction();47         }48         return this._transaction;49       }50       set { this._transaction = value; }51     }52     /// <summary>53     /// 事务状态54     /// </summary>55     public bool Committed { get; set; }56     /// <summary>57     /// 异步锁定58     /// </summary>59     private readonly object sync = new object();60     /// <summary>61     /// 提交事务62     /// </summary>63     public void Commit()64     {65       if (!Committed)66       {67         lock (sync)68         {69           if (this._transaction != null)70             _transaction.Commit();71         }72         Committed = true;73       }74     }75     /// <summary>76     /// 回滚事务77     /// </summary>78     public void Rollback()79     {80       Committed = false;81       if (this._transaction != null)82         this._transaction.Rollback();83     }84     #endregion

View Code

 

 

获取单条记录

 

 1 #region 获取单条记录 2     /// <summary> 3     /// 通过lambda表达式获取一条记录p=>p.id==id 4     /// </summary> 5     public virtual T Get(Expression<Func<T, bool>> predicate) 6     { 7       try 8       { 9         return dbSet.AsNoTracking().SingleOrDefault(predicate);10       }11       catch (Exception e)12       {13         throw e;14       }15     }16     #endregion

View Code

 

 

增删改操作

 

 1 #region 增删改操作 2  3     /// <summary> 4     /// 添加一条模型记录,自动提交更改 5     /// </summary> 6     public virtual bool Save(T entity) 7     { 8       try 9       {10         int row = 0;11         var entry = this.Context.Entry<T>(entity);12         entry.State = System.Data.Entity.EntityState.Added;13         row = Context.SaveChanges();14         entry.State = System.Data.Entity.EntityState.Detached;15         return row > 0;16       }17       catch (Exception e)18       {19         throw e;20       }21 22     }23 24     /// <summary>25     /// 更新一条模型记录,自动提交更改26     /// </summary>27     public virtual bool Update(T entity)28     {29       try30       {31         int rows = 0;32         var entry = this.Context.Entry(entity);33         entry.State = System.Data.Entity.EntityState.Modified;34         rows = this.Context.SaveChanges();35         entry.State = System.Data.Entity.EntityState.Detached;36         return rows > 0;37       }38       catch (Exception e)39       {40         throw e;41       }42     }43 44     /// <summary>45     /// 更新模型记录,如不存在进行添加操作46     /// </summary>47     public virtual bool SaveOrUpdate(T entity, bool isEdit)48     {49       try50       {51         return isEdit ? Update(entity) : Save(entity);52       }53       catch (Exception e) { throw e; }54     }55 56     /// <summary>57     /// 删除一条或多条模型记录,含事务58     /// </summary>59     public virtual int Delete(Expression<Func<T, bool>> predicate = null)60     {61       try62       {63         int rows = 0;64         IQueryable<T> entry = (predicate == null) ? this.dbSet.AsQueryable() : this.dbSet.Where(predicate);65         List<T> list = entry.ToList();66         if (list.Count > 0)67         {68           for (int i = 0; i < list.Count; i++)69           {70             this.dbSet.Remove(list[i]);71           }72           rows = this.Context.SaveChanges();73         }74         return rows;75       }76       catch (Exception e)77       {78         throw e;79       }80     }81     /// <summary>82     /// 使用原始SQL语句,含事务处理83     /// </summary>84     public virtual int DeleteBySql(string sql, params DbParameter[] para)85     {86       try87       {88         return this.Context.Database.ExecuteSqlCommand(sql, para);89       }90       catch (Exception e)91       {92         throw e;93       }94     }95     #endregion

View Code

 

 

多模型操作

 

 1 #region 多模型操作 2  3     /// <summary> 4     /// 增加多模型数据,指定独立模型集合 5     /// </summary> 6     public virtual int SaveList<T1>(List<T1> t) where T1 : class 7     { 8       try 9       { 10         if (t == null || t.Count == 0) return 0; 11         this.Context.Set<T1>().Local.Clear(); 12         foreach (var item in t) 13         { 14           this.Context.Set<T1>().Add(item); 15         } 16         return this.Context.SaveChanges(); 17       } 18       catch (Exception e) 19       { 20         throw e; 21       } 22     } 23     /// <summary> 24     /// 增加多模型数据,与当前模型一致 25     /// </summary> 26     public virtual int SaveList(List<T> t) 27     { 28       try 29       { 30         this.dbSet.Local.Clear(); 31         foreach (var item in t) 32         { 33           this.dbSet.Add(item); 34         } 35         return this.Context.SaveChanges(); 36       } 37       catch (Exception e) 38       { 39         throw e; 40       } 41     } 42     /// <summary> 43     /// 更新多模型,指定独立模型集合 44     /// </summary> 45     public virtual int UpdateList<T1>(List<T1> t) where T1 : class 46     { 47       if (t.Count <= 0) return 0; 48       try 49       { 50         foreach (var item in t) 51         { 52           this.Context.Entry<T1>(item).State = System.Data.Entity.EntityState.Modified; 53         } 54         return this.Context.SaveChanges(); 55       } 56       catch (Exception e) 57       { 58         throw e; 59       } 60     } 61     /// <summary> 62     /// 更新多模型,与当前模型一致 63     /// </summary> 64     public virtual int UpdateList(List<T> t) 65     { 66       if (t.Count <= 0) return 0; 67       try 68       { 69         foreach (var item in t) 70         { 71           this.Context.Entry(item).State = System.Data.Entity.EntityState.Modified; 72         } 73         return this.Context.SaveChanges(); 74       } 75       catch (Exception e) { throw e; } 76     } 77     /// <summary> 78     /// 批量删除数据,当前模型 79     /// </summary> 80     public virtual int DeleteList(List<T> t) 81     { 82       if (t == null || t.Count == 0) return 0; 83       foreach (var item in t) 84       { 85         this.dbSet.Remove(item); 86       } 87       return this.Context.SaveChanges(); 88     } 89     /// <summary> 90     /// 批量删除数据,自定义模型 91     /// </summary> 92     public virtual int DeleteList<T1>(List<T1> t) where T1 : class 93     { 94       try 95       { 96         if (t == null || t.Count == 0) return 0; 97         foreach (var item in t) 98         { 99           this.Context.Set<T1>().Remove(item);100         }101         return this.Context.SaveChanges();102       }103       catch (Exception e) { throw e; }104     }105     #endregion

View Code

 

 

存储过程操作

 

 1 #region 存储过程操作 2     /// <summary> 3     /// 执行返回影响行数的存储过程 4     /// </summary> 5     /// <param name="procname">过程名称</param> 6     /// <param name="parameter">参数对象</param> 7     /// <returns></returns> 8     public virtual object ExecuteProc(string procname, params DbParameter[] parameter) 9     {10       try11       {12         return ExecuteSqlCommand(procname, parameter);13       }14       catch (Exception e)15       {16         throw e;17       }18     }19     /// <summary>20     /// 执行返回结果集的存储过程21     /// </summary>22     /// <param name="procname">过程名称</param>23     /// <param name="parameter">参数对象</param>24     /// <returns></returns>25     public virtual object ExecuteQueryProc(string procname, params DbParameter[] parameter)26     {27       try28       {29         return this.Context.Database.SqlFunctionForDynamic(procname, parameter);30       }31       catch (Exception e)32       {33         throw e;34       }35     }36     #endregion

View Code

 

 

存在验证操作

 

 1 #region 存在验证操作 2     /// <summary> 3     /// 验证当前条件是否存在相同项 4     /// </summary> 5     public virtual bool IsExist(Expression<Func<T, bool>> predicate) 6     { 7       var entry = this.dbSet.Where(predicate); 8       return (entry.Any()); 9     }10 11     /// <summary>12     /// 根据SQL验证实体对象是否存在13     /// </summary>14     public virtual bool IsExist(string sql, params DbParameter[] para)15     {16       IEnumerable result = this.Context.Database.SqlQuery(typeof(int), sql, para);17 18       if (result.GetEnumerator().Current == null || result.GetEnumerator().Current.ToString() == "0")19         return false;20       return true;21     }22     #endregion

View Code

 

 

获取多条数据操作

 

 1 #region 获取多条数据操作 2     /// <summary> 3     /// 返回IQueryable集合,延时加载数据 4     /// </summary> 5     public virtual IQueryable<T> LoadAll(Expression<Func<T, bool>> predicate) 6     { 7       try 8       { 9         if (predicate != null) 10         { 11           return this.dbSet.Where(predicate).AsNoTracking<T>(); 12         } 13         return this.dbSet.AsQueryable<T>().AsNoTracking<T>(); 14       } 15       catch (Exception e) 16       { 17         throw e; 18       } 19     } 20     /// <summary> 21     /// 返回DbQuery集合,延时加载数据 22     /// </summary> 23     public virtual DbQuery<T> LoadQueryAll(Expression<Func<T, bool>> predicate) 24     { 25       try 26       { 27         if (predicate != null) 28         { 29           return this.dbSet.Where(predicate) as DbQuery<T>; 30         } 31         return this.dbSet; 32       } 33       catch (Exception e) 34       { 35         throw e; 36       } 37     } 38     /// <summary> 39     /// 返回List集合,不采用延时加载 40     /// </summary> 41     public virtual List<T> LoadListAll(Expression<Func<T, bool>> predicate) 42     { 43       try 44       { 45         if (predicate != null) 46         { 47           return this.dbSet.Where(predicate).AsNoTracking().ToList(); 48         } 49         return this.dbSet.AsQueryable<T>().AsNoTracking().ToList(); 50       } 51       catch (Exception e) 52       { 53         throw e; 54       } 55     } 56     /// <summary> 57     /// 返回IEnumerable集合,采用原始T-Sql方式 58     /// </summary> 59     public virtual IEnumerable<T> LoadEnumerableAll(string sql, params DbParameter[] para) 60     { 61       try 62       { 63         return this.Context.Database.SqlQuery<T>(sql, para); 64       } 65       catch (Exception e) 66       { 67         throw e; 68       } 69     } 70     /// <summary> 71     /// 返回IEnumerable动态集合,采用原始T-Sql方式 72     /// </summary> 73     public virtual System.Collections.IEnumerable LoadEnumerable(string sql, params DbParameter[] para) 74     { 75       try 76       { 77         return this.Context.Database.SqlQueryForDynamic(sql, para); 78       } 79       catch (Exception e) 80       { 81         throw e; 82       } 83     } 84     /// <summary> 85     /// 返回IList集合,采用原始T-Sql方式 86     /// </summary> 87     public virtual List<T> SelectBySql(string sql, params DbParameter[] para) 88     { 89       try 90       { 91         return this.Context.Database.SqlQuery(typeof(T), sql, para).Cast<T>().ToList(); 92       } 93       catch (Exception e) 94       { 95         throw e; 96       } 97     } 98     /// <summary> 99     /// 指定泛型,返回IList集合,采用原始T-Sql方式100     /// </summary>101     public virtual List<T1> SelectBySql<T1>(string sql, params DbParameter[] para)102     {103       try104       {105         return this.Context.Database.SqlQuery<T1>(sql, para).ToList();106       }107       catch (Exception e)108       {109         throw e;110       }111     }112     /// <summary>113     /// 可指定返回结果、排序、查询条件的通用查询方法,返回实体对象114     /// </summary>115     /// <typeparam name="TEntity">实体对象</typeparam>116     /// <typeparam name="TOrderBy">排序字段类型</typeparam>117     /// <typeparam name="TResult">数据结果,与TEntity一致</typeparam>118     /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>119     /// <param name="orderby">排序字段</param>120     /// <param name="selector">返回结果(必须是模型中存在的字段)</param>121     /// <param name="IsAsc">排序方向,true为正序false为倒序</param>122     /// <returns>实体集合</returns>123     public virtual List<TResult> QueryEntity<TEntity, TOrderBy, TResult>124       (Expression<Func<TEntity, bool>> where,125       Expression<Func<TEntity, TOrderBy>> orderby,126       Expression<Func<TEntity, TResult>> selector,127       bool IsAsc)128       where TEntity : class129       where TResult : class130     {131       IQueryable<TEntity> query = this.Context.Set<TEntity>();132       if (where != null)133       {134         query = query.Where(where);135       }136 137       if (orderby != null)138       {139         query = IsAsc ? query.OrderBy(orderby) : query.OrderByDescending(orderby);140       }141       if (selector == null)142       {143         return query.Cast<TResult>().AsNoTracking().ToList();144       }145       return query.Select(selector).AsNoTracking().ToList();146     }147 148     /// <summary>149     /// 可指定返回结果、排序、查询条件的通用查询方法,返回Object对象150     /// </summary>151     /// <typeparam name="TEntity">实体对象</typeparam>152     /// <typeparam name="TOrderBy">排序字段类型</typeparam>153     /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>154     /// <param name="orderby">排序字段</param>155     /// <param name="selector">返回结果(必须是模型中存在的字段)</param>156     /// <param name="IsAsc">排序方向,true为正序false为倒序</param>157     /// <returns>自定义实体集合</returns>158     public virtual List<object> QueryObject<TEntity, TOrderBy>159       (Expression<Func<TEntity, bool>> where,160       Expression<Func<TEntity, TOrderBy>> orderby,161       Func<IQueryable<TEntity>,162       List<object>> selector,163       bool IsAsc)164       where TEntity : class165     {166       IQueryable<TEntity> query = this.Context.Set<TEntity>();167       if (where != null)168       {169         query = query.Where(where);170       }171 172       if (orderby != null)173       {174         query = IsAsc ? query.OrderBy(orderby) : query.OrderByDescending(orderby);175       }176       if (selector == null)177       {178         return query.AsNoTracking().ToList<object>();179       }180       return selector(query);181     }182 183     /// <summary>184     /// 可指定返回结果、排序、查询条件的通用查询方法,返回动态类对象185     /// </summary>186     /// <typeparam name="TEntity">实体对象</typeparam>187     /// <typeparam name="TOrderBy">排序字段类型</typeparam>188     /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>189     /// <param name="orderby">排序字段</param>190     /// <param name="selector">返回结果(必须是模型中存在的字段)</param>191     /// <param name="IsAsc">排序方向,true为正序false为倒序</param>192     /// <returns>动态类</returns>193     public virtual dynamic QueryDynamic<TEntity, TOrderBy>194       (Expression<Func<TEntity, bool>> where,195       Expression<Func<TEntity, TOrderBy>> orderby,196       Func<IQueryable<TEntity>,197       List<object>> selector,198       bool IsAsc)199       where TEntity : class200     {201       List<object> list = QueryObject<TEntity, TOrderBy>202         (where, orderby, selector, IsAsc);203       return Common.JsonConverter.JsonClass(list);204     }205     #endregion

View Code

 

 

分页操作

 

 1 #region 分页操作 2     /// <summary> 3     /// 待自定义分页函数,使用必须重写,指定数据模型 4     /// </summary> 5     public virtual IList<T1> PageByListSql<T1>(string sql, IList<DbParameter> parameters, Common.PageCollection page) 6     { 7       return null; 8     } 9     /// <summary> 10     /// 待自定义分页函数,使用必须重写, 11     /// </summary> 12     public virtual IList<T> PageByListSql(string sql, IList<DbParameter> parameters, Common.PageCollection page) 13     { 14       return null; 15     } 16  17     /// <summary> 18     /// 对IQueryable对象进行分页逻辑处理,过滤、查询项、排序对IQueryable操作 19     /// </summary> 20     /// <param name="t">Iqueryable</param> 21     /// <param name="index">当前页</param> 22     /// <param name="PageSize">每页显示多少条</param> 23     /// <returns>当前IQueryable to List的对象</returns> 24     public virtual Common.PageInfo<T> Query(IQueryable<T> query, int index, int PageSize) 25     { 26       if (index < 1) 27       { 28         index = 1; 29       } 30       if (PageSize <= 0) 31       { 32         PageSize = 20; 33       } 34       int count = query.Count(); 35  36       int maxpage = count / PageSize; 37  38       if (count % PageSize > 0) 39       { 40         maxpage++; 41       } 42       if (index > maxpage) 43       { 44         index = maxpage; 45       } 46       if (count > 0) 47         query = query.Skip((index - 1) * PageSize).Take(PageSize); 48       return new Common.PageInfo<T>(index, PageSize, count, query.ToList()); 49     } 50     /// <summary> 51     /// 通用EF分页,默认显示20条记录 52     /// </summary> 53     /// <typeparam name="TEntity">实体模型</typeparam> 54     /// <typeparam name="TOrderBy">排序类型</typeparam> 55     /// <param name="index">当前页</param> 56     /// <param name="pageSize">显示条数</param> 57     /// <param name="where">过滤条件</param> 58     /// <param name="orderby">排序字段</param> 59     /// <param name="selector">结果集合</param> 60     /// <param name="isAsc">排序方向true正序 false倒序</param> 61     /// <returns>自定义实体集合</returns> 62     public virtual Common.PageInfo<object> Query<TEntity, TOrderBy> 63       (int index, int pageSize, 64       Expression<Func<TEntity, bool>> where, 65       Expression<Func<TEntity, TOrderBy>> orderby, 66       Func<IQueryable<TEntity>, 67       List<object>> selector, 68       bool isAsc) 69       where TEntity : class 70     { 71       if (index < 1) 72       { 73         index = 1; 74       } 75  76       if (pageSize <= 0) 77       { 78         pageSize = 20; 79       } 80  81       IQueryable<TEntity> query = this.Context.Set<TEntity>(); 82       if (where != null) 83       { 84         query = query.Where(where); 85       } 86       int count = query.Count(); 87  88       int maxpage = count / pageSize; 89  90       if (count % pageSize > 0) 91       { 92         maxpage++; 93       } 94       if (index > maxpage) 95       { 96         index = maxpage; 97       } 98  99       if (orderby != null)100       {101         query = isAsc ? query.OrderBy(orderby) : query.OrderByDescending(orderby);102       }103       if (count > 0)104         query = query.Skip((index - 1) * pageSize).Take(pageSize);105       //返回结果为null,返回所有字段106       if (selector == null)107         return new Common.PageInfo<object>(index, pageSize, count, query.ToList<object>());108       return new Common.PageInfo<object>(index, pageSize, count, selector(query).ToList());109     }110     /// <summary>111     /// 普通SQL查询分页方法112     /// </summary>113     /// <param name="index">当前页</param>114     /// <param name="pageSize">显示行数</param>115     /// <param name="tableName">表名/视图</param>116     /// <param name="field">获取项</param>117     /// <param name="filter">过滤条件</param>118     /// <param name="orderby">排序字段+排序方向</param>119     /// <param name="group">分组字段</param>120     /// <returns>结果集</returns>121     public virtual Common.PageInfo Query(int index, int pageSize, string tableName, string field, string filter, string orderby, string group, params DbParameter[] para)122     {123       //执行分页算法124       if (index <= 0)125         index = 1;126       int start = (index - 1) * pageSize;127       if (start > 0)128         start -= 1;129       else130         start = 0;131       int end = index * pageSize;132 133       #region 查询逻辑134       string logicSql = "SELECT";135       //查询项136       if (!string.IsNullOrEmpty(field))137       {138         logicSql += " " + field;139       }140       else141       {142         logicSql += " *";143       }144       logicSql += " FROM (" + tableName + " ) where";145       //过滤条件146       if (!string.IsNullOrEmpty(filter))147       {148         logicSql += " " + filter;149       }150       else151       {152         filter = " 1=1";153         logicSql += " 1=1";154       }155       //分组156       if (!string.IsNullOrEmpty(group))157       {158         logicSql += " group by " + group;159       }160 161       #endregion162 163       //获取当前条件下数据总条数164       int count = this.Context.Database.SqlQuery(typeof(int), "select count(*) from (" + tableName + ") where " + filter, para).Cast<int>().FirstOrDefault();165       string sql = "SELECT T.* FROM ( SELECT B.* FROM ( SELECT A.*,ROW_NUMBER() OVER(ORDER BY getdate()) as RN" +166             logicSql + ") A ) B WHERE B.RN<=" + end + ") T WHERE T.RN>" + start;167       //排序168       if (!string.IsNullOrEmpty(orderby))169       {170         sql += " order by " + orderby;171       }172       var list = ExecuteSqlQuery(sql, para) as IEnumerable;173       if (list != null)174         return new Common.PageInfo(index, pageSize, count, list.Cast<object>().ToList());175       return new Common.PageInfo(index, pageSize, count, new { });176     }177 178     /// <summary>179     /// 最简单的SQL分页180     /// </summary>181     /// <param name="index">页码</param>182     /// <param name="pageSize">显示行数</param>183     /// <param name="sql">纯SQL语句</param>184     /// <param name="orderby">排序字段与方向</param>185     /// <returns></returns>186     public virtual Common.PageInfo Query(int index, int pageSize, string sql, string orderby, params DbParameter[] para)187     {188       return this.Query(index, pageSize, sql, null, null, orderby, null, para);189     }190     /// <summary>191     /// 多表联合分页算法192     /// </summary>193     public virtual Common.PageInfo Query(IQueryable query, int index, int PageSize)194     {195       var enumerable = (query as System.Collections.IEnumerable).Cast<object>();196       if (index < 1)197       {198         index = 1;199       }200       if (PageSize <= 0)201       {202         PageSize = 20;203       }204 205       int count = enumerable.Count();206 207       int maxpage = count / PageSize;208 209       if (count % PageSize > 0)210       {211         maxpage++;212       }213       if (index > maxpage)214       {215         index = maxpage;216       }217       if (count > 0)218         enumerable = enumerable.Skip((index - 1) * PageSize).Take(PageSize);219       return new Common.PageInfo(index, PageSize, count, enumerable.ToList());220     }221     #endregion

View Code

 

 

ADO.NET增删改查方法

 

 1 #region ADO.NET增删改查方法 2     /// <summary> 3     /// 执行增删改方法,含事务处理 4     /// </summary> 5     public virtual object ExecuteSqlCommand(string sql, params DbParameter[] para) 6     { 7       try 8       { 9         return this.Context.Database.ExecuteSqlCommand(sql, para);10       }11       catch (Exception e)12       {13         throw e;14       }15 16     }17     /// <summary>18     /// 执行多条SQL,增删改方法,含事务处理19     /// </summary>20     public virtual object ExecuteSqlCommand(Dictionary<string, object> sqllist)21     {22       try23       {24         int rows = 0;25         IEnumerator<KeyValuePair<string, object>> enumerator = sqllist.GetEnumerator();26         using (Transaction)27         {28           while (enumerator.MoveNext())29           {30             rows += this.Context.Database.ExecuteSqlCommand(enumerator.Current.Key, enumerator.Current.Value);31           }32           Commit();33         }34         return rows;35       }36       catch (Exception e)37       {38         Rollback();39         throw e;40       }41 42     }43     /// <summary>44     /// 执行查询方法,返回动态类,接收使用var,遍历时使用dynamic类型45     /// </summary>46     public virtual object ExecuteSqlQuery(string sql, params DbParameter[] para)47     {48       try49       {50         return this.Context.Database.SqlQueryForDynamic(sql, para);51       }52       catch (Exception e)53       {54         throw e;55       }56     }57     #endregion

View Code

 

 

我们引用的类库和解决方案

 

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data; 6 using System.Data.Common; 7 using System.Data.Entity; 8 using System.Data.Entity.Infrastructure; 9 using Domain;10 using System.Linq.Expressions;11 using System.Collections;

View Code

 

 

完整的 RepositoryBase.cs

 

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data; 6 using System.Data.Common; 7 using System.Data.Entity; 8 using System.Data.Entity.Infrastructure; 9 using Domain; 10 using System.Linq.Expressions; 11 using System.Collections; 12  13 namespace Service 14 { 15   /// <summary> 16   /// 数据操作基本实现类,公用实现方法 17   /// add yuangang by 2016-05-10 18   /// </summary> 19   /// <typeparam name="T">具体操作的实体模型</typeparam> 20   public abstract class RepositoryBase<T> : IRepository<T> where T : class 21   { 22     #region 固定公用帮助,含事务 23  24     private DbContext context = new MyConfig().db; 25     /// <summary> 26     /// 数据上下文--->根据Domain实体模型名称进行更改 27     /// </summary> 28     public DbContext Context 29     { 30       get 31       { 32         context.Configuration.ValidateOnSaveEnabled = false; 33         return context; 34       } 35     } 36     /// <summary> 37     /// 数据上下文--->拓展属性 38     /// </summary> 39     public MyConfig Config 40     { 41       get 42       { 43         return new MyConfig(); 44       } 45     } 46     /// <summary> 47     /// 公用泛型处理属性 48     /// 注:所有泛型操作的基础 49     /// </summary> 50     public DbSet<T> dbSet 51     { 52       get { return this.Context.Set<T>(); } 53     } 54     /// <summary> 55     /// 事务 56     /// </summary> 57     private DbContextTransaction _transaction = null; 58     /// <summary> 59     /// 开始事务 60     /// </summary> 61     public DbContextTransaction Transaction 62     { 63       get 64       { 65         if (this._transaction == null) 66         { 67           this._transaction = this.Context.Database.BeginTransaction(); 68         } 69         return this._transaction; 70       } 71       set { this._transaction = value; } 72     } 73     /// <summary> 74     /// 事务状态 75     /// </summary> 76     public bool Committed { get; set; } 77     /// <summary> 78     /// 异步锁定 79     /// </summary> 80     private readonly object sync = new object(); 81     /// <summary> 82     /// 提交事务 83     /// </summary> 84     public void Commit() 85     { 86       if (!Committed) 87       { 88         lock (sync) 89         { 90           if (this._transaction != null) 91             _transaction.Commit(); 92         } 93         Committed = true; 94       } 95     } 96     /// <summary> 97     /// 回滚事务 98     /// </summary> 99     public void Rollback()100     {101       Committed = false;102       if (this._transaction != null)103         this._transaction.Rollback();104     }105     #endregion106 107     #region 获取单条记录108     /// <summary>109     /// 通过lambda表达式获取一条记录p=>p.id==id110     /// </summary>111     public virtual T Get(Expression<Func<T, bool>> predicate)112     {113       try114       {115         return dbSet.AsNoTracking().SingleOrDefault(predicate);116       }117       catch (Exception e)118       {119         throw e;120       }121     }122     #endregion123 124     #region 增删改操作125 126     /// <summary>127     /// 添加一条模型记录,自动提交更改128     /// </summary>129     public virtual bool Save(T entity)130     {131       try132       {133         int row = 0;134         var entry = this.Context.Entry<T>(entity);135         entry.State = System.Data.Entity.EntityState.Added;136         row = Context.SaveChanges();137         entry.State = System.Data.Entity.EntityState.Detached;138         return row > 0;139       }140       catch (Exception e)141       {142         throw e;143       }144 145     }146 147     /// <summary>148     /// 更新一条模型记录,自动提交更改149     /// </summary>150     public virtual bool Update(T entity)151     {152       try153       {154         int rows = 0;155         var entry = this.Context.Entry(entity);156         entry.State = System.Data.Entity.EntityState.Modified;157         rows = this.Context.SaveChanges();158         entry.State = System.Data.Entity.EntityState.Detached;159         return rows > 0;160       }161       catch (Exception e)162       {163         throw e;164       }165     }166 167     /// <summary>168     /// 更新模型记录,如不存在进行添加操作169     /// </summary>170     public virtual bool SaveOrUpdate(T entity, bool isEdit)171     {172       try173       {174         return isEdit ? Update(entity) : Save(entity);175       }176       catch (Exception e) { throw e; }177     }178 179     /// <summary>180     /// 删除一条或多条模型记录,含事务181     /// </summary>182     public virtual int Delete(Expression<Func<T, bool>> predicate = null)183     {184       try185       {186         int rows = 0;187         IQueryable<T> entry = (predicate == null) ? this.dbSet.AsQueryable() : this.dbSet.Where(predicate);188         List<T> list = entry.ToList();189         if (list.Count > 0)190         {191           for (int i = 0; i < list.Count; i++)192           {193             this.dbSet.Remove(list[i]);194           }195           rows = this.Context.SaveChanges();196         }197         return rows;198       }199       catch (Exception e)200       {201         throw e;202       }203     }204     /// <summary>205     /// 使用原始SQL语句,含事务处理206     /// </summary>207     public virtual int DeleteBySql(string sql, params DbParameter[] para)208     {209       try210       {211         return this.Context.Database.ExecuteSqlCommand(sql, para);212       }213       catch (Exception e)214       {215         throw e;216       }217     }218     #endregion219 220     #region 多模型操作221 222     /// <summary>223     /// 增加多模型数据,指定独立模型集合224     /// </summary>225     public virtual int SaveList<T1>(List<T1> t) where T1 : class226     {227       try228       {229         if (t == null || t.Count == 0) return 0;230         this.Context.Set<T1>().Local.Clear();231         foreach (var item in t)232         {233           this.Context.Set<T1>().Add(item);234         }235         return this.Context.SaveChanges();236       }237       catch (Exception e)238       {239         throw e;240       }241     }242     /// <summary>243     /// 增加多模型数据,与当前模型一致244     /// </summary>245     public virtual int SaveList(List<T> t)246     {247       try248       {249         this.dbSet.Local.Clear();250         foreach (var item in t)251         {252           this.dbSet.Add(item);253         }254         return this.Context.SaveChanges();255       }256       catch (Exception e)257       {258         throw e;259       }260     }261     /// <summary>262     /// 更新多模型,指定独立模型集合263     /// </summary>264     public virtual int UpdateList<T1>(List<T1> t) where T1 : class265     {266       if (t.Count <= 0) return 0;267       try268       {269         foreach (var item in t)270         {271           this.Context.Entry<T1>(item).State = System.Data.Entity.EntityState.Modified;272         }273         return this.Context.SaveChanges();274       }275       catch (Exception e)276       {277         throw e;278       }279     }280     /// <summary>281     /// 更新多模型,与当前模型一致282     /// </summary>283     public virtual int UpdateList(List<T> t)284     {285       if (t.Count <= 0) return 0;286       try287       {288         foreach (var item in t)289         {290           this.Context.Entry(item).State = System.Data.Entity.EntityState.Modified;291         }292         return this.Context.SaveChanges();293       }294       catch (Exception e) { throw e; }295     }296     /// <summary>297     /// 批量删除数据,当前模型298     /// </summary>299     public virtual int DeleteList(List<T> t)300     {301       if (t == null || t.Count == 0) return 0;302       foreach (var item in t)303       {304         this.dbSet.Remove(item);305       }306       return this.Context.SaveChanges();307     }308     /// <summary>309     /// 批量删除数据,自定义模型310     /// </summary>311     public virtual int DeleteList<T1>(List<T1> t) where T1 : class312     {313       try314       {315         if (t == null || t.Count == 0) return 0;316         foreach (var item in t)317         {318           this.Context.Set<T1>().Remove(item);319         }320         return this.Context.SaveChanges();321       }322       catch (Exception e) { throw e; }323     }324     #endregion325 326     #region 存储过程操作327     /// <summary>328     /// 执行返回影响行数的存储过程329     /// </summary>330     /// <param name="procname">过程名称</param>331     /// <param name="parameter">参数对象</param>332     /// <returns></returns>333     public virtual object ExecuteProc(string procname, params DbParameter[] parameter)334     {335       try336       {337         return ExecuteSqlCommand(procname, parameter);338       }339       catch (Exception e)340       {341         throw e;342       }343     }344     /// <summary>345     /// 执行返回结果集的存储过程346     /// </summary>347     /// <param name="procname">过程名称</param>348     /// <param name="parameter">参数对象</param>349     /// <returns></returns>350     public virtual object ExecuteQueryProc(string procname, params DbParameter[] parameter)351     {352       try353       {354         return this.Context.Database.SqlFunctionForDynamic(procname, parameter);355       }356       catch (Exception e)357       {358         throw e;359       }360     }361     #endregion362 363     #region 存在验证操作364     /// <summary>365     /// 验证当前条件是否存在相同项366     /// </summary>367     public virtual bool IsExist(Expression<Func<T, bool>> predicate)368     {369       var entry = this.dbSet.Where(predicate);370       return (entry.Any());371     }372 373     /// <summary>374     /// 根据SQL验证实体对象是否存在375     /// </summary>376     public virtual bool IsExist(string sql, params DbParameter[] para)377     {378       IEnumerable result = this.Context.Database.SqlQuery(typeof(int), sql, para);379 380       if (result.GetEnumerator().Current == null || result.GetEnumerator().Current.ToString() == "0")381         return false;382       return true;383     }384     #endregion385 386     #region 获取多条数据操作387     /// <summary>388     /// 返回IQueryable集合,延时加载数据389     /// </summary>390     public virtual IQueryable<T> LoadAll(Expression<Func<T, bool>> predicate)391     {392       try393       {394         if (predicate != null)395         {396           return this.dbSet.Where(predicate).AsNoTracking<T>();397         }398         return this.dbSet.AsQueryable<T>().AsNoTracking<T>();399       }400       catch (Exception e)401       {402         throw e;403       }404     }405     /// <summary>406     /// 返回DbQuery集合,延时加载数据407     /// </summary>408     public virtual DbQuery<T> LoadQueryAll(Expression<Func<T, bool>> predicate)409     {410       try411       {412         if (predicate != null)413         {414           return this.dbSet.Where(predicate) as DbQuery<T>;415         }416         return this.dbSet;417       }418       catch (Exception e)419       {420         throw e;421       }422     }423     /// <summary>424     /// 返回List集合,不采用延时加载425     /// </summary>426     public virtual List<T> LoadListAll(Expression<Func<T, bool>> predicate)427     {428       try429       {430         if (predicate != null)431         {432           return this.dbSet.Where(predicate).AsNoTracking().ToList();433         }434         return this.dbSet.AsQueryable<T>().AsNoTracking().ToList();435       }436       catch (Exception e)437       {438         throw e;439       }440     }441     /// <summary>442     /// 返回IEnumerable集合,采用原始T-Sql方式443     /// </summary>444     public virtual IEnumerable<T> LoadEnumerableAll(string sql, params DbParameter[] para)445     {446       try447       {448         return this.Context.Database.SqlQuery<T>(sql, para);449       }450       catch (Exception e)451       {452         throw e;453       }454     }455     /// <summary>456     /// 返回IEnumerable动态集合,采用原始T-Sql方式457     /// </summary>458     public virtual System.Collections.IEnumerable LoadEnumerable(string sql, params DbParameter[] para)459     {460       try461       {462         return this.Context.Database.SqlQueryForDynamic(sql, para);463       }464       catch (Exception e)465       {466         throw e;467       }468     }469     /// <summary>470     /// 返回IList集合,采用原始T-Sql方式471     /// </summary>472     public virtual List<T> SelectBySql(string sql, params DbParameter[] para)473     {474       try475       {476         return this.Context.Database.SqlQuery(typeof(T), sql, para).Cast<T>().ToList();477       }478       catch (Exception e)479       {480         throw e;481       }482     }483     /// <summary>484     /// 指定泛型,返回IList集合,采用原始T-Sql方式485     /// </summary>486     public virtual List<T1> SelectBySql<T1>(string sql, params DbParameter[] para)487     {488       try489       {490         return this.Context.Database.SqlQuery<T1>(sql, para).ToList();491       }492       catch (Exception e)493       {494         throw e;495       }496     }497     /// <summary>498     /// 可指定返回结果、排序、查询条件的通用查询方法,返回实体对象499     /// </summary>500     /// <typeparam name="TEntity">实体对象</typeparam>501     /// <typeparam name="TOrderBy">排序字段类型</typeparam>502     /// <typeparam name="TResult">数据结果,与TEntity一致</typeparam>503     /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>504     /// <param name="orderby">排序字段</param>505     /// <param name="selector">返回结果(必须是模型中存在的字段)</param>506     /// <param name="IsAsc">排序方向,true为正序false为倒序</param>507     /// <returns>实体集合</returns>508     public virtual List<TResult> QueryEntity<TEntity, TOrderBy, TResult>509       (Expression<Func<TEntity, bool>> where,510       Expression<Func<TEntity, TOrderBy>> orderby,511       Expression<Func<TEntity, TResult>> selector,512       bool IsAsc)513       where TEntity : class514       where TResult : class515     {516       IQueryable<TEntity> query = this.Context.Set<TEntity>();517       if (where != null)518       {519         query = query.Where(where);520       }521 522       if (orderby != null)523       {524         query = IsAsc ? query.OrderBy(orderby) : query.OrderByDescending(orderby);525       }526       if (selector == null)527       {528         return query.Cast<TResult>().AsNoTracking().ToList();529       }530       return query.Select(selector).AsNoTracking().ToList();531     }532 533     /// <summary>534     /// 可指定返回结果、排序、查询条件的通用查询方法,返回Object对象535     /// </summary>536     /// <typeparam name="TEntity">实体对象</typeparam>537     /// <typeparam name="TOrderBy">排序字段类型</typeparam>538     /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>539     /// <param name="orderby">排序字段</param>540     /// <param name="selector">返回结果(必须是模型中存在的字段)</param>541     /// <param name="IsAsc">排序方向,true为正序false为倒序</param>542     /// <returns>自定义实体集合</returns>543     public virtual List<object> QueryObject<TEntity, TOrderBy>544       (Expression<Func<TEntity, bool>> where,545       Expression<Func<TEntity, TOrderBy>> orderby,546       Func<IQueryable<TEntity>,547       List<object>> selector,548       bool IsAsc)549       where TEntity : class550     {551       IQueryable<TEntity> query = this.Context.Set<TEntity>();552       if (where != null)553       {554         query = query.Where(where);555       }556 557       if (orderby != null)558       {559         query = IsAsc ? query.OrderBy(orderby) : query.OrderByDescending(orderby);560       }561       if (selector == null)562       {563         return query.AsNoTracking().ToList<object>();564       }565       return selector(query);566     }567 568     /// <summary>569     /// 可指定返回结果、排序、查询条件的通用查询方法,返回动态类对象570     /// </summary>571     /// <typeparam name="TEntity">实体对象</typeparam>572     /// <typeparam name="TOrderBy">排序字段类型</typeparam>573     /// <param name="where">过滤条件,需要用到类型转换的需要提前处理与数据表一致的</param>574     /// <param name="orderby">排序字段</param>575     /// <param name="selector">返回结果(必须是模型中存在的字段)</param>576     /// <param name="IsAsc">排序方向,true为正序false为倒序</param>577     /// <returns>动态类</returns>578     public virtual dynamic QueryDynamic<TEntity, TOrderBy>579       (Expression<Func<TEntity, bool>> where,580       Expression<Func<TEntity, TOrderBy>> orderby,581       Func<IQueryable<TEntity>,582       List<object>> selector,583       bool IsAsc)584       where TEntity : class585     {586       List<object> list = QueryObject<TEntity, TOrderBy>587         (where, orderby, selector, IsAsc);588       return Common.JsonConverter.JsonClass(list);589     }590     #endregion591 592     #region 分页操作593     /// <summary>594     /// 待自定义分页函数,使用必须重写,指定数据模型595     /// </summary>596     public virtual IList<T1> PageByListSql<T1>(string sql, IList<DbParameter> parameters, Common.PageCollection page)597     {598       return null;599     }600     /// <summary>601     /// 待自定义分页函数,使用必须重写,602     /// </summary>603     public virtual IList<T> PageByListSql(string sql, IList<DbParameter> parameters, Common.PageCollection page)604     {605       return null;606     }607 608     /// <summary>609     /// 对IQueryable对象进行分页逻辑处理,过滤、查询项、排序对IQueryable操作610     /// </summary>611     /// <param name="t">Iqueryable</param>612     /// <param name="index">当前页</param>613     /// <param name="PageSize">每页显示多少条</param>614     /// <returns>当前IQueryable to List的对象</returns>615     public virtual Common.PageInfo<T> Query(IQueryable<T> query, int index, int PageSize)616     {617       if (index < 1)618       {619         index = 1;620       }621       if (PageSize <= 0)622       {623         PageSize = 20;624       }625       int count = query.Count();626 627       int maxpage = count / PageSize;628 629       if (count % PageSize > 0)630       {631         maxpage++;632       }633       if (index > maxpage)634       {635         index = maxpage;636       }637       if (count > 0)638         query = query.Skip((index - 1) * PageSize).Take(PageSize);639       return new Common.PageInfo<T>(index, PageSize, count, query.ToList());640     }641     /// <summary>642     /// 通用EF分页,默认显示20条记录643     /// </summary>644     /// <typeparam name="TEntity">实体模型</typeparam>645     /// <typeparam name="TOrderBy">排序类型</typeparam>646     /// <param name="index">当前页</param>647     /// <param name="pageSize">显示条数</param>648     /// <param name="where">过滤条件</param>649     /// <param name="orderby">排序字段</param>650     /// <param name="selector">结果集合</param>651     /// <param name="isAsc">排序方向true正序 false倒序</param>652     /// <returns>自定义实体集合</returns>653     public virtual Common.PageInfo<object> Query<TEntity, TOrderBy>654       (int index, int pageSize,655       Expression<Func<TEntity, bool>> where,656       Expression<Func<TEntity, TOrderBy>> orderby,657       Func<IQueryable<TEntity>,658       List<object>> selector,659       bool isAsc)660       where TEntity : class661     {662       if (index < 1)663       {664         index = 1;665       }666 667       if (pageSize <= 0)668       {669         pageSize = 20;670       }671 672       IQueryable<TEntity> query = this.Context.Set<TEntity>();673       if (where != null)674       {675         query = query.Where(where);676       }677       int count = query.Count();678 679       int maxpage = count / pageSize;680 681       if (count % pageSize > 0)682       {683         maxpage++;684       }685       if (index > maxpage)686       {687         index = maxpage;688       }689 690       if (orderby != null)691       {692         query = isAsc ? query.OrderBy(orderby) : query.OrderByDescending(orderby);693       }694       if (count > 0)695         query = query.Skip((index - 1) * pageSize).Take(pageSize);696       //返回结果为null,返回所有字段697       if (selector == null)698         return new Common.PageInfo<object>(index, pageSize, count, query.ToList<object>());699       return new Common.PageInfo<object>(index, pageSize, count, selector(query).ToList());700     }701     /// <summary>702     /// 普通SQL查询分页方法703     /// </summary>704     /// <param name="index">当前页</param>705     /// <param name="pageSize">显示行数</param>706     /// <param name="tableName">表名/视图</param>707     /// <param name="field">获取项</param>708     /// <param name="filter">过滤条件</param>709     /// <param name="orderby">排序字段+排序方向</param>710     /// <param name="group">分组字段</param>711     /// <returns>结果集</returns>712     public virtual Common.PageInfo Query(int index, int pageSize, string tableName, string field, string filter, string orderby, string group, params DbParameter[] para)713     {714       //执行分页算法715       if (index <= 0)716         index = 1;717       int start = (index - 1) * pageSize;718       if (start > 0)719         start -= 1;720       else721         start = 0;722       int end = index * pageSize;723 724       #region 查询逻辑725       string logicSql = "SELECT";726       //查询项727       if (!string.IsNullOrEmpty(field))728       {729         logicSql += " " + field;730       }731       else732       {733         logicSql += " *";734       }735       logicSql += " FROM (" + tableName + " ) where";736       //过滤条件737       if (!string.IsNullOrEmpty(filter))738       {739         logicSql += " " + filter;740       }741       else742       {743         filter = " 1=1";744         logicSql += " 1=1";745       }746       //分组747       if (!string.IsNullOrEmpty(group))748       {749         logicSql += " group by " + group;750       }751 752       #endregion753 754       //获取当前条件下数据总条数755       int count = this.Context.Database.SqlQuery(typeof(int), "select count(*) from (" + tableName + ") where " + filter, para).Cast<int>().FirstOrDefault();756       string sql = "SELECT T.* FROM ( SELECT B.* FROM ( SELECT A.*,ROW_NUMBER() OVER(ORDER BY getdate()) as RN" +757             logicSql + ") A ) B WHERE B.RN<=" + end + ") T WHERE T.RN>" + start;758       //排序759       if (!string.IsNullOrEmpty(orderby))760       {761         sql += " order by " + orderby;762       }763       var list = ExecuteSqlQuery(sql, para) as IEnumerable;764       if (list != null)765         return new Common.PageInfo(index, pageSize, count, list.Cast<object>().ToList());766       return new Common.PageInfo(index, pageSize, count, new { });767     }768 769     /// <summary>770     /// 最简单的SQL分页771     /// </summary>772     /// <param name="index">页码</param>773     /// <param name="pageSize">显示行数</param>774     /// <param name="sql">纯SQL语句</param>775     /// <param name="orderby">排序字段与方向</param>776     /// <returns></returns>777     public virtual Common.PageInfo Query(int index, int pageSize, string sql, string orderby, params DbParameter[] para)778     {779       return this.Query(index, pageSize, sql, null, null, orderby, null, para);780     }781     /// <summary>782     /// 多表联合分页算法783     /// </summary>784     public virtual Common.PageInfo Query(IQueryable query, int index, int PageSize)785     {786       var enumerable = (query as System.Collections.IEnumerable).Cast<object>();787       if (index < 1)788       {789         index = 1;790       }791       if (PageSize <= 0)792       {793         PageSize = 20;794       }795 796       int count = enumerable.Count();797 798       int maxpage = count / PageSize;799 800       if (count % PageSize > 0)801       {802         maxpage++;803       }804       if (index > maxpage)805       {806         index = maxpage;807       }808       if (count > 0)809         enumerable = enumerable.Skip((index - 1) * PageSize).Take(PageSize);810       return new Common.PageInfo(index, PageSize, count, enumerable.ToList());811     }812     #endregion813 814     #region ADO.NET增删改查方法815     /// <summary>816     /// 执行增删改方法,含事务处理817     /// </summary>818     public virtual object ExecuteSqlCommand(string sql, params DbParameter[] para)819     {820       try821       {822         return this.Context.Database.ExecuteSqlCommand(sql, para);823       }824       catch (Exception e)825       {826         throw e;827       }828 829     }830     /// <summary>831     /// 执行多条SQL,增删改方法,含事务处理832     /// </summary>833     public virtual object ExecuteSqlCommand(Dictionary<string, object> sqllist)834     {835       try836       {837         int rows = 0;838         IEnumerator<KeyValuePair<string, object>> enumerator = sqllist.GetEnumerator();839         using (Transaction)840         {841           while (enumerator.MoveNext())842           {843             rows += this.Context.Database.ExecuteSqlCommand(enumerator.Current.Key, enumerator.Current.Value);844           }845           Commit();846         }847         return rows;848       }849       catch (Exception e)850       {851         Rollback();852         throw e;853       }854 855     }856     /// <summary>857     /// 执行查询方法,返回动态类,接收使用var,遍历时使用dynamic类型858     /// </summary>859     public virtual object ExecuteSqlQuery(string sql, params DbParameter[] para)860     {861       try862       {863         return this.Context.Database.SqlQueryForDynamic(sql, para);864       }865       catch (Exception e)866       {867         throw e;868       }869     }870     #endregion871   }872 }

View Code

 

 

需要用的Common公共帮助类

这写类库,大家去【C#公共帮助类】找一下。

 

原创文章 转载请尊重劳动成果 http://yuangang.cnblogs.com