1 using System;  2 using System.Data;  3 using System.Collections.Generic;  4 using System.Data.Entity;  5 using System.Linq;  6 using System.Linq.Expressions;  7 using System.Text;  8 using System.Threading.Tasks;  9 using SchoolDAL.Context; 10 using SchoolDAL.Entity; 11 using System.Data.Entity.Infrastructure; 12 using System.Data.SqlClient; 13 using EntityFramework.Extensions; 14 using System.Reflection; 15 namespace SchoolDAL 16 { 17  18     public class BaseDAL 19     { 20  21         public  DbContext Context; 22  23         public BaseDAL(DbContext context) 24         { 25             Context = context; 26         } 27         public int GetModelCount<TEntity>(Expression<Func<TEntity, bool>> filterList) where TEntity : class 28         { 29             return FindByItem(filterList).Count(); 30         } 31  32         public List<TEntity> FindModelList<TEntity>(Expression<Func<TEntity, bool>> filterList,int currPage,int size,out int Total) where TEntity : class 33         { 34             List<TEntity> list=new List<TEntity>(); 35             var json = FindByItem(filterList); 36             try 37             { 38                 Total = json.Count(); 39                 list = json.Skip(currPage).Take(size).ToList();// only support order by Id asc 40             } 41             catch 42             { 43                 Total = 0; 44             } 45             return list; 46         } 47  48         public TEntity FindModel<TEntity>(Expression<Func<TEntity, bool>> filterList) where TEntity : class 49         { 50           return   FindByItem(filterList).FirstOrDefault(); 51         } 52  53         public IQueryable<TEntity> FindByItem<TEntity>(List<Expression<Func<TEntity, bool>>> filterList) where TEntity : class 54         { 55             var json = Context.Set<TEntity>().Where(t => true); 56             foreach (var item in filterList) 57             { 58                 json = json.Where(item); 59             } 60             return json; 61         } 62  63         public IQueryable<TEntity> FindByItem<TEntity>(Expression<Func<TEntity, bool>> filterList) where TEntity : class 64         { 65             var json = Context.Set<TEntity>().Where(filterList); 66  67             return json; 68         } 69  70         public bool Create<TEntity>(TEntity TObject) where TEntity : class 71         { 72             try 73             { 74                 Context.Set<TEntity>().Add(TObject); 75                 Context.SaveChanges(); 76                 return true; 77             } 78             catch (Exception ex) 79             { 80                 return false; 81             } 82         } 83  84         public bool Edit<TEntity>(TEntity TObject) where TEntity : class 85         { 86             try 87             { 88                 var entry = Context.Entry(TObject); 89                 Context.Set<TEntity>().Attach(TObject); 90                 entry.State = EntityState.Modified; 91                 Context.SaveChanges(); 92                 return true; 93             } 94             catch 95             { 96                 //throw ex; 97                 return false; 98             } 99         }100 101         public void Delete<TEntity>(TEntity TObject) where TEntity : class102         {103             try104             {105                 Context.Set<TEntity>().Remove(TObject);106                 Context.SaveChanges();107 108             }109             catch (Exception ex)110             {111 112             }113 114         }115 116   117         public int BulkUpdate<TEntity>(List<Expression<Func<TEntity, bool>>> filterList, Expression<Func<TEntity, TEntity>> UpdatedModel) where TEntity : class118         {119             int res = 0;120             var json = FindByItem(filterList);121             using (var tran = Context.Database.BeginTransaction())122             {123                 try124                 {125                     res = Context.Set<TEntity>().Update(json, UpdatedModel);126                     tran.Commit();127                 }128                 catch129                 {130                     tran.Rollback();131                 }132             }133             return res;134         }135 136         public int BulkDelete<TEntity>(List<Expression<Func<TEntity, bool>>> filterList) where TEntity : class137         {138             int res=0;139             var json = FindByItem(filterList);140             using(var tran=   Context.Database.BeginTransaction())141             {142                 try143                 {144                    res= Context.Set<TEntity>().Delete(json);145                     tran.Commit();146                 }147                 catch148                 {149                 tran.Rollback();150                 }151             }152             return res;153 154         }155 156         public void BulkCreate<TEntity>(List<TEntity> list,string tableName) where TEntity : class157         {158 159             DataTable dt = new DataTable();160             Type type = typeof(TEntity);161             PropertyInfo[] propes = type.GetProperties();162             foreach (var prop in propes)163             {164                 dt.Columns.Add(prop.Name);165             }166             foreach (var entity in list)167             {168                 DataRow row = dt.NewRow();169                 foreach (DataColumn col in dt.Columns)170                 {171                     foreach (var prop in propes)172                     {173                         if (!col.ColumnName.Equals("id", StringComparison.InvariantCultureIgnoreCase))174                         {175                             if (prop.Name.Equals(col.ColumnName, StringComparison.InvariantCultureIgnoreCase))176                                 row[col.ColumnName] = prop.GetValue(entity);177                         }178                     }179 180                 }181                 dt.Rows.Add(row);182             }183             if (dt != null && dt.Rows.Count > 0)184             {185                 using (var tran = Context.BeginTransaction())186                 {187                     try188                     {189                         using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(Context.Database.Connection.ConnectionString))190                         {191                             sqlBulkCopy.BatchSize = 10000;192                             sqlBulkCopy.BulkCopyTimeout = 12000;193                             sqlBulkCopy.DestinationTableName = string.Format("dbo.{0}", tableName);196                                 for (int i = 1; i < dt.Columns.Count; i++)197                                 {198                                     sqlBulkCopy.ColumnMappings.Add(i, i);199                                 }201                             sqlBulkCopy.WriteToServer(dt);202                             tran.Commit();203                         }204                     }205                     catch (Exception e)206                     {207                         tran.Rollback();208                     }209                 }210 211             }212            213         }214 215         public SchoolContext GetContext()216         {217             return new SchoolContext();218         }219 220         public void DisposeContext(SchoolContext db)221         {222                 if (db != null)223                     db.Dispose();224         }225 226         public DbEntityEntry<TEntity> EditEntry<TEntity>(TEntity TObject) where TEntity : class227         {228             var entry = Context.Entry(TObject);229             Context.Set<TEntity>().Attach(TObject);230             entry.State = EntityState.Modified;231             return entry;232         }233 234         public List<TEntity> ExecuteByStoreProcedure<TEntity>(string ExecuteProcedure,SqlParameter[] parms) where TEntity : class235         {236             List<SqlParameter> parmList = new List<SqlParameter>();237 245             var list = Context.Database.SqlQuery<TEntity>(ExecuteProcedure, parms);246                 var enityList = list.ToList();247              248                 return enityList;249          250         }251 252     }253 }