首页 > 代码库 > Entity FrameWork 6帮助类
Entity FrameWork 6帮助类
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 = http://www.mamicode.com/data.Provider.CreateQuery(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 =http://www.mamicode.com/> (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 != "\r\n" && (!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 }
Entity FrameWork 6帮助类
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。