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

索引

【无私分享:从入门到精通ASP.NET MVC】从0开始,一起搭框架、做项目 目录索引

简述

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

项目准备

我们用的工具是:VS 2013 + SqlServer 2012 + IIS7.5

希望大家对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 : class
40             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         #endregion
122 
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         #endregion
133 
134         #region 查询多条数据
135         /// <summary>
136         /// 获取集合 IQueryable
137         /// </summary>
138         IQueryable<T> LoadAll(Expression<Func<T, bool>> predicate);
139         /// <summary>
140         /// 获取集合 IList
141         /// </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 : class
173             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         #endregion
199 
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         #endregion
263 
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         #endregion
278     }
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             get
10             {
11                 context.Configuration.ValidateOnSaveEnabled = false;
12                 return context;
13             }
14         }
15         /// <summary>
16         /// 数据上下文--->拓展属性
17         /// </summary>
18         public MyConfig Config
19         {
20             get
21             {
22                 return new MyConfig();
23             }
24         }
25         /// <summary>
26         /// 公用泛型处理属性
27         /// 注:所有泛型操作的基础
28         /// </summary>
29         public DbSet<T> dbSet
30         {
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 Transaction
41         {
42             get
43             {
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             try
30             {
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             try
50             {
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             try
62             {
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             try
87             {
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             try
11             {
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             try
28             {
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             try
104             {
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 : class
129             where TResult : class
130         {
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 : class
165         {
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 : class
200         {
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             else
130                 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             else
141             {
142                 logicSql += " *";
143             }
144             logicSql += " FROM (" + tableName + " ) where";
145             //过滤条件
146             if (!string.IsNullOrEmpty(filter))
147             {
148                 logicSql += " " + filter;
149             }
150             else
151             {
152                 filter = " 1=1";
153                 logicSql += "  1=1";
154             }
155             //分组
156             if (!string.IsNullOrEmpty(group))
157             {
158                 logicSql += " group by " + group;
159             }
160 
161             #endregion
162 
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             try
23             {
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             try
49             {
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         #endregion
106 
107         #region 获取单条记录
108         /// <summary>
109         /// 通过lambda表达式获取一条记录p=>p.id==id
110         /// </summary>
111         public virtual T Get(Expression<Func<T, bool>> predicate)
112         {
113             try
114             {
115                 return dbSet.AsNoTracking().SingleOrDefault(predicate);
116             }
117             catch (Exception e)
118             {
119                 throw e;
120             }
121         }
122         #endregion
123 
124         #region 增删改操作
125 
126         /// <summary>
127         /// 添加一条模型记录,自动提交更改
128         /// </summary>
129         public virtual bool Save(T entity)
130         {
131             try
132             {
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             try
153             {
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             try
173             {
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             try
185             {
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             try
210             {
211                 return this.Context.Database.ExecuteSqlCommand(sql, para);
212             }
213             catch (Exception e)
214             {
215                 throw e;
216             }
217         }
218         #endregion
219 
220         #region 多模型操作
221 
222         /// <summary>
223         /// 增加多模型数据,指定独立模型集合
224         /// </summary>
225         public virtual int SaveList<T1>(List<T1> t) where T1 : class
226         {
227             try
228             {
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             try
248             {
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 : class
265         {
266             if (t.Count <= 0) return 0;
267             try
268             {
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             try
287             {
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 : class
312         {
313             try
314             {
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         #endregion
325 
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             try
336             {
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             try
353             {
354                 return this.Context.Database.SqlFunctionForDynamic(procname, parameter);
355             }
356             catch (Exception e)
357             {
358                 throw e;
359             }
360         }
361         #endregion
362 
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         #endregion
385 
386         #region 获取多条数据操作
387         /// <summary>
388         /// 返回IQueryable集合,延时加载数据
389         /// </summary>
390         public virtual IQueryable<T> LoadAll(Expression<Func<T, bool>> predicate)
391         {
392             try
393             {
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             try
411             {
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             try
429             {
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             try
447             {
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             try
461             {
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             try
475             {
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             try
489             {
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 : class
514             where TResult : class
515         {
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 : class
550         {
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 : class
585         {
586             List<object> list = QueryObject<TEntity, TOrderBy>
587                  (where, orderby, selector, IsAsc);
588             return Common.JsonConverter.JsonClass(list);
589         }
590         #endregion
591 
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 : class
661         {
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             else
721                 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             else
732             {
733                 logicSql += " *";
734             }
735             logicSql += " FROM (" + tableName + " ) where";
736             //过滤条件
737             if (!string.IsNullOrEmpty(filter))
738             {
739                 logicSql += " " + filter;
740             }
741             else
742             {
743                 filter = " 1=1";
744                 logicSql += "  1=1";
745             }
746             //分组
747             if (!string.IsNullOrEmpty(group))
748             {
749                 logicSql += " group by " + group;
750             }
751 
752             #endregion
753 
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         #endregion
813 
814         #region ADO.NET增删改查方法
815         /// <summary>
816         /// 执行增删改方法,含事务处理
817         /// </summary>
818         public virtual object ExecuteSqlCommand(string sql, params DbParameter[] para)
819         {
820             try
821             {
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             try
836             {
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             try
862             {
863                 return this.Context.Database.SqlQueryForDynamic(sql, para);
864             }
865             catch (Exception e)
866             {
867                 throw e;
868             }
869         }
870         #endregion
871     }
872 }
View Code

 

 

需要用的Common公共帮助类

这写类库,大家去【C#公共帮助类】找一下。有些公共帮助类,我还没来得及发布,请大家耐心等待一下哈~~~理解、理解~~

PS1:大家的实现类 是不是找不到SqlFunctionForDynamic 这个方法,这是我的失误,因为刚才有点工作的事,忘记了,现在补充上

我们在Service类库下面新建一个 查询动态类 DatabaseExtensions

 

 

代码如下:添加上这个类之后 那个方法就有了

 

  1 using System;
  2 using System.Collections;
  3 using System.Collections.Generic;
  4 using System.Configuration;
  5 using System.Data;
  6 using System.Data.Entity;
  7 using System.Linq;
  8 using System.Reflection;
  9 using System.Reflection.Emit;
 10 using System.Text;
 11 
 12 namespace Service
 13 {
 14     /// <summary>
 15     /// 查询动态类
 16     /// add yuangang by 2016-05-10
 17     /// </summary>
 18     public static class DatabaseExtensions
 19     {
 20         /// <summary>
 21         /// 自定义Connection对象
 22         /// </summary>
 23         private static IDbConnection DefaultConnection 
 24         {
 25             get
 26             {
 27                 return Domain.MyConfig.DefaultConnection;
 28             }
 29         }
 30         /// <summary>
 31         /// 自定义数据库连接字符串,与EF连接模式一致
 32         /// </summary>
 33         private static string DefaultConnectionString 
 34         {
 35             get 
 36             {
 37                 return Domain.MyConfig.DefaultConnectionString;
 38             }
 39         }
 40         /// <summary>
 41         /// 动态查询主方法
 42         /// </summary>
 43         /// <returns></returns>
 44         public static IEnumerable SqlQueryForDynamic(this Database db,
 45                 string sql,
 46                 params object[] parameters)
 47         {
 48             IDbConnection defaultConn = DefaultConnection;
 49 
 50             //ADO.NET数据库连接字符串
 51             db.Connection.ConnectionString = DefaultConnectionString;
 52 
 53             return SqlQueryForDynamicOtherDB(db, sql, defaultConn, parameters);
 54         }
 55         private static IEnumerable SqlQueryForDynamicOtherDB(this Database db,  string sql, IDbConnection conn, params object[] parameters)
 56         {
 57             conn.ConnectionString = db.Connection.ConnectionString;
 58 
 59             if (conn.State != ConnectionState.Open)
 60             {
 61                 conn.Open();
 62             }
 63 
 64             IDbCommand cmd = conn.CreateCommand();
 65             cmd.CommandText = sql;
 66             if (parameters != null)
 67             {
 68                 foreach (var item in parameters)
 69                 {
 70                     cmd.Parameters.Add(item);
 71                 }
 72             }
 73 
 74             using (IDataReader dataReader = cmd.ExecuteReader())
 75             {
 76 
 77                 if (!dataReader.Read())
 78                 {
 79                     return null; //无结果返回Null
 80                 }
 81 
 82                 #region 构建动态字段
 83 
 84                 TypeBuilder builder = DatabaseExtensions.CreateTypeBuilder(
 85                     "EF_DynamicModelAssembly",
 86                     "DynamicModule",
 87                     "DynamicType");
 88 
 89                 int fieldCount = dataReader.FieldCount;
 90                 for (int i = 0; i < fieldCount; i++)
 91                 {
 92                     Type t = dataReader.GetFieldType(i);
 93                     switch (t.Name.ToLower())
 94                     {
 95                         case "decimal":
 96                             t = typeof(Decimal?);
 97                             break;
 98                         case "double":
 99                             t = typeof(Double?);
100                             break;
101                         case "datetime":
102                             t = typeof(DateTime?);
103                             break;
104                         case "single":
105                             t = typeof(float?);
106                             break;
107                         case "int16":
108                             t = typeof(int?);
109                             break;
110                         case "int32":
111                             t = typeof(int?);
112                             break;
113                         case "int64":
114                             t = typeof(int?);
115                             break;
116                         default:
117                             break;
118                     }
119                     DatabaseExtensions.CreateAutoImplementedProperty(
120                         builder,
121                         dataReader.GetName(i),
122                         t);
123                 }
124 
125                 #endregion
126 
127                 cmd.Parameters.Clear();
128                 dataReader.Close();
129                 dataReader.Dispose();
130                 cmd.Dispose();
131                 conn.Close();
132                 conn.Dispose();
133 
134                 Type returnType = builder.CreateType();
135 
136                 if (parameters != null)
137                 {
138                     return db.SqlQuery(returnType, sql, parameters);
139                 }
140                 else
141                 {
142                     return db.SqlQuery(returnType, sql);
143                 }
144             }
145         }
146 
147         private static TypeBuilder CreateTypeBuilder(string assemblyName, string moduleName, string typeName)
148         {
149             TypeBuilder typeBuilder = AppDomain.CurrentDomain.DefineDynamicAssembly(
150               new AssemblyName(assemblyName),
151               AssemblyBuilderAccess.Run).DefineDynamicModule(moduleName).DefineType(typeName,
152               TypeAttributes.Public);
153             typeBuilder.DefineDefaultConstructor(MethodAttributes.Public);
154             return typeBuilder;
155         }
156 
157         private static void CreateAutoImplementedProperty(TypeBuilder builder, string propertyName, Type propertyType)
158         {
159             const string PrivateFieldPrefix = "m_";
160             const string GetterPrefix = "get_";
161             const string SetterPrefix = "set_";
162 
163             // Generate the field.
164             FieldBuilder fieldBuilder = builder.DefineField(
165               string.Concat(
166                 PrivateFieldPrefix, propertyName),
167               propertyType,
168               FieldAttributes.Private);
169 
170             // Generate the property
171             PropertyBuilder propertyBuilder = builder.DefineProperty(
172               propertyName,
173               System.Reflection.PropertyAttributes.HasDefault,
174               propertyType, null);
175 
176             // Property getter and setter attributes.
177             MethodAttributes propertyMethodAttributes = MethodAttributes.Public
178               | MethodAttributes.SpecialName
179               | MethodAttributes.HideBySig;
180 
181             // Define the getter method.
182             MethodBuilder getterMethod = builder.DefineMethod(
183                 string.Concat(
184                   GetterPrefix, propertyName),
185                 propertyMethodAttributes,
186                 propertyType,
187                 Type.EmptyTypes);
188 
189             // Emit the IL code.
190             // ldarg.0
191             // ldfld,_field
192             // ret
193             ILGenerator getterILCode = getterMethod.GetILGenerator();
194             getterILCode.Emit(OpCodes.Ldarg_0);
195             getterILCode.Emit(OpCodes.Ldfld, fieldBuilder);
196             getterILCode.Emit(OpCodes.Ret);
197 
198             // Define the setter method.
199             MethodBuilder setterMethod = builder.DefineMethod(
200               string.Concat(SetterPrefix, propertyName),
201               propertyMethodAttributes,
202               null,
203               new Type[] { propertyType });
204 
205             // Emit the IL code.
206             // ldarg.0
207             // ldarg.1
208             // stfld,_field
209             // ret
210             ILGenerator setterILCode = setterMethod.GetILGenerator();
211             setterILCode.Emit(OpCodes.Ldarg_0);
212             setterILCode.Emit(OpCodes.Ldarg_1);
213             setterILCode.Emit(OpCodes.Stfld, fieldBuilder);
214             setterILCode.Emit(OpCodes.Ret);
215 
216             propertyBuilder.SetGetMethod(getterMethod);
217             propertyBuilder.SetSetMethod(setterMethod);
218         }
219 
220         public static dynamic SqlFunctionForDynamic(this Database db,
221                 string sql,
222                 params object[] parameters)
223         {
224             IDbConnection conn = DefaultConnection;
225 
226             //ADO.NET数据库连接字符串
227             conn.ConnectionString = DefaultConnectionString;
228 
229             if (conn.State != ConnectionState.Open)
230             {
231                 conn.Open();
232             }
233 
234             IDbCommand cmd = conn.CreateCommand();
235             cmd.CommandText = sql;
236             cmd.CommandType = CommandType.StoredProcedure;
237             if (parameters != null)
238             {
239                 foreach (var item in parameters)
240                 {
241                     cmd.Parameters.Add(item);
242                 }
243             }
244             //1、DataReader查询数据
245             using (IDataReader dataReader = cmd.ExecuteReader())
246             {
247                 if (!dataReader.Read())
248                 {
249                     return null;
250                 }
251                 //2、DataReader转换Json
252                 string jsonstr = Common.JsonConverter.ToJson(dataReader);
253                 dataReader.Close();
254                 dataReader.Dispose();
255                 cmd.Dispose();
256                 conn.Close();
257                 conn.Dispose();
258                 //3、Json转换动态类
259                 dynamic dyna = Common.JsonConverter.ConvertJson(jsonstr);
260                 return dyna;
261             }
262         }
263         /// <summary>
264         /// 对可空类型进行判断转换(*要不然会报错)
265         /// </summary>
266         /// <param name="value">DataReader字段的值</param>
267         /// <param name="conversionType">该字段的类型</param>
268         /// <returns></returns>
269         private static object CheckType(object value, Type conversionType)
270         {
271             if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
272             {
273                 if (value == null)
274                     return null;
275                 System.ComponentModel.NullableConverter nullableConverter = new System.ComponentModel.NullableConverter(conversionType);
276                 conversionType = nullableConverter.UnderlyingType;
277             }
278             return Convert.ChangeType(value, conversionType);
279         }
280 
281         /// <summary>
282         /// 判断指定对象是否是有效值
283         /// </summary>
284         /// <param name="obj"></param>
285         /// <returns></returns>
286         private static bool IsNullOrDBNull(object obj)
287         {
288             return (obj == null || (obj is DBNull)) ? true : false;
289         }
290     }
291 }
View Code

 

 

 

 

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

 

posted @ 2016-05-10 09:44 果冻布丁喜之郎 阅读(...) 评论(...) 编辑 收藏