记EF的一个基本访问类
代码：
1 using EFModel; 2 using System; 3 using System.Collections.Generic; 4 using System.Data.Entity; 5 using System.Data.Entity.ModelConfiguration.Conventions; 6 using System.Linq; 7 using System.Text; 8 using System.Threading.Tasks; 9 10 namespace EFDal 11 { 12 13 /// <summary> 14 /// 15 /// </summary> 16 public class DataContext : DbContext 17 { 18 /// <summary> 19 /// 20 /// </summary> 21 public Boolean IsDisposed = false; 22 23 24 public static bool ModelFlag = true; 25 26 /// <summary> 27 /// 28 /// </summary> 29 static DataContext() 30 { 31 if (ModelFlag) 32 { 33 Database.SetInitializer<DataContext>(null); //不创建 34 } 35 else 36 { 37 Database.SetInitializer<DataContext>(new DefaultExistsDb()); 38 } 39 40 } 41 42 43 /// <summary> 44 /// 45 /// </summary> 46 public DataContext() 47 : base() 48 { 49 50 51 } 52 53 /// <summary> 54 /// db connection string 55 /// </summary> 56 public string DBConnectionString 57 { 58 get; 59 set; 60 } 61 62 /// <summary> 63 /// 64 /// </summary> 65 /// <param name="connectionString"></param> 66 public DataContext(string connectionString) 67 : base(connectionString) 68 { 69 DBConnectionString = connectionString; 70 } 71 72 /// <summary> 73 /// get this db connection string 74 /// </summary> 75 /// <returns></returns> 76 public string GetConnectionString() 77 { 78 if (!string.IsNullOrEmpty(DBConnectionString)) 79 { 80 return DBConnectionString; 81 } 82 else 83 { 84 return this.Database.Connection.ConnectionString; 85 } 86 } 87 88 /// <summary> 89 /// check the database real exist or not (check sql server have this db or not) 90 /// </summary> 91 /// <returns></returns> 92 public Boolean IsExists() 93 { 94 return this.Database.Exists(); 95 } 96 97 /// <summary> 98 /// dispose 99 /// </summary> 100 /// <param name="disposing"></param> 101 protected override void Dispose(bool disposing) 102 { 103 IsDisposed = true; 104 base.Dispose(disposing); 105 } 106 107 108 109 110 /// <summary> 111 /// 112 /// </summary> 113 /// <param name="modelBuilder"></param> 114 protected override void OnModelCreating(DbModelBuilder modelBuilder) 115 { 116 modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); //去掉表名复数 117 modelBuilder.Entity<Employee>().Property(p => p.EmployeeID).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None); 118 } 119 120 } 121 122 /// <summary> 123 /// 默认数据库，当model变更时，重建创建 124 /// </summary> 125 public class Change_RecreateDb : System.Data.Entity.DropCreateDatabaseIfModelChanges<DataContext> 126 { 127 128 } 129 /// <summary> 130 /// 重新创建 131 /// </summary> 132 public class RecreateDb : System.Data.Entity.DropCreateDatabaseAlways<DataContext> 133 { 134 135 } 136 /// <summary> 137 /// 当数据库不存在时创建 138 /// </summary> 139 public class DefaultExistsDb : System.Data.Entity.CreateDatabaseIfNotExists<DataContext> 140 { 141 142 } 143 144 145 }
另外实体类在主键字段中，应该标识[Key]。不然在数据主键标识定义为非PK_xxx时，会提示主键错误
实体类定义示例：
1 /// <summary> 2 /// Employee 3 /// </summary> 4 public class Employee 5 { 6 private int _EmployeeID; 7 private int _ClientID; 8 private string _EmployeeCode; 9 private string _EmployeeName; 10 private string _EmployeePass; 11 private int _EmployeeStatus; 12 private int _RoleID; 13 private decimal _Timezone; 14 private string _TimezoneCode; 15 private int _Language; 16 private int _MainStatus; 17 private string _Mobile; 18 /// <summary> 19 /// 20 /// </summary> 21 public int EmployeeID 22 { 23 get 24 { 25 return _EmployeeID; 26 } 27 set 28 { 29 _EmployeeID = value; 30 } 31 } 32 /// <summary> 33 /// 34 /// </summary> 35 public int ClientID 36 { 37 get 38 { 39 return _ClientID; 40 } 41 set 42 { 43 _ClientID = value; 44 } 45 } 46 /// <summary> 47 /// 48 /// </summary> 49 public string EmployeeCode 50 { 51 get 52 { 53 return _EmployeeCode; 54 } 55 set 56 { 57 if (value == null) 58 { 59 _EmployeeCode = string.Empty; 60 } 61 else 62 { 63 _EmployeeCode = value; 64 } 65 } 66 } 67 /// <summary> 68 /// 69 /// </summary> 70 public string EmployeeName 71 { 72 get 73 { 74 return _EmployeeName; 75 } 76 set 77 { 78 if (value == null) 79 { 80 _EmployeeName = string.Empty; 81 } 82 else 83 { 84 _EmployeeName = value; 85 } 86 } 87 } 88 /// <summary> 89 /// 90 /// </summary> 91 public string EmployeePass 92 { 93 get 94 { 95 return _EmployeePass; 96 } 97 set 98 { 99 if (value == null) 100 { 101 _EmployeePass = string.Empty; 102 } 103 else 104 { 105 _EmployeePass = value; 106 } 107 } 108 } 109 /// <summary> 110 /// 111 /// </summary> 112 public int EmployeeStatus 113 { 114 get 115 { 116 return _EmployeeStatus; 117 } 118 set 119 { 120 _EmployeeStatus = value; 121 } 122 } 123 /// <summary> 124 /// 125 /// </summary> 126 public int RoleID 127 { 128 get 129 { 130 return _RoleID; 131 } 132 set 133 { 134 _RoleID = value; 135 } 136 } 137 138 /// <summary> 139 /// 140 /// </summary> 141 public decimal Timezone 142 { 143 get 144 { 145 return _Timezone; 146 } 147 set 148 { 149 _Timezone = value; 150 } 151 } 152 /// <summary> 153 /// 154 /// </summary> 155 public string TimezoneCode 156 { 157 get 158 { 159 return _TimezoneCode; 160 } 161 set 162 { 163 if (value == null) 164 { 165 _TimezoneCode = string.Empty; 166 } 167 else 168 { 169 _TimezoneCode = value; 170 } 171 } 172 } 173 /// <summary> 174 /// 175 /// </summary> 176 public int Language 177 { 178 get 179 { 180 return _Language; 181 } 182 set 183 { 184 _Language = value; 185 } 186 } 187 /// <summary> 188 /// 189 /// </summary> 190 public int MainStatus 191 { 192 get 193 { 194 return _MainStatus; 195 } 196 set 197 { 198 _MainStatus = value; 199 } 200 } 201 /// <summary> 202 /// 203 /// </summary> 204 public string Mobile 205 { 206 get 207 { 208 return _Mobile; 209 } 210 set 211 { 212 if (value == null) 213 { 214 _Mobile = string.Empty; 215 } 216 else 217 { 218 _Mobile = value; 219 } 220 } 221 } 222 223 }
仓储接口：
1 public interface IRepository<T> : IDisposable where T : class 2 { 3 4 /// <summary> 5 /// 添加实体 6 /// </summary> 7 /// <typeparam name="T"></typeparam> 8 /// <param name="Entity"></param> 9 /// <returns></returns> 10 bool Add(T Entity) ; 11 12 /// <summary> 13 /// 批量的进行添加实体 14 /// </summary> 15 /// <typeparam name="T"></typeparam> 16 /// <param name="Entity"></param> 17 /// <returns></returns> 18 bool AddRange(List<T> Entity); 19 20 21 /// <summary> 22 /// 删除单个实体 23 /// </summary> 24 /// <typeparam name="T"></typeparam> 25 /// <param name="Entity"></param> 26 /// <returns></returns> 27 bool Delete(T Entity); 28 29 /// <summary> 30 /// 根据查询条件进行删除单个实体 31 /// </summary> 32 /// <typeparam name="T"></typeparam> 33 /// <param name="whereLambda"></param> 34 /// <returns></returns> 35 bool Delete(Expression<Func<T, bool>> whereLambda); 36 37 38 /// <summary> 39 ///单个对象的修改 40 /// </summary> 41 /// <typeparam name="T"></typeparam> 42 /// <param name="Entity">需要修改的对象</param> 43 /// <returns></returns> 44 bool Update(T Entity); 45 46 47 /// <summary> 48 /// 批量修改 49 /// </summary> 50 /// <typeparam name="T"></typeparam> 51 /// <param name="whereLambda"></param> 52 /// <param name="updateLambda"></param> 53 /// <returns></returns> 54 bool Update(Expression<Func<T, bool>> WhereLambda, Expression<Func<T, T>> UpdateLambda) ; 55 56 57 /// <summary> 58 /// 批量的修改 59 /// </summary> 60 /// <typeparam name="T"></typeparam> 61 /// <param name="Entity"></param> 62 /// <returns></returns> 63 bool Update(List<T> Entity) ; 64 65 66 /// <summary> 67 /// 批量统一的进行更新 68 /// </summary> 69 /// <typeparam name="T"></typeparam> 70 /// <param name="model">需要修改的对象实体</param> 71 /// <param name="WhereLambda">查询的条件</param> 72 /// <param name="ModifiedProNames"></param> 73 /// <returns></returns> 74 bool Update(T model, Expression<Func<T, bool>> WhereLambda, params string[] ModifiedProNames) ; 75 76 77 /// <summary> 78 /// 根据主键进行查询 79 /// </summary> 80 /// <typeparam name="T"></typeparam> 81 /// <param name="ID"></param> 82 /// <returns></returns> 83 T FindByID(dynamic ID) ; 84 85 /// <summary> 86 /// 默认查询选择第一条数据,没有那么进行返回NULL 87 /// </summary> 88 /// <typeparam name="T"></typeparam> 89 /// <param name="WhereLambda"></param> 90 /// <returns>返回bool</returns> 91 T GetFristDefault(Expression<Func<T, bool>> WhereLambda = null) ; 92 93 /// <summary> 94 /// 查询所有的数据 95 /// </summary> 96 /// <typeparam name="T"></typeparam> 97 /// <returns></returns> 98 List<T> GetAll(string Order = null) ; 99 100 /// <summary> 101 /// 含有带条件的查询 102 /// </summary> 103 /// <typeparam name="T"></typeparam> 104 /// <param name="WhereLambda"></param> 105 /// <returns></returns> 106 List<T> GetAllQuery(Expression<Func<T, bool>> WhereLambda = null) ; 107 108 109 /// <summary> 110 ///获取查询的数量 111 /// </summary> 112 /// <typeparam name="T"></typeparam> 113 /// <param name="WhereLambda"></param> 114 /// <returns></returns> 115 int GetCount(Expression<Func<T, bool>> WhereLambda = null); 116 117 /// <summary> 118 /// 判断对象是否存在 119 /// </summary> 120 /// <typeparam name="T"></typeparam> 121 /// <param name="WhereLambda"></param> 122 /// <returns></returns> 123 bool GetAny(Expression<Func<T, bool>> WhereLambda = null); 124 125 126 /// <summary> 127 /// 根据查询过条件进行分页 128 /// </summary> 129 /// <typeparam name="T"></typeparam> 130 /// <typeparam name="TKey"></typeparam> 131 /// <param name="PageIndex">当前页面</param> 132 /// <param name="PageSize">页面的大小</param> 133 /// <param name="TotalCount">总记录数</param> 134 /// <param name="OrderBy">排序的条件</param> 135 /// <param name="WhereLambda">查询条件</param> 136 /// <param name="IsOrder">是否正序</param> 137 /// <returns></returns> 138 List<T> Pagination<TKey>(int PageIndex, int PageSize, out int TotalCount, Expression<Func<T, TKey>> OrderBy, Expression<Func<T, bool>> WhereLambda = null, bool IsOrder = true) ; 139 140 141 /// <summary> 142 /// 根据查询条件进行做分页查询 143 /// </summary> 144 /// <typeparam name="T">查询的对象</typeparam> 145 /// <param name="PageIndex">当前的页码</param> 146 /// <param name="PageSize">每页的大小</param> 147 /// <param name="TotalCount">总页数</param> 148 /// <param name="ordering">排序条件</param> 149 /// <param name="WhereLambda">查询条件</param> 150 /// <returns></returns> 151 List<T> Pagination(int PageIndex, int PageSize, out int TotalCount, string ordering, Expression<Func<T, bool>> WhereLambda = null) ; 152 153 154 /// <summary> 155 /// 根据查询条件进行转化 156 /// </summary> 157 /// <typeparam name="T"></typeparam> 158 /// <param name="WhereLambda"></param> 159 /// <returns></returns> 160 List<T> GetSelect(Expression<Func<T, bool>> WhereLambda) ; 161 162 163 /// <summary> 164 /// 执行存储过程或自定义sql语句--返回集合 165 /// </summary> 166 /// <typeparam name="T"></typeparam> 167 /// <param name="Sql"></param> 168 /// <param name="Parms"></param> 169 /// <param name="CmdType"></param> 170 /// <returns></returns> 171 List<T> QueryProc(string Sql, List<MySqlParameter> Parms, CommandType CmdType = CommandType.Text) ; 172 173 174 /// <summary> 175 /// 回滚 176 /// </summary> 177 /// <typeparam name="T"></typeparam> 178 void RollBackChanges() ; 179 180 181 }
仓储代码：
1 /// <summary> 2 /// 3 /// </summary> 4 public class Repository<T> : IRepository<T>, IDisposable where T : class 5 { 6 private string _ConnectionString = "Server=127.0.0.1,1433;database=MarketDB;uid=sa;pwd=123456"; 7 /// <summary> 8 /// 9 /// </summary> 10 private DbContext _DbContextHandle = new DataContext(@"Server=127.0.0.1,1433;database=MarketDB;uid=sa;pwd=123456");//此处进行调用EF的DBContext的实体类或者通过工厂化模式来进行调用。 11 12 public Repository() 13 { 14 } 15 public Repository(string connectionString) 16 { 17 if (string.IsNullOrEmpty(connectionString)) 18 { 19 throw new ArgumentNullException("Connection String"); 20 } 21 else 22 { 23 this._ConnectionString = connectionString; 24 _DbContextHandle = new DbContext(connectionString); 25 } 26 } 27 /// <summary> 28 /// 添加一个对象 29 /// </summary> 30 /// <typeparam name="T"></typeparam> 31 /// <param name="Entity"></param> 32 /// <returns></returns> 33 public bool Add(T Entity) 34 { 35 using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required)) 36 { 37 T t = _DbContextHandle.Set<T>().Add(Entity); 38 int Count = _DbContextHandle.SaveChanges(); 39 trans.Complete(); 40 return Count > 0; 41 } 42 } 43 44 /// <summary> 45 /// 批量的插入数据 46 /// </summary> 47 /// <typeparam name="T"></typeparam> 48 /// <param name="Entity"></param> 49 /// <returns></returns> 50 public bool AddRange(List<T> Entity) 51 { 52 using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required)) 53 { 54 _DbContextHandle.Set<T>().AddRange(Entity); 55 int Count = _DbContextHandle.SaveChanges(); 56 trans.Complete(); 57 return Count > 0; 58 } 59 } 60 61 /// <summary> 62 /// 根据查询条件进行删除对象 63 /// </summary> 64 /// <typeparam name="T"></typeparam> 65 /// <param name="whereLambda">查询条件</param> 66 /// <returns></returns> 67 public bool Delete(Expression<Func<T, bool>> whereLambda) 68 { 69 using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required)) 70 { 71 var EntityModel = _DbContextHandle.Set<T>().Where(whereLambda).FirstOrDefault(); 72 if (EntityModel != null) 73 { 74 _DbContextHandle.Set<T>().Remove(EntityModel); 75 int Count = _DbContextHandle.SaveChanges(); 76 trans.Complete(); 77 return Count > 0; 78 } 79 return false; 80 } 81 } 82 83 84 /// <summary> 85 /// 删除单个对象的实体 86 /// </summary> 87 /// <typeparam name="T"></typeparam> 88 /// <param name="Entity">实体对象</param> 89 /// <returns></returns> 90 public bool Delete(T Entity) 91 { 92 using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required)) 93 { 94 _DbContextHandle.Set<T>().Attach(Entity); 95 _DbContextHandle.Set<T>().Remove(Entity); 96 int Count = _DbContextHandle.SaveChanges(); 97 trans.Complete(); 98 return Count > 0; 99 } 100 } 101 102 /// <summary> 103 /// 批量的进行更新数据 104 /// </summary> 105 /// <typeparam name="T"></typeparam> 106 /// <param name="Entity"></param> 107 /// <returns></returns> 108 public bool Update(List<T> Entity) 109 { 110 int Count = 0; 111 using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required)) 112 { 113 if (Entity != null) 114 { 115 foreach (var items in Entity) 116 { 117 var EntityModel = _DbContextHandle.Entry(Entity); 118 _DbContextHandle.Set<T>().Attach(items); 119 EntityModel.State = EntityState.Modified; 120 } 121 } 122 Count = _DbContextHandle.SaveChanges(); 123 trans.Complete(); 124 } 125 126 return Count > 0; 127 } 128 129 130 /// <summary> 131 /// 进行修改单个实体对象 132 /// </summary> 133 /// <typeparam name="T"></typeparam> 134 /// <param name="Entity">实体对象</param> 135 /// <returns></returns> 136 public bool Update(T Entity) 137 { 138 using (TransactionScope trans = new TransactionScope()) 139 { 140 var EntityModel = _DbContextHandle.Entry<T>(Entity); 141 _DbContextHandle.Set<T>().Attach(Entity); 142 EntityModel.State = EntityState.Modified; 143 int Count = _DbContextHandle.SaveChanges(); 144 trans.Complete(); 145 return Count > 0; 146 } 147 } 148 149 /// <summary> 150 /// 批量的修改 151 /// </summary> 152 /// <typeparam name="T"></typeparam> 153 /// <param name="WhereLambda"></param> 154 /// <param name="UpdateLambda"></param> 155 /// <returns></returns> 156 public bool Update(Expression<Func<T, bool>> WhereLambda, Expression<Func<T, T>> UpdateLambda) 157 { 158 //_DbContextHandle.Set<T>().Where(WhereLambda).Update<T>(UpdateLambda); 159 //return _DbContextHandle.SaveChanges() > 0; 160 return false; 161 } 162 163 164 /// <summary> 165 /// 查询条件进行修改 166 /// </summary> 167 /// <typeparam name="T"></typeparam> 168 /// <param name="model"></param> 169 /// <param name="WhereLambda"></param> 170 /// <param name="ModifiedProNames"></param> 171 /// <returns></returns> 172 public bool Update(T model, Expression<Func<T, bool>> WhereLambda, params string[] ModifiedProNames) 173 { 174 //查询要修改的数据 175 List<T> ListModifing = _DbContextHandle.Set<T>().Where(WhereLambda).ToList(); 176 Type t = typeof(T); 177 List<PropertyInfo> ProInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList(); 178 Dictionary<string, PropertyInfo> DitProList = new Dictionary<string, PropertyInfo>(); 179 ProInfos.ForEach(p => 180 { 181 if (ModifiedProNames.Contains(p.Name)) 182 { 183 DitProList.Add(p.Name, p); 184 } 185 }); 186 187 if (DitProList.Count <= 0) 188 { 189 throw new Exception("指定修改的字段名称有误或为空"); 190 } 191 foreach (var item in DitProList) 192 { 193 PropertyInfo proInfo = item.Value; 194 object newValue = proInfo.GetValue(model, null); 195 //批量进行修改相互对应的属性 196 foreach (T oModel in ListModifing) 197 { 198 proInfo.SetValue(oModel, newValue, null);//设置其中新的值 199 } 200 } 201 202 return _DbContextHandle.SaveChanges() > 0; 203 } 204 /// <summary> 205 /// 释放缓存 206 /// </summary> 207 public void Dispose() 208 { 209 _DbContextHandle.Dispose(); 210 } 211 212 /// <summary> 213 /// 查询单个对象 214 /// </summary> 215 /// <typeparam name="T"></typeparam> 216 /// <param name="ID">主键ID</param> 217 /// <returns></returns> 218 public T FindByID(dynamic ID) 219 { 220 DbSet<T> db = _DbContextHandle.Set<T>(); 221 var result =db.Find(ID); 222 return result; 223 224 //return _DbContextHandle.Set<T>().Find(ID) ?? null; 225 } 226 227 228 /// <summary> 229 /// 获取全部数据的列表 230 /// </summary> 231 /// <typeparam name="T"></typeparam> 232 /// <param name="Order">排序</param> 233 /// <returns></returns> 234 public List<T> GetAll(string orderString = "") 235 { 236 if (string.IsNullOrEmpty(orderString)) 237 { 238 return _DbContextHandle.Set<T>().ToList(); 239 } 240 else 241 { 242 return null; // _DbContextHandle.Set<T>().OrderBy(orderString).ToList(); 243 } 244 } 245 246 /// <summary> 247 ///根据查询条件进行查询列表 248 /// </summary> 249 /// <typeparam name="T"></typeparam> 250 /// <param name="WhereLambda"></param> 251 /// <returns></returns> 252 public List<T> GetAllQuery(Expression<Func<T, bool>> WhereLambda = null) 253 { 254 return WhereLambda != null ? _DbContextHandle.Set<T>().Where(WhereLambda).ToList() ?? null : _DbContextHandle.Set<T>().ToList() ?? null; 255 } 256 257 /// <summary> 258 ///判断对象是否存在 259 /// </summary> 260 /// <typeparam name="T"></typeparam> 261 /// <param name="WhereLambda"></param> 262 /// <returns></returns> 263 public bool GetAny(Expression<Func<T, bool>> WhereLambda = null) 264 { 265 return WhereLambda != null ? _DbContextHandle.Set<T>().Where(WhereLambda).Any() : _DbContextHandle.Set<T>().Any(); 266 } 267 268 /// <summary> 269 /// 获取查询条件的记录数 270 /// </summary> 271 /// <typeparam name="T"></typeparam> 272 /// <param name="WhereLambda"></param> 273 /// <returns></returns> 274 public int GetCount(Expression<Func<T, bool>> WhereLambda = null) 275 { 276 return WhereLambda != null ? _DbContextHandle.Set<T>().Where(WhereLambda).Count() : _DbContextHandle.Set<T>().Count(); 277 } 278 279 280 /// <summary> 281 /// 获取单条的记录 282 /// </summary> 283 /// <typeparam name="T"></typeparam> 284 /// <param name="WhereLambda"></param> 285 /// <returns></returns> 286 public T GetFristDefault(Expression<Func<T, bool>> WhereLambda = null) 287 { 288 return WhereLambda != null ? _DbContextHandle.Set<T>().Where(WhereLambda).FirstOrDefault() ?? null : _DbContextHandle.Set<T>().FirstOrDefault() ?? null; 289 } 290 291 292 /// <summary> 293 /// 查询对象的转化 294 /// </summary> 295 /// <typeparam name="T"></typeparam> 296 /// <param name="WhereLambda"></param> 297 /// <returns></returns> 298 public List<T> GetSelect(Expression<Func<T, bool>> WhereLambda) 299 { 300 return _DbContextHandle.Set<T>().Where(WhereLambda).ToList() ?? null; 301 } 302 303 /// <summary> 304 ///根据查询条件进行分页 305 /// </summary> 306 /// <typeparam name="T"></typeparam> 307 /// <param name="PageIndex">当前页</param> 308 /// <param name="PageSize">每页的大小</param> 309 /// <param name="TotalCount">总记录数</param> 310 /// <param name="ordering">排序条件</param> 311 /// <param name="WhereLambda">查询条件</param> 312 /// <returns></returns> 313 public List<T> Pagination(int PageIndex, int PageSize, out int TotalCount, string Ordering, Expression<Func<T, bool>> WhereLambda = null) 314 { 315 //分页的时候一定要注意 Order 一定在Skip 之前 316 //var QueryList = _DbContextHandle.Set<T>().OrderBy(Ordering); 317 //if (WhereLambda != null) 318 //{ 319 // QueryList = QueryList.Where(WhereLambda); 320 //} 321 322 //TotalCount = QueryList.Count(); 323 //return QueryList.Skip(PageSize * (PageIndex - 1)).Take(PageSize).ToList() ?? null; 324 TotalCount = 0; 325 return null; 326 } 327 328 /// <summary> 329 ///根据查询条件进行分页 330 /// </summary> 331 /// <typeparam name="T"></typeparam> 332 /// <param name="PageIndex">当前页</param> 333 /// <param name="PageSize">每页的大小</param> 334 /// <param name="TotalCount">总记录数</param> 335 /// <param name="OrderBy">排序条件</param> 336 /// <param name="WhereLambda">查询的条件</param> 337 /// <param name="IsOrder"></param> 338 /// <returns></returns> 339 public List<T> Pagination<TKey>(int PageIndex, int PageSize, out int TotalCount, Expression<Func<T, TKey>> OrderBy, Expression<Func<T, bool>> WhereLambda = null, bool IsOrder = true) 340 { 341 //分页的时候一定要注意 Order一定在Skip 之前 342 IQueryable<T> QueryList = IsOrder == true ? _DbContextHandle.Set<T>().OrderBy(OrderBy) : _DbContextHandle.Set<T>().OrderByDescending(OrderBy); 343 344 if (WhereLambda != null) 345 { 346 QueryList = QueryList.Where(WhereLambda); 347 } 348 349 TotalCount = QueryList.Count(); 350 return QueryList.Skip(PageSize * (PageIndex - 1)).Take(PageSize).ToList() ?? null; 351 } 352 353 354 /// <summary> 355 /// 执行存储过程的SQL 语句 356 /// </summary> 357 /// <typeparam name="T"></typeparam> 358 /// <param name="Sql">执行的SQL语句</param> 359 /// <param name="Parms">SQL 语句的参数</param> 360 /// <param name="CmdType"></param> 361 /// <returns></returns> 362 public List<T> QueryProc(string Sql, List<MySqlParameter> Parms, CommandType CmdType = CommandType.Text) 363 { 364 //进行执行存储过程 365 if (CmdType == CommandType.StoredProcedure) 366 { 367 StringBuilder paraNames = new StringBuilder(); 368 foreach (var item in Parms) 369 { 370 paraNames.Append(string.Format(" @{item},", item)); 371 } 372 Sql = paraNames.Length > 0 ? string.Format("exec {Sql} {paraNames.ToString().Trim(',')}", Sql) : string.Format("exec {Sql} ", Sql); 373 } 374 return _DbContextHandle.Set<T>().SqlQuery(Sql, Parms.ToArray()).ToList(); 375 } 376 377 378 /// <summary> 379 /// 进行回滚 380 /// </summary> 381 /// <typeparam name="T"></typeparam> 382 public void RollBackChanges() 383 { 384 var Query = _DbContextHandle.ChangeTracker.Entries().ToList(); 385 386 Query.ForEach(p => p.State = EntityState.Unchanged); 387 } 388 389 }
调用示例：
1 /// <summary> 2 /// 3 /// </summary> 4 /// <param name="employeeCode"></param> 5 /// <returns></returns> 6 public Employee GetEmployee(string employeeCode) 7 { 8 if(string.IsNullOrEmpty(employeeCode)) 9 { 10 return null; 11 } 12 Repository<Employee> employeeRepository = new Repository<Employee>(); 13 Employee emp = employeeRepository.GetFristDefault(p => p.EmployeeCode == employeeCode); 14 return emp; 15 }
附加自定义查询返回DataSet：
1 public class BaseSqlDal 2 { 3 4 //private string _ConnectionString = "Server=127.0.0.1,1433;database=MarketDB;uid=sa;pwd=123456"; 5 /// <summary> 6 /// 7 /// </summary> 8 private DbContext _DbContextHandle = new DataContext(@"Server=127.0.0.1,1433;database=MarketDB;uid=sa;pwd=123456");//此处进行调用EF的DBContext的实体类或者通过工厂化模式来进行调用。 9 10 public List<T> Query<T>(string cmdString) where T : class 11 { 12 return _DbContextHandle.Database.SqlQuery<T>(cmdString).ToList(); 13 } 14 public List<T> Query<T>(string cmdString, params object[] parameters) where T : class 15 { 16 17 return _DbContextHandle.Database.SqlQuery<T>(cmdString, parameters).ToList(); 18 } 19 /// <summary> 20 /// 21 /// </summary> 22 /// <param name="cmdString"></param> 23 /// <param name="dicParameter"></param> 24 /// <param name="sqlDbType"></param> 25 /// <returns></returns> 26 public DataSet QueryDataSet(string cmdString, Dictionary<string, object> dicParameter, SqlDbType sqlDbType) 27 { 28 DataSet ds = new DataSet(); 29 SqlConnection connection = _DbContextHandle.Database.Connection as SqlConnection; 30 SqlCommand cmd = new SqlCommand(cmdString, connection); 31 return ds; 32 } 33 }
