EntityFramework6写的数据访问框架之二BaseDAL
来源：互联网 发布：mac如何切换双系统 编辑：程序博客网 时间：2024/06/11 21:09
using System;using System.Collections.Generic;using System.Data;using System.Data.Entity;using System.Linq;using System.Linq.Expressions;using System.Text;using LinqKit;using System.Configuration;using System.Data.SqlClient;using System.Collections;namespace MyTest{ public abstract class BaseDAL { public BaseDAL() { } public abstract BaseDb<T> GetDbContext<T>() where T : class; #region 通用增删改查 #region 非原始sql语句方式 /// <summary> /// 新增 /// </summary> /// <param name="entity">实体</param> /// <returns>返回受影响行数</returns> protected bool Add<T>(T entity) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { db.Entry<T>(entity).State = EntityState.Added; return db.SaveChanges() > 0; } } /// <summary> /// 修改 /// </summary> /// <param name="entity">实体</param> /// <returns>返回受影响行数</returns> protected bool Update<T>(T entity) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { db.Set<T>().Attach(entity); db.Entry<T>(entity).State = EntityState.Modified; return db.SaveChanges() > 0; } } /// <summary> /// 删除 /// </summary> /// <param name="entity">实体</param> /// <returns>返回受影响行数</returns> protected bool Delete<T>(T entity) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { db.Set<T>().Attach(entity); db.Entry<T>(entity).State = EntityState.Deleted; return db.SaveChanges() > 0; } } /// <summary> /// 根据条件删除 /// </summary> /// <param name="deleWhere">删除条件</param> /// <returns>返回受影响行数</returns> protected bool DeleteByConditon<T>(Expression<Func<T, bool>> deleWhere) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { List<T> entitys = db.Set<T>().Where(deleWhere).ToList(); entitys.ForEach(m => db.Entry<T>(m).State = EntityState.Deleted); return db.SaveChanges() > 0; } } /// <summary> /// 查找单个 /// </summary> /// <param name="id">主键</param> /// <returns></returns> protected T GetSingleById<T>(int id) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { return db.Set<T>().Find(id); } } /// <summary> /// 查找单个 /// </summary> /// <param name="seleWhere">查询条件</param> /// <returns></returns> protected T GetSingle<T>(Expression<Func<T, bool>> seleWhere) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { return db.Set<T>().AsExpandable().FirstOrDefault(seleWhere); } } /// <summary> /// 获取所有实体集合 /// </summary> /// <returns></returns> protected List<T> GetAll<T>() where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { return db.Set<T>().AsExpandable().ToList<T>(); } } /// <summary> /// 获取所有实体集合(单个排序) /// </summary> /// <returns></returns> protected List<T> GetAll<T, Tkey>(Expression<Func<T, Tkey>> orderWhere, bool isDesc) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { return CommonSort(db.Set<T>().AsExpandable(), orderWhere, isDesc).ToList<T>(); } } /// <summary> /// 获取所有实体集合(多个排序) /// </summary> /// <returns></returns> protected List<T> GetAll<T>(params OrderModelField[] orderByExpression) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { return CommonSort(db.Set<T>().AsExpandable(), orderByExpression).ToList(); } } /// <summary> /// 单个排序通用方法 /// </summary> /// <typeparam name="Tkey">排序字段</typeparam> /// <param name="data">要排序的数据</param> /// <param name="orderWhere">排序条件</param> /// <param name="isDesc">是否倒序</param> /// <returns>排序后的集合</returns> protected IQueryable<T> CommonSort<T, Tkey>(IQueryable<T> data, Expression<Func<T, Tkey>> orderWhere, bool isDesc) where T : class { if (isDesc) { return data.OrderByDescending(orderWhere); } else { return data.OrderBy(orderWhere); } } /// <summary> /// 多个排序通用方法 /// </summary> /// <typeparam name="Tkey">排序字段</typeparam> /// <param name="data">要排序的数据</param> /// <param name="orderWhereAndIsDesc">字典集合(排序条件,是否倒序)</param> /// <returns>排序后的集合</returns> protected IQueryable<T> CommonSort<T>(IQueryable<T> data, params OrderModelField[] orderByExpression) where T : class { //创建表达式变量参数 var parameter = Expression.Parameter(typeof(T), "o"); if (orderByExpression != null && orderByExpression.Length > 0) { for (int i = 0; i < orderByExpression.Length; i++) { //根据属性名获取属性 var property = typeof(T).GetProperty(orderByExpression[i].PropertyName); //创建一个访问属性的表达式 var propertyAccess = Expression.MakeMemberAccess(parameter, property); var orderByExp = Expression.Lambda(propertyAccess, parameter); string OrderName = ""; if (i > 0) { OrderName = orderByExpression[i].IsDESC ? "ThenByDescending" : "ThenBy"; } else OrderName = orderByExpression[i].IsDESC ? "OrderByDescending" : "OrderBy"; MethodCallExpression resultExp = Expression.Call(typeof(Queryable), OrderName, new Type[] { typeof(T), property.PropertyType }, data.Expression, Expression.Quote(orderByExp)); data = data.Provider.CreateQuery<T>(resultExp); } } return data; } /// <summary> /// 根据条件查询实体集合 /// </summary> /// <param name="seleWhere">查询条件 lambel表达式</param> /// <returns></returns> protected List<T> GetList<T>(Expression<Func<T, bool>> seleWhere) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { return db.Set<T>().AsExpandable().Where(seleWhere).ToList(); } } /// <summary> /// 根据条件查询实体集合 /// </summary> /// <param name="seleWhere">查询条件 lambel表达式</param> /// <returns></returns> protected List<T> GetList<T, TValue>(Expression<Func<T, TValue>> seleWhere, IEnumerable<TValue> conditions) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { return db.Set<T>().AsExpandable().WhereIn<T, TValue>(seleWhere, conditions).ToList(); } } /// <summary> /// 根据条件查询实体集合(单个字段排序) /// </summary> /// <param name="seleWhere">查询条件 lambel表达式</param> /// <returns></returns> protected List<T> GetList<T, Tkey>(Expression<Func<T, bool>> seleWhere, Expression<Func<T, Tkey>> orderWhere, bool isDesc) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { return CommonSort(db.Set<T>().AsExpandable().Where(seleWhere), orderWhere, isDesc).ToList(); } } /// <summary> /// 根据条件查询实体集合(多个字段排序) /// </summary> /// <param name="seleWhere">查询条件 lambel表达式</param> /// <returns></returns> protected List<T> GetList<T>(Expression<Func<T, bool>> seleWhere, params OrderModelField[] orderByExpression) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { return CommonSort(db.Set<T>().AsExpandable().Where(seleWhere), orderByExpression).ToList(); } } /// <summary> /// 获取分页集合(无条件无排序) /// </summary> /// <returns></returns> protected List<T> GetListPaged<T, Tkey>(int pageIndex, int pageSize, out int totalcount) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { totalcount = db.Set<T>().AsExpandable().Count();//获取总数 //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序 AsExpandable是linqkit.dll中的方法 return db.Set<T>().AsExpandable().Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); } } /// <summary> /// 获取分页集合(无条件单个排序) /// </summary> /// <returns></returns> protected List<T> GetListPaged<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, Tkey>> orderWhere, bool isDesc, out int totalcount) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { totalcount = db.Set<T>().AsExpandable().Count();//获取总数 //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序 AsExpandable是linqkit.dll中的方法 return CommonSort(db.Set<T>().AsExpandable(), orderWhere, isDesc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); } } /// <summary> /// 获取分页集合(无条件多字段排序) /// </summary> /// <returns></returns> protected List<T> GetListPaged<T>(int pageIndex, int pageSize, out int totalcount, params OrderModelField[] orderByExpression) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { totalcount = db.Set<T>().AsExpandable().Count();//获取总数 //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序 AsExpandable是linqkit.dll中的方法 return CommonSort(db.Set<T>().AsExpandable(), orderByExpression).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); } } /// <summary> /// 获取分页集合(有条件无排序) /// </summary> /// <returns></returns> protected List<T> GetListPaged<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, bool>> seleWhere, out int totalcount) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { totalcount = db.Set<T>().AsExpandable().Where(seleWhere).Count();//获取总数 //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序 AsExpandable是linqkit.dll中的方法 return db.Set<T>().AsExpandable().Where(seleWhere).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); } } /// <summary> /// 获取分页集合(有条件单个排序) /// </summary> /// <returns></returns> protected List<T> GetListPaged<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, bool>> seleWhere, Expression<Func<T, Tkey>> orderWhere, bool isDesc, out int totalcount) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { totalcount = db.Set<T>().AsExpandable().Where(seleWhere).Count();//获取总数 //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序 AsExpandable是linqkit.dll中的方法 return CommonSort(db.Set<T>().AsExpandable().Where(seleWhere), orderWhere, isDesc).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); } } /// <summary> /// 获取分页集合(有条件多字段排序) /// </summary> /// <returns></returns> protected List<T> GetListPaged<T>(int pageIndex, int pageSize, Expression<Func<T, bool>> seleWhere, out int totalcount, params OrderModelField[] orderModelFiled) where T : class { using (BaseDb<T> db = this.GetDbContext<T>()) { totalcount = db.Set<T>().AsExpandable().Where(seleWhere).Count();//获取总数 //需要增加AsExpandable(),否则查询的是所有数据到内存，然后再排序 AsExpandable是linqkit.dll中的方法 return CommonSort(db.Set<T>().AsExpandable().Where(seleWhere), orderModelFiled).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); } } #endregion #endregion #region sql查询 protected int ProcedureQuery<T>(string sp, params SqlParameter[] parameters) where T : class { using (var context = this.GetDbContext<T>()) { using (context.Database.Connection) { context.Database.Connection.Open(); var cmd = context.Database.Connection.CreateCommand(); cmd.CommandText = sp; cmd.CommandType = CommandType.StoredProcedure; if (parameters != null && parameters.Count() > 0) { cmd.Parameters.AddRange(parameters); } return cmd.ExecuteNonQuery(); } } } protected object ProcedureToScalar<T>(string sql, params SqlParameter[] parameters) where T : class { using (var context = this.GetDbContext<T>()) { using (context.Database.Connection) { context.Database.Connection.Open(); var cmd = context.Database.Connection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.StoredProcedure; if (parameters != null && parameters.Count() > 0) { cmd.Parameters.AddRange(parameters); } return cmd.ExecuteScalar(); } } } /// <summary> /// 执行存储过程 带返回总数以及结果集 /// </summary> /// <param name="storedProcName"></param> /// <param name="parameters"></param> /// <returns></returns> protected DataSet ProcedureDataSet<T>(string storedProcName, params IDataParameter[] parameters) where T : class { using (var context = this.GetDbContext<T>()) { using (context.Database.Connection) { context.Database.Connection.Open(); var cmd = context.Database.Connection.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = storedProcName; cmd.CommandTimeout = 60 * 5; if (parameters != null) { foreach (SqlParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } SqlDataAdapter sdap = new SqlDataAdapter(); sdap.SelectCommand = (SqlCommand)cmd; DataSet set = new DataSet(); sdap.Fill(set); return set; } } } protected List<T> ProcedureToList<T>(string sql, params SqlParameter[] parameters) where T : class,new() { using (var context = this.GetDbContext<T>()) { using (context.Database.Connection) { context.Database.Connection.Open(); var cmd = context.Database.Connection.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = sql; if (parameters != null && parameters.Count() > 0) { cmd.Parameters.AddRange(parameters); } SqlDataAdapter sdap = new SqlDataAdapter(); sdap.SelectCommand = cmd as SqlCommand; DataTable dt = new DataTable(); sdap.Fill(dt); return dt.ToList<T>(); } } } /// <summary> /// 执行SQL语句,返回执行结果（int） /// </summary> /// <typeparam name="TElement"></typeparam> /// <param name="sp"></param> /// <param name="parameters">new SqlParameter() </param> /// <returns></returns> protected int ExecuteSql<T>(string strsql) where T : class { SqlParameter[] parameters = new SqlParameter[0]; return ExecuteSql<T>(strsql, parameters); } /// <summary> /// 执行SQL语句,返回执行结果（int） /// </summary> /// <typeparam name="TElement"></typeparam> /// <param name="sp"></param> /// <param name="parameters">new SqlParameter() </param> /// <returns></returns> protected int ExecuteSql<T>(string strsql, params SqlParameter[] parameters) where T : class { using (var context = this.GetDbContext<T>()) { using (context.Database.Connection) { context.Database.Connection.Open(); using (var cmd = context.Database.Connection.CreateCommand()) { cmd.CommandText = strsql; cmd.CommandType = CommandType.Text; if (parameters != null && parameters.Count() > 0) { cmd.Parameters.AddRange(parameters); } return cmd.ExecuteNonQuery(); } } } } protected object ExecuteSqlScalar<T>(string sql, params SqlParameter[] parameters) where T : class { using (var context = this.GetDbContext<T>()) { using (context.Database.Connection) { context.Database.Connection.Open(); var cmd = context.Database.Connection.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; if (parameters != null && parameters.Count() > 0) { cmd.Parameters.AddRange(parameters); } return cmd.ExecuteScalar(); } } } protected DataTable SQLQuery<T>(string sql) where T : class { using (var context = this.GetDbContext<T>()) { using (context.Database.Connection) { context.Database.Connection.Open(); var cmd = context.Database.Connection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; SqlDataAdapter sdap = new SqlDataAdapter(); sdap.SelectCommand = cmd as SqlCommand; DataTable dt = new DataTable(); sdap.Fill(dt); return dt; } } } protected DataTable SQLQuery<T>(string sql, params SqlParameter[] parameters) where T : class { using (var context = this.GetDbContext<T>()) { using (context.Database.Connection) { context.Database.Connection.Open(); var cmd = context.Database.Connection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; if (parameters != null && parameters.Count() > 0) { cmd.Parameters.AddRange(parameters); } SqlDataAdapter sdap = new SqlDataAdapter(); sdap.SelectCommand = cmd as SqlCommand; DataTable dt = new DataTable(); sdap.Fill(dt); return dt; } } } #endregion protected DataSet ManySQLQuery<T>(ArrayList arrayList) where T : class { using (var context = this.GetDbContext<T>()) { using (context.Database.Connection) { context.Database.Connection.Open(); var cmd = context.Database.Connection.CreateCommand(); SqlDataAdapter sdap = new SqlDataAdapter(); string table = "table"; DataSet set = new DataSet(); for (int i = 0; i < arrayList.Count; ++i) { cmd.CommandText = arrayList[i].ToString(); sdap.SelectCommand = (SqlCommand)cmd; sdap.Fill(set, table + i); } return set; } } } private string FormatSqlString(object obj) { if (obj.GetType() == typeof(System.Boolean)) { return ((bool)obj ? "1" : "0"); } if (obj.GetType() == typeof(System.Guid)) { return "'" + obj.ToString() + "'"; } if (obj.GetType() == typeof(System.Int16) || obj.GetType() == typeof(System.Int32) || obj.GetType() == typeof(System.Int64) || obj.GetType() == typeof(System.Decimal) || obj.GetType() == typeof(System.Double)) { return obj.ToString(); } return "'" + obj.ToString() + "'"; } } public struct OrderModelField { public bool IsDESC { get; set; } public string PropertyName { get; set; } }} 0 0
- EntityFramework6写的数据访问框架之二BaseDAL
- EntityFramework6写的数据访问框架之三DatabaseExtension
- EntityFramework6写的数据访问框架之四使用
- EntityFramework6写的数据访问框架之五事务
- EntityFramework6写的数据访问框架之一 BaseDb
- Java专家之路（二）---数据访问和数据持久化框架的总结
- Application应用框架思考(二) 之[数据访问层]
- 网络通信框架Volley之二——访问服务器获取Json.xml数据和详细的解析数据
- 菜鸟级三层框架（EF+MVC）项目实战之 系列二 对数据访问层的抽象上
- 菜鸟级三层框架（EF+MVC）项目实战之 系列二 对数据访问层的抽象中
- 菜鸟级三层框架（EF+MVC）项目实战之 系列二 对数据访问层的抽象下
- EntityFramework6中管理DbContext的正确方式(二)【DbContext的默认行为】
- Yii框架之数据访问对象 (DAO)
- 数据存储与访问二之SharedPrefrenced
- 自己写的数据访问层
- 自己写的一个数据访问类
- (二)PetShop数据访问层之数据库访问设计
- spring的数据访问框架的原理
- Oracle12c Release1安装图解（详解）
- Logistic Regression--逻辑回归算法汇总
- Android五大布局介绍&属性设置大全
- codevs2800送外卖(floyd+状压dp)
- App工程结构搭建：几种常见Android代码架构分析
- EntityFramework6写的数据访问框架之二BaseDAL
- java 序列化实现对象的深拷贝
- SQL-事务基础知识
- jq选择器-1
- could not initialize proxy - no Session
- EntityFramework6写的数据访问框架之三DatabaseExtension
- android自动弹出软键盘
- View如何使用对话框中的变量
- RMAN-06183: datafile or datafile copy +DATA_ERP/ap_media35 (file number 27) larger than MAXSETSIZE
