entity framework 6 通用数据类

原文  http://blog.csdn.net/laokaizzz/article/details/25730813

  1 public class BaseDAL
  2     {
  3   string strConn = "";
  4   public BaseDAL(string connString)
  5   {
  6       strConn = connString;
  7   }
  8 
  9   #region 通用增删改查
 10   #region 非原始sql语句方式
 11   /// <summary>
 12   /// 新增
 13   /// </summary>
 14   /// <param name="entity">实体</param>
 15   /// <returns>返回受影响行数</returns>
 16   public bool Add<T>(T entity) where T : class
 17   {
 18       using (SysDb<T> db = new SysDb<T>(strConn))
 19       {
 20     db.Entry<T>(entity).State = EntityState.Added;
 21     return db.SaveChanges() > 0;
 22       }
 23   }
 24 
 25   /// <summary>
 26   /// 修改
 27   /// </summary>
 28   /// <param name="entity">实体</param>
 29   /// <returns>返回受影响行数</returns>
 30   public bool Update<T>(T entity) where T : class
 31   {
 32       using (SysDb<T> db = new SysDb<T>(strConn))
 33       {
 34     db.Set<T>().Attach(entity);
 35     db.Entry<T>(entity).State = EntityState.Modified;
 36     return db.SaveChanges() > 0;
 37       }
 38   }
 39 
 40   /// <summary>
 41   /// 删除
 42   /// </summary>
 43   /// <param name="entity">实体</param>
 44   /// <returns>返回受影响行数</returns>
 45   public bool Delete<T>(T entity) where T : class
 46   {
 47       using (SysDb<T> db = new SysDb<T>(strConn))
 48       {
 49     db.Set<T>().Attach(entity);
 50     db.Entry<T>(entity).State = EntityState.Deleted;
 51     return db.SaveChanges() > 0;
 52       }
 53   }
 54 
 55   /// <summary>
 56   /// 根据条件删除
 57   /// </summary>
 58   /// <param name="deleWhere">删除条件</param>
 59   /// <returns>返回受影响行数</returns>
 60   public bool DeleteByConditon<T>(Expression<Func<T, bool>> deleWhere) where T : class
 61   {
 62       using (SysDb<T> db = new SysDb<T>(strConn))
 63       {
 64     List<T> entitys = db.Set<T>().Where(deleWhere).ToList();
 65     entitys.ForEach(m => db.Entry<T>(m).State = EntityState.Deleted);
 66     return db.SaveChanges() > 0;
 67       }
 68   }
 69 
 70   /// <summary>
 71   /// 查找单个
 72   /// </summary>
 73   /// <param name="id">主键</param>
 74   /// <returns></returns>
 75   public T GetSingleById<T>(int id) where T : class
 76   {
 77       using (SysDb<T> db = new SysDb<T>(strConn))
 78       {
 79     return db.Set<T>().Find(id);
 80       }
 81   }
 82 
 83   /// <summary>
 84   /// 查找单个
 85   /// </summary>
 86   /// <param name="seleWhere">查询条件</param>
 87   /// <returns></returns>
 88   public T GetSingle<T>(Expression<Func<T, bool>> seleWhere) where T : class
 89   {
 90       using (SysDb<T> db = new SysDb<T>(strConn))
 91       {
 92     return db.Set<T>().AsExpandable().FirstOrDefault(seleWhere);
 93       }
 94   }
 95 
 96   /// <summary>
 97   /// 获取所有实体集合
 98   /// </summary>
 99   /// <returns></returns>
100   public List<T> GetAll<T>() where T : class
101   {
102       using (SysDb<T> db = new SysDb<T>(strConn))
103       {
104     return db.Set<T>().AsExpandable().ToList<T>();
105       }
106   }
107 
108   /// <summary>
109   /// 获取所有实体集合(单个排序)
110   /// </summary>
111   /// <returns></returns>
112   public List<T> GetAll<T, Tkey>(Expression<Func<T, Tkey>> orderWhere, bool isDesc) where T : class
113   {
114       using (SysDb<T> db = new SysDb<T>(strConn))
115       {
116     return CommonSort(db.Set<T>().AsExpandable(), orderWhere, isDesc).ToList<T>();
117       }
118   }
119 
120   /// <summary>
121   /// 获取所有实体集合(多个排序)
122   /// </summary>
123   /// <returns></returns>
124   public List<T> GetAll<T>(params OrderModelField[] orderByExpression) where T : class
125   {
126       using (SysDb<T> db = new SysDb<T>(strConn))
127       {
128     return CommonSort(db.Set<T>().AsExpandable(), orderByExpression).ToList();
129       }
130   }
131 
132   /// <summary>
133   /// 单个排序通用方法
134   /// </summary>
135   /// <typeparam name="Tkey">排序字段</typeparam>
136   /// <param name="data">要排序的数据</param>
137   /// <param name="orderWhere">排序条件</param>
138   /// <param name="isDesc">是否倒序</param>
139   /// <returns>排序后的集合</returns>
140   public IQueryable<T> CommonSort<T, Tkey>(IQueryable<T> data, Expression<Func<T, Tkey>> orderWhere, bool isDesc) where T : class
141   {
142       if (isDesc)
143       {
144     return data.OrderByDescending(orderWhere);
145       }
146       else
147       {
148     return data.OrderBy(orderWhere);
149       }
150   }
151 
152   /// <summary>
153   /// 多个排序通用方法
154   /// </summary>
155   /// <typeparam name="Tkey">排序字段</typeparam>
156   /// <param name="data">要排序的数据</param>
157   /// <param name="orderWhereAndIsDesc">字典集合(排序条件,是否倒序)</param>
158   /// <returns>排序后的集合</returns>
159   public IQueryable<T> CommonSort<T>(IQueryable<T> data, params OrderModelField[] orderByExpression) where T : class
160   {
161       //创建表达式变量参数
162       var parameter = Expression.Parameter(typeof(T), "o");
163 
164       if (orderByExpression != null && orderByExpression.Length > 0)
165       {
166     for (int i = 0; i < orderByExpression.Length; i++)
167     {
168         //根据属性名获取属性
169         var property = typeof(T).GetProperty(orderByExpression[i].PropertyName);
170         //创建一个访问属性的表达式
171         var propertyAccess = Expression.MakeMemberAccess(parameter, property);
172         var orderByExp = Expression.Lambda(propertyAccess, parameter);
173 
174         string OrderName = "";
175         if (i > 0)
176         {
177       OrderName = orderByExpression[i].IsDESC ? "ThenByDescending" : "ThenBy";
178         }
179         else
180       OrderName = orderByExpression[i].IsDESC ? "OrderByDescending" : "OrderBy";
181 
182         MethodCallExpression resultExp = Expression.Call(typeof(Queryable), OrderName, new Type[] { typeof(T), property.PropertyType },
183       data.Expression, Expression.Quote(orderByExp));
184 
185         data = data.Provider.CreateQuery<T>(resultExp);
186     }
187       }
188       return data;
189   }
190 
191   /// <summary>
192   /// 根据条件查询实体集合
193   /// </summary>
194   /// <param name="seleWhere">查询条件 lambel表达式</param>
195   /// <returns></returns>
196   public List<T> GetList<T>(Expression<Func<T, bool>> seleWhere) where T : class
197   {
198       using (SysDb<T> db = new SysDb<T>(strConn))
199       {
200     return db.Set<T>().AsExpandable().Where(seleWhere).ToList();
201       }
202   }
203 
204   /// <summary>
205   /// 根据条件查询实体集合
206   /// </summary>
207   /// <param name="seleWhere">查询条件 lambel表达式</param>
208   /// <returns></returns>
209   public List<T> GetList<T, TValue>(Expression<Func<T, TValue>> seleWhere, IEnumerable<TValue> conditions) where T : class
210   {
211       using (SysDb<T> db = new SysDb<T>(strConn))
212       {
213 
214     return db.Set<T>().AsExpandable().WhereIn<T, TValue>(seleWhere, conditions).ToList();
215       }
216   }
217 
218   /// <summary>
219   /// 根据条件查询实体集合(单个字段排序)
220   /// </summary>
221   /// <param name="seleWhere">查询条件 lambel表达式</param>
222   /// <returns></returns>
223   public List<T> GetList<T, Tkey>(Expression<Func<T, bool>> seleWhere, Expression<Func<T, Tkey>> orderWhere, bool isDesc) where T : class
224   {
225       using (SysDb<T> db = new SysDb<T>(strConn))
226       {
227     return CommonSort(db.Set<T>().AsExpandable().Where(seleWhere), orderWhere, isDesc).ToList();
228       }
229   }
230 
231   /// <summary>
232   /// 根据条件查询实体集合(多个字段排序)
233   /// </summary>
234   /// <param name="seleWhere">查询条件 lambel表达式</param>
235   /// <returns></returns>
236   public List<T> GetList<T>(Expression<Func<T, bool>> seleWhere, params OrderModelField[] orderByExpression) where T : class
237   {
238       using (SysDb<T> db = new SysDb<T>(strConn))
239       {
240     return CommonSort(db.Set<T>().AsExpandable().Where(seleWhere), orderByExpression).ToList();
241       }
242   }
243 
244   /// <summary>
245   /// 获取分页集合(无条件无排序)
246   /// </summary>
247   /// <returns></returns>
248   public List<T> GetListPaged<T, Tkey>(int pageIndex, int pageSize, out int totalcount) where T : class
249   {
250       using (SysDb<T> db = new SysDb<T>(strConn))
251       {
252     totalcount = db.Set<T>().AsExpandable().Count();//获取总数
253     //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序  AsExpandable是linqkit.dll中的方法
254     return db.Set<T>().AsExpandable().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
255       }
256   }
257 
258   /// <summary>
259   /// 获取分页集合(无条件单个排序)
260   /// </summary>
261   /// <returns></returns>
262   public List<T> GetListPaged<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, Tkey>> orderWhere, bool isDesc, out int totalcount) where T : class
263   {
264       using (SysDb<T> db = new SysDb<T>(strConn))
265       {
266     totalcount = db.Set<T>().AsExpandable().Count();//获取总数
267     //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序  AsExpandable是linqkit.dll中的方法
268     return CommonSort(db.Set<T>().AsExpandable(), orderWhere, isDesc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
269       }
270   }
271 
272   /// <summary>
273   /// 获取分页集合(无条件多字段排序)
274   /// </summary>
275   /// <returns></returns>
276   public List<T> GetListPaged<T>(int pageIndex, int pageSize, out int totalcount, params OrderModelField[] orderByExpression) where T : class
277   {
278       using (SysDb<T> db = new SysDb<T>(strConn))
279       {
280     totalcount = db.Set<T>().AsExpandable().Count();//获取总数
281     //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序  AsExpandable是linqkit.dll中的方法
282     return CommonSort(db.Set<T>().AsExpandable(), orderByExpression).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
283       }
284   }
285 
286   /// <summary>
287   /// 获取分页集合(有条件无排序)
288   /// </summary>
289   /// <returns></returns>
290   public List<T> GetListPaged<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, bool>> seleWhere, out int totalcount) where T : class
291   {
292       using (SysDb<T> db = new SysDb<T>(strConn))
293       {
294     totalcount = db.Set<T>().AsExpandable().Where(seleWhere).Count();//获取总数
295     //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序  AsExpandable是linqkit.dll中的方法
296     return db.Set<T>().AsExpandable().Where(seleWhere).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
297       }
298   }
299 
300   /// <summary>
301   /// 获取分页集合(有条件单个排序)
302   /// </summary>
303   /// <returns></returns>
304   public List<T> GetListPaged<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, bool>> seleWhere,
305       Expression<Func<T, Tkey>> orderWhere, bool isDesc, out int totalcount) where T : class
306   {
307       using (SysDb<T> db = new SysDb<T>(strConn))
308       {
309     totalcount = db.Set<T>().AsExpandable().Where(seleWhere).Count();//获取总数
310     //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序  AsExpandable是linqkit.dll中的方法
311     return CommonSort(db.Set<T>().AsExpandable().Where(seleWhere), orderWhere, isDesc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
312       }
313   }
314 
315   /// <summary>
316   /// 获取分页集合(有条件多字段排序)
317   /// </summary>
318   /// <returns></returns>
319   public List<T> GetListPaged<T>(int pageIndex, int pageSize, Expression<Func<T, bool>> seleWhere,
320       out int totalcount, params OrderModelField[] orderModelFiled) where T : class
321   {
322       using (SysDb<T> db = new SysDb<T>(strConn))
323       {
324     totalcount = db.Set<T>().AsExpandable().Where(seleWhere).Count();//获取总数
325     //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序  AsExpandable是linqkit.dll中的方法
326     return CommonSort(db.Set<T>().AsExpandable().Where(seleWhere), orderModelFiled).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
327       }
328   }
329   #endregion
330 
331   #region 原始sql操作
332   /// <summary>
333   /// 执行操作
334   /// </summary>
335   /// <param name="sql"></param>
336   /// <param name="paras"></param>
337   public void ExecuteSql(string sql, params object[] paras)
338   {
339       using (SysDb db = new SysDb(strConn))
340       {
341     db.Database.ExecuteSqlCommand(sql, paras);
342       }
343   }
344 
345   /// <summary>
346   /// 查询列表
347   /// </summary>
348   /// <typeparam name="T"></typeparam>
349   /// <param name="sql"></param>
350   /// <param name="paras"></param>
351   /// <returns></returns>
352   public List<T> QueryList<T>(string sql, params object[] paras) where T : class
353   {
354       using (SysDb db = new SysDb(strConn))
355       {
356     return db.Database.SqlQuery<T>(sql, paras).ToList();
357       }
358   }
359 
360   /// <summary>
361   /// 查询单个
362   /// </summary>
363   /// <typeparam name="T"></typeparam>
364   /// <param name="sql"></param>
365   /// <param name="paras"></param>
366   /// <returns></returns>
367   public T QuerySingle<T>(string sql, params object[] paras) where T : class
368   {
369       using (SysDb<T> db = new SysDb<T>(strConn))
370       {
371     return db.Database.SqlQuery<T>(sql, paras).FirstOrDefault();
372       }
373   }
374 
375   /// <summary>
376   /// 执行事务
377   /// </summary>
378   /// <param name="lsSql"></param>
379   /// <param name="lsParas"></param>
380   public void ExecuteTransaction(List<String> lsSql, List<Object[]> lsParas)
381   {
382       using (SysDb db = new SysDb(strConn))
383       {
384     using (var tran = db.Database.BeginTransaction())
385     {
386         try
387         {
388       for (int i = 0; i < lsSql.Count; i++)
389       {
390           if (lsParas != null && lsParas.Count > 0)
391           {
392         db.Database.ExecuteSqlCommand(lsSql[i], lsParas[i]);
393           }
394       }
395       foreach (String item in lsSql)
396       {
397           db.Database.ExecuteSqlCommand(item);
398       }
399 
400       tran.Commit();
401         }
402         catch (Exception ex)
403         {
404       tran.Rollback();
405       throw ex;
406         }
407     }
408       }
409   }
410   #endregion
411   #endregion
412 
413   #region 通用属性
414   /// <summary>
415   /// 获取数据库服务器当前时间。
416   /// </summary>
417   public DateTime ServerTime
418   {
419       get
420       {
421     using (SysDb db = new SysDb(strConn))
422     {
423         String sql = "SELECT GETDATE()";
424         Object objServerTime = db.Database.SqlQuery<Object>(sql);
425         return Convert.ToDateTime(objServerTime);
426     }
427       }
428   }
429 
430   /// <summary>
431   /// 获取数据库版本。
432   /// </summary>
433   public String DatabaseVersion
434   {
435       get
436       {
437     using (SysDb db = new SysDb(strConn))
438     {
439         try
440         {
441       String sql = "SELECT Version FROM Sys_Version";
442       Object objServerTime = db.Database.SqlQuery<Object>(sql);
443       return Convert.ToString(objServerTime);
444         }
445         catch
446         {
447         }
448         return String.Empty;
449     }
450       }
451   }
452   #endregion
453 
454     }
455     public static class QueryableExtension
456     {
457   /// <summary>
458   /// 扩展方法  支持 in 操作
459   /// </summary>
460   /// <typeparam name="TEntity">需要扩展的对象类型</typeparam>
461   /// <typeparam name="TValue">in 的值类型</typeparam>
462   /// <param name="source">需要扩展的对象</param>
463   /// <param name="valueSelector">值选择器 例如c=>c.UserId</param>
464   /// <param name="values">值集合</param>
465   /// <returns></returns>
466   public static IQueryable<TEntity> WhereIn<TEntity, TValue>(this IQueryable<TEntity> source, Expression<Func<TEntity, TValue>> valueSelector,
467     IEnumerable<TValue> values)
468   {
469       if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); }
470       if (null == values) { throw new ArgumentNullException("values"); }
471       ParameterExpression p = valueSelector.Parameters.Single();
472 
473       if (!values.Any())
474       {
475     return source;
476       }
477       var equals = values.Select(value => (Expression)Expression.Equal(valueSelector.Body, Expression.Constant(value, typeof(TValue))));
478       var body = equals.Aggregate<Expression>((accumulate, equal) => Expression.Or(accumulate, equal));
479       return source.Where(Expression.Lambda<Func<TEntity, bool>>(body, p));
480   }
481     }
482     public struct OrderModelField
483     {
484 
485   public bool IsDESC { get; set; }
486   public string PropertyName { get; set; }
487     }
488 dbcontext类：
489 
490 public class SysDb : DbContext
491   {
492     bool isNew = true;//是否是新的sql执行
493     string strMsg = "";//sql执行的相关信息
494     string strConn = "";//数据库连接字符串
495     string UserName = "";//日志用户名称
496     string AdditionalInfo = "";//日志额外信息
497     public SysDb(string connString) : // 数据库链接字符串
498       base(connString)
499     {
500       strConn = connString;
501       Database.SetInitializer<SysDb>(null);//设置为空，防止自动检查和生成
502       base.Database.Log = (info) => Debug.WriteLine(info);
503     }
504 
505     public SysDb(string connString, string logUserName, string logAdditionalInfo) : // 数据库链接字符串
506       base(connString)
507     {
508       strConn = connString;
509       Database.SetInitializer<SysDb>(null);//设置为空，防止自动检查和生成
510       UserName = logUserName;
511       AdditionalInfo = logAdditionalInfo;
512       base.Database.Log = AddLogger;
513     }
514 
515     protected override void OnModelCreating(DbModelBuilder modelBuilder)
516     {
517       //去掉复数映射
518       modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
519       base.OnModelCreating(modelBuilder);
520     }
521 
522     /// <summary>
523     /// 添加日志
524     /// </summary>
525     /// <param name="info"></param>
526     public void AddLogger(string info)
527     {
528       if (info != "
" && (!info.Contains("Sys_EventLog")))
529       {
530         string strTemp = info.ToUpper().Trim();
531         if (isNew)
532         {
533           //记录增删改
534           if (strTemp.StartsWith("INSERT") || strTemp.StartsWith("UPDATE") || strTemp.StartsWith("DELETE"))
535           {
536             strMsg = info;
537             isNew = false;
538           }
539         }
540         else
541         {
542           if (strTemp.StartsWith("CLOSED CONNECTION"))
543           {
544             //增加新日志
545             using (SysDb db = new SysDb(strConn))
546             {
547               try
548               {
549         //保存日志到数据库或其他地方
550 
551               }
552               catch (Exception ex)
553               {
554                 using (System.IO.StreamWriter sw = new System.IO.StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "//logError.txt"))
555                 {
556                   sw.Write(ex.Message);
557                   sw.Flush();
558                 }
559               }
560             }
561             //清空
562             strMsg = "";
563             isNew = true;
564           }
565           else
566           {
567             strMsg += info;
568           }
569         }
570 
571       }
572     }
573 
574     
575   }
576   public class SysDb<T> : SysDb where T : class
577   {
578     public SysDb(string connString) : // 数据库链接字符串
579       base(connString)
580     {
581       Database.SetInitializer<SysDb<T>>(null);//设置为空，防止自动检查和生成
582     }
583 
584     public SysDb(string connString, string logUserName, string logAdditionalInfo) : // 数据库链接字符串
585       base(connString,logUserName,logAdditionalInfo)
586     {
587       Database.SetInitializer<SysDb<T>>(null);//设置为空，防止自动检查和生成
588     }
589 
590     public DbSet<T> Entities { get; set; }
591   }
592 界面使用：（bll层忽略）
593 
594 public class BusinessController : Controller
595   {
596     //
597     // GET: /Jygl/Business/
598     BaseBLL basebll = new BaseBLL(WebHelper.Conn);
599 
600     public ActionResult GetXMList(int page,int rows)
601     {
602       int count = 0;
603       //查询条件
604       //Expression<Func<JY_XM, bool>> searchPredicate = PredicateBuilder.True<JY_XM>();
605       //searchPredicate = searchPredicate.And(c => c.UserName.Contains(""));
606       Expression<Func<JY_XM, int>> keySelector = u => u.UID;
607       string str = ExceptionHelper<JY_XM>.TryCatchPageQueryJson<int>(basebll.GetListPaged, page, rows, keySelector, false, out count);
608       return Content(str); 
609     }
610 
611     [HttpPost]
612     public ActionResult XMEdit(JY_XM jyxm)
613     {
614       basebll.Add(jyxm);
615       return View();
616     }
617 
618     public ActionResult GetAllGCLB()
619     {
620 
621       List<DICT_GCLB> lsGCLB = basebll.GetAll<DICT_GCLB>();
622       DICT_GCLB dicNew=new DICT_GCLB();
623       dicNew.GCLBText="-请选择-";
624       dicNew.GCLBId=0;
625       lsGCLB.Add(dicNew);
626 
627       return Content(WebHelper.Json(lsGCLB));
628     }
629 
630     public ActionResult GetAllArea()
631     {
632       List<DICT_Area> lsArea = basebll.GetAll<DICT_Area>();
633       DICT_Area dicNew=new DICT_Area();
634       dicNew.AreaText="-请选择-";
635       dicNew.AreaId=0;
636       lsArea.Add(dicNew);
637       return Content(WebHelper.Json(lsArea));
638     }
639   }
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/smartsensor/p/4762258.html