原文
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 /// 12 /// 新增 13 /// 14 /// 实体 15 /// 返回受影响行数 16 public bool Add (T entity) where T : class 17 { 18 using (SysDb db = new SysDb (strConn)) 19 { 20 db.Entry (entity).State = EntityState.Added; 21 return db.SaveChanges() > 0; 22 } 23 } 24 25 /// 26 /// 修改 27 /// 28 /// 实体 29 /// 返回受影响行数 30 public bool Update (T entity) where T : class 31 { 32 using (SysDb db = new SysDb (strConn)) 33 { 34 db.Set ().Attach(entity); 35 db.Entry (entity).State = EntityState.Modified; 36 return db.SaveChanges() > 0; 37 } 38 } 39 40 /// 41 /// 删除 42 /// 43 /// 实体 44 /// 返回受影响行数 45 public bool Delete (T entity) where T : class 46 { 47 using (SysDb db = new SysDb (strConn)) 48 { 49 db.Set ().Attach(entity); 50 db.Entry (entity).State = EntityState.Deleted; 51 return db.SaveChanges() > 0; 52 } 53 } 54 55 /// 56 /// 根据条件删除 57 /// 58 /// 删除条件 59 /// 返回受影响行数 60 public bool DeleteByConditon (Expression > deleWhere) where T : class 61 { 62 using (SysDb db = new SysDb (strConn)) 63 { 64 List entitys = db.Set ().Where(deleWhere).ToList(); 65 entitys.ForEach(m => db.Entry (m).State = EntityState.Deleted); 66 return db.SaveChanges() > 0; 67 } 68 } 69 70 /// 71 /// 查找单个 72 /// 73 /// 主键 74 /// (int id) where T : class 76 { 77 using (SysDb db = new SysDb (strConn)) 78 { 79 return db.Set ().Find(id); 80 } 81 } 82 83 /// 84 /// 查找单个 85 /// 86 /// 查询条件 87 /// (Expression > seleWhere) where T : class 89 { 90 using (SysDb db = new SysDb (strConn)) 91 { 92 return db.Set ().AsExpandable().FirstOrDefault(seleWhere); 93 } 94 } 95 96 /// 97 /// 获取所有实体集合 98 /// 99 /// GetAll () where T : class101 {102 using (SysDb db = new SysDb (strConn))103 {104 return db.Set ().AsExpandable().ToList ();105 }106 }107 108 /// 109 /// 获取所有实体集合(单个排序)110 /// 111 /// GetAll (Expression > orderWhere, bool isDesc) where T : class113 {114 using (SysDb db = new SysDb (strConn))115 {116 return CommonSort(db.Set ().AsExpandable(), orderWhere, isDesc).ToList ();117 }118 }119 120 /// 121 /// 获取所有实体集合(多个排序)122 /// 123 /// GetAll (params OrderModelField[] orderByExpression) where T : class125 {126 using (SysDb db = new SysDb (strConn))127 {128 return CommonSort(db.Set ().AsExpandable(), orderByExpression).ToList();129 }130 }131 132 /// 133 /// 单个排序通用方法134 /// 135 /// 排序字段 136 /// 要排序的数据137 /// 排序条件138 /// 是否倒序139 /// 排序后的集合 140 public IQueryable CommonSort (IQueryable data, Expression > orderWhere, bool isDesc) where T : class141 {142 if (isDesc)143 {144 return data.OrderByDescending(orderWhere);145 }146 else147 {148 return data.OrderBy(orderWhere);149 }150 }151 152 /// 153 /// 多个排序通用方法154 /// 155 /// 排序字段 156 /// 要排序的数据157 /// 字典集合(排序条件,是否倒序)158 /// 排序后的集合 159 public IQueryable CommonSort (IQueryable data, params OrderModelField[] orderByExpression) where T : class160 {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 else180 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 (resultExp);186 }187 }188 return data;189 }190 191 /// 192 /// 根据条件查询实体集合193 /// 194 /// 查询条件 lambel表达式195 /// GetList (Expression > seleWhere) where T : class197 {198 using (SysDb db = new SysDb (strConn))199 {200 return db.Set ().AsExpandable().Where(seleWhere).ToList();201 }202 }203 204 /// 205 /// 根据条件查询实体集合206 /// 207 /// 查询条件 lambel表达式208 /// GetList (Expression > seleWhere, IEnumerable conditions) where T : class210 {211 using (SysDb db = new SysDb (strConn))212 {213 214 return db.Set ().AsExpandable().WhereIn (seleWhere, conditions).ToList();215 }216 }217 218 /// 219 /// 根据条件查询实体集合(单个字段排序)220 /// 221 /// 查询条件 lambel表达式222 /// GetList (Expression > seleWhere, Expression > orderWhere, bool isDesc) where T : class224 {225 using (SysDb db = new SysDb (strConn))226 {227 return CommonSort(db.Set ().AsExpandable().Where(seleWhere), orderWhere, isDesc).ToList();228 }229 }230 231 /// 232 /// 根据条件查询实体集合(多个字段排序)233 /// 234 /// 查询条件 lambel表达式235 /// GetList (Expression > seleWhere, params OrderModelField[] orderByExpression) where T : class237 {238 using (SysDb db = new SysDb (strConn))239 {240 return CommonSort(db.Set ().AsExpandable().Where(seleWhere), orderByExpression).ToList();241 }242 }243 244 /// 245 /// 获取分页集合(无条件无排序)246 /// 247 /// GetListPaged (int pageIndex, int pageSize, out int totalcount) where T : class249 {250 using (SysDb db = new SysDb (strConn))251 {252 totalcount = db.Set ().AsExpandable().Count();//获取总数253 //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序 AsExpandable是linqkit.dll中的方法254 return db.Set ().AsExpandable().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();255 }256 }257 258 /// 259 /// 获取分页集合(无条件单个排序)260 /// 261 /// GetListPaged (int pageIndex, int pageSize, Expression > orderWhere, bool isDesc, out int totalcount) where T : class263 {264 using (SysDb db = new SysDb (strConn))265 {266 totalcount = db.Set ().AsExpandable().Count();//获取总数267 //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序 AsExpandable是linqkit.dll中的方法268 return CommonSort(db.Set ().AsExpandable(), orderWhere, isDesc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();269 }270 }271 272 /// 273 /// 获取分页集合(无条件多字段排序)274 /// 275 /// GetListPaged (int pageIndex, int pageSize, out int totalcount, params OrderModelField[] orderByExpression) where T : class277 {278 using (SysDb db = new SysDb (strConn))279 {280 totalcount = db.Set ().AsExpandable().Count();//获取总数281 //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序 AsExpandable是linqkit.dll中的方法282 return CommonSort(db.Set ().AsExpandable(), orderByExpression).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();283 }284 }285 286 /// 287 /// 获取分页集合(有条件无排序)288 /// 289 /// GetListPaged (int pageIndex, int pageSize, Expression > seleWhere, out int totalcount) where T : class291 {292 using (SysDb db = new SysDb (strConn))293 {294 totalcount = db.Set ().AsExpandable().Where(seleWhere).Count();//获取总数295 //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序 AsExpandable是linqkit.dll中的方法296 return db.Set ().AsExpandable().Where(seleWhere).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();297 }298 }299 300 /// 301 /// 获取分页集合(有条件单个排序)302 /// 303 /// GetListPaged (int pageIndex, int pageSize, Expression > seleWhere,305 Expression > orderWhere, bool isDesc, out int totalcount) where T : class306 {307 using (SysDb db = new SysDb (strConn))308 {309 totalcount = db.Set ().AsExpandable().Where(seleWhere).Count();//获取总数310 //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序 AsExpandable是linqkit.dll中的方法311 return CommonSort(db.Set ().AsExpandable().Where(seleWhere), orderWhere, isDesc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();312 }313 }314 315 /// 316 /// 获取分页集合(有条件多字段排序)317 /// 318 /// GetListPaged (int pageIndex, int pageSize, Expression > seleWhere,320 out int totalcount, params OrderModelField[] orderModelFiled) where T : class321 {322 using (SysDb db = new SysDb (strConn))323 {324 totalcount = db.Set ().AsExpandable().Where(seleWhere).Count();//获取总数325 //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序 AsExpandable是linqkit.dll中的方法326 return CommonSort(db.Set ().AsExpandable().Where(seleWhere), orderModelFiled).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();327 }328 }329 #endregion330 331 #region 原始sql操作332 /// 333 /// 执行操作334 /// 335 /// 336 /// 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 /// 346 /// 查询列表347 /// 348 /// QueryList (string sql, params object[] paras) where T : class353 {354 using (SysDb db = new SysDb(strConn))355 {356 return db.Database.SqlQuery (sql, paras).ToList();357 }358 }359 360 /// 361 /// 查询单个362 /// 363 /// (string sql, params object[] paras) where T : class368 {369 using (SysDb db = new SysDb (strConn))370 {371 return db.Database.SqlQuery (sql, paras).FirstOrDefault();372 }373 }374 375 /// 376 /// 执行事务377 /// 378 /// 379 /// 380 public void ExecuteTransaction(List lsSql, List lsParas)381 {382 using (SysDb db = new SysDb(strConn))383 {384 using (var tran = db.Database.BeginTransaction())385 {386 try387 {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 #endregion411 #endregion412 413 #region 通用属性414 /// 415 /// 获取数据库服务器当前时间。416 /// 417 public DateTime ServerTime418 {419 get420 {421 using (SysDb db = new SysDb(strConn))422 {423 String sql = "SELECT GETDATE()";424 Object objServerTime = db.Database.SqlQuery