加手势

style=" cursor:pointer;"

/// <summary>
/// mssql数据库 数据层 父类
/// </summary>
/// <typeparam name="T"></typeparam>
public class BaseDAL<T> where T : class,new()
{
/// <summary>
/// EF上下文对象
/// </summary>

public DbContext _context;
public BaseDAL()
{
if (_context == null)
_context = new DBContextFactory().GetDbContext();
}

private DbSet<T> _entities;

public DbSet<T> Entities
{
get
{
if (_entities == null)
_entities = _context.Set<T>();
return _entities;
}

}
public virtual IQueryable<T> Table
{
get
{
return this.Entities;
}
}

#region 新增 实体 +int AddNew(T model)
/// <summary>
/// 新增 实体
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public virtual void Insert(T model)
{
if (model == null)
throw new ArgumentNullException("model");
try
{

_context.Set<T>().Add(model);
_context.SaveChanges();

}
catch (DbEntityValidationException dbEx)
{
var msg = string.Empty;
foreach (var validationErrors in dbEx.EntityValidationErrors)
foreach (var validationError in validationErrors.ValidationErrors)
msg += string.Format("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage) + Environment.NewLine;

var fail = new Exception(msg, dbEx);
throw fail;
}
}

public virtual void Insert(List<T> model)
{
if (model == null)
throw new ArgumentNullException("model");
try
{
_context.Set<T>().AddRange(model);
_context.SaveChanges();

}
catch (DbEntityValidationException dbEx)
{
var msg = string.Empty;
foreach (var validationErrors in dbEx.EntityValidationErrors)
foreach (var validationError in validationErrors.ValidationErrors)
msg += string.Format("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage) + Environment.NewLine;

var fail = new Exception(msg, dbEx);
throw fail;
}
}

#endregion

#region 1.0 新增 实体 +int Add(T model)
/// <summary>
/// 新增 实体
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public virtual int Insert2(T entity)
{
try
{
this.Entities.Add(entity);
return _context.SaveChanges();//保存成功后，会将自增的id设置给 model的 主键属性，并返回受影响行数
}
catch (DbEntityValidationException dbEx)
{
var msg = string.Empty;

foreach (var validationErrors in dbEx.EntityValidationErrors)
foreach (var validationError in validationErrors.ValidationErrors)
msg += string.Format("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage) + Environment.NewLine;

var fail = new Exception(msg, dbEx);
throw fail;
}
}
/// <summary>
/// 新增 实体
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public virtual T InsertBack(T entity)
{
try
{
this.Entities.Add(entity);
_context.SaveChanges();//保存成功后，会将自增的id设置给 model的 主键属性，并返回受影响行数
return entity;
}
catch (DbEntityValidationException dbEx)
{
var msg = string.Empty;

foreach (var validationErrors in dbEx.EntityValidationErrors)
foreach (var validationError in validationErrors.ValidationErrors)
msg += string.Format("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage) + Environment.NewLine;

var fail = new Exception(msg, dbEx);
throw fail;
}
}
#endregion

#region 2.0 根据 id 删除 +int Del(T model)
/// <summary>
/// 根据 id 删除
/// </summary>
/// <param name="model">包含要删除id的对象</param>
/// <returns></returns>
public virtual int Delete(T model)
{
_context.Set<T>().Attach(model);
_context.Set<T>().Remove(model);
return _context.SaveChanges();
}
#endregion

#region 3.0 根据条件删除 +int DelBy(Expression<Func<T, bool>> delWhere)
/// <summary>
/// 3.0 根据条件删除
/// </summary>
/// <param name="delWhere"></param>
/// <returns></returns>
public virtual int Delete(Expression<Func<T, bool>> delWhere)
{
//3.1查询要删除的数据
List<T> listDeleting = _context.Set<T>().Where(delWhere).ToList();
//3.2将要删除的数据 用删除方法添加到 EF 容器中
listDeleting.ForEach(u =>
{
_context.Set<T>().Attach(u);//先附加到 EF容器
_context.Set<T>().Remove(u);//标识为 删除 状态
});
//3.3一次性 生成sql语句到数据库执行删除
return _context.SaveChanges();
}
#endregion

#region 4.0.0 修改 +int Update(T entity)
/// <summary>
///修改
/// </summary>
/// <param name="entity">Entity</param>
public virtual int Update(T entity)
{
try
{
if (entity == null)
throw new ArgumentNullException("entity");

if (!(this._context.Entry<T>(entity).State == EntityState.Modified))
this._context.Entry<T>(entity).State = EntityState.Modified;
return _context.SaveChanges();
}
catch (DbEntityValidationException dbEx)
{
var msg = string.Empty;

foreach (var validationErrors in dbEx.EntityValidationErrors)
foreach (var validationError in validationErrors.ValidationErrors)
msg += Environment.NewLine + string.Format("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);

var fail = new Exception(msg, dbEx);
throw fail;
}
}
#endregion

#region 4.0.1 批量修改 +int Modify(T model, Expression<Func<T, bool>> whereLambda, params string[] modifiedProNames)
/// <summary>
/// 4.0 批量修改
/// </summary>
/// <param name="model">要修改的实体对象</param>
/// <param name="whereLambda">查询条件</param>
/// <param name="proNames">要修改的 属性 名称</param>
/// <returns></returns>
public virtual int Update(T model, Expression<Func<T, bool>> whereLambda, params string[] modifiedProNames)
{
//4.1查询要修改的数据
List<T> listModifing = _context.Set<T>().Where(whereLambda).ToList();

//获取 实体类 类型对象
Type t = typeof(T); // model.GetType();
//获取 实体类 所有的 公有属性
List<PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
//创建 实体属性 字典集合
Dictionary<string, PropertyInfo> dictPros = new Dictionary<string, PropertyInfo>();
//将 实体属性 中要修改的属性名 添加到 字典集合中 键：属性名 值：属性对象
proInfos.ForEach(p =>
{
if (modifiedProNames.Contains(p.Name))
{
dictPros.Add(p.Name, p);
}
});

//4.3循环 要修改的属性名
foreach (string proName in modifiedProNames)
{
//判断 要修改的属性名是否在 实体类的属性集合中存在
if (dictPros.ContainsKey(proName))
{
//如果存在，则取出要修改的 属性对象
PropertyInfo proInfo = dictPros[proName];
//取出 要修改的值
object newValue = proInfo.GetValue(model, null); //object newValue = model.uName;

//4.4批量设置 要修改 对象的 属性
foreach (T usrO in listModifing)
{
//为 要修改的对象 的 要修改的属性 设置新的值
proInfo.SetValue(usrO, newValue, null); //usrO.uName = newValue;
}
}
}
//4.4一次性 生成sql语句到数据库执行
return _context.SaveChanges();
}
#endregion

#region 5.0 根据条件查询
public virtual T GetBy(long sysNo)
{
return this.Entities.Find(sysNo);
}

/// <summary>
/// 5.0 根据条件查询 +List<T> GetListBy(Expression<Func<T,bool>> whereLambda)
/// </summary>
/// <param name="whereLambda"></param>
/// <returns></returns>
public virtual List<T> GetListBy(Expression<Func<T, bool>> whereLambda)
{
return _context.Set<T>().Where(whereLambda).ToList();
}
#endregion

#region 5.1 根据条件 排序 和查询 + List<T> GetListBy<TKey>
/// <summary>
/// 5.1 根据条件 排序 和查询
/// </summary>
/// <typeparam name="TKey">排序字段类型</typeparam>
/// <param name="whereLambda">查询条件 lambda表达式</param>
/// <param name="orderLambda">排序条件 lambda表达式</param>
/// <returns></returns>
public virtual List<T> GetListBy<TKey>(Expression<Func<T, bool>> whereLambda, Expression<Func<T, TKey>> orderLambda)
{
return _context.Set<T>().Where(whereLambda).OrderBy(orderLambda).ToList();
}
#endregion

#region 6.0 分页查询 + List<T> GetPagedList<TKey>
/// <summary>
/// 6.0 分页查询 + List<T> GetPagedList<TKey>
/// </summary>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页容量</param>
/// <param name="whereLambda">条件 lambda表达式</param>
/// <param name="orderBy">排序 lambda表达式</param>
/// <returns></returns>
public virtual List<T> GetPagedList<TKey>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, Expression<Func<T, TKey>> orderBy)
{
// 分页 一定注意： Skip 之前一定要 OrderBy
return _context.Set<T>().Where(whereLambda).OrderBy(orderBy).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
}
#endregion

#region 6.1分页查询 带输出 +List<T> GetPagedList<TKey>
/// <summary>
/// 6.1分页查询 带输出
/// </summary>
/// <typeparam name="TKey"></typeparam>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="rowCount"></param>
/// <param name="whereLambda"></param>
/// <param name="orderBy"></param>
/// <param name="isAsc"></param>
/// <returns></returns>
public virtual List<T> GetPagedList<TKey>(int pageIndex, int pageSize, ref int rowCount, Expression<Func<T, bool>> whereLambda, Expression<Func<T, TKey>> orderBy, bool isAsc = true)
{
rowCount = _context.Set<T>().Where(whereLambda).Count();
rowCount = rowCount % pageSize == 0 ? rowCount / pageSize : rowCount / pageSize + 1;

//1.查询分页数据
if (isAsc)
{
return _context.Set<T>().OrderBy(orderBy).Where(whereLambda).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
}
//2.查询总行数
else
{
return _context.Set<T>().OrderByDescending(orderBy).Where(whereLambda).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
}
}
#endregion

#region 7.0 执行sql语句 +int ExcuteSql(string strSql, params object[] paras)
/// <summary>
/// 7.0 执行sql语句 +int ExcuteSql(string strSql, params object[] paras)
/// </summary>
/// <param name="strSql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public virtual int ExcuteSql(string strSql, params object[] paras)
{
return _context.Database.ExecuteSqlCommand(strSql, paras);
}
#endregion

#region 8.0 根据条件获取一个 不被ef跟踪的 对象 +T GetModelWithOutTrace(Expression<Func<T, bool>> whereLambda)
/// <summary>
/// 8.0 根据条件获取一个 不被ef跟踪的 对象
/// </summary>
/// <param name="whereLambda"></param>
/// <returns></returns>
public T GetModelWithOutTrace(Expression<Func<T, bool>> whereLambda)
{
return _context.Set<T>().AsNoTracking().Single(whereLambda);
}
#endregion

#region 9.0 返回DataTable

/// <summary>
/// 执行SQL 语句并返回DataTable
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="sqlType"></param>
/// <returns></returns>
public DataTable ExceuteScalar(string sql, System.Data.CommandType sqlType, params SqlParameter[] param)
{
SqlConnection conn = null;
DataTable table = new DataTable();
try
{
//1. 新建数据库连通道
conn = new SqlConnection();
conn.ConnectionString = _context.Database.Connection.ConnectionString;

//2.新建查询命令
SqlCommand command = conn.CreateCommand();
command.CommandType = sqlType;
command.CommandText = sql;
command.Parameters.AddRange(param);
if (conn.State != ConnectionState.Open)
conn.Open();

//3.数据读取
var dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(table);
}
catch (Exception ex)
{
//记录日志
LogHelper.LogInfo("执行失败" + ex.Message.ToString());
return null;
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();
}

return table;
}
/// <summary>
/// 返回第一行的第一列
/// </summary>
/// <param name="connectionString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, CommandType sqlType, params SqlParameter[] param)
{

SqlConnection connection = new SqlConnection();
connection.ConnectionString = _context.Database.Connection.ConnectionString;
try
{
SqlCommand command = connection.CreateCommand();
command.CommandType = sqlType;
command.CommandText = sql;
command.Parameters.AddRange(param);
if (connection.State != ConnectionState.Open)
connection.Open();
object val = command.ExecuteScalar();
command.Parameters.Clear();
return val;
}
catch (Exception ex)
{
//记录日志
LogHelper.LogInfo("执行失败" + ex.Message.ToString());
return null;
}
finally
{
if (connection != null && connection.State == ConnectionState.Open)
connection.Close();
}

}
/// <summary>
/// 执行SQL 无返回
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlType"></param>
/// <param name="param"></param>
/// <returns></returns>
public void ExceuteNonQuery(string sql, System.Data.CommandType sqlType, params SqlParameter[] param)
{
SqlConnection conn = null;
try
{
//1. 新建数据库连通道
conn = new SqlConnection();
conn.ConnectionString = _context.Database.Connection.ConnectionString;

//2.新建查询命令
SqlCommand command = conn.CreateCommand();
command.CommandType = sqlType;
command.CommandText = sql;
command.Parameters.AddRange(param);
if (conn.State != ConnectionState.Open)
conn.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
//记录日志
LogHelper.LogInfo("执行失败" + ex.Message.ToString());

}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();
}

}

#endregion

/// <summary>
/// 执行SQL 语句并返回DataTable
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="sqlType"></param>
/// <returns></returns>
public DataSet ExceuteDataSet(string sql, System.Data.CommandType sqlType, params SqlParameter[] param)
{
SqlConnection conn = null;
DataSet ds = new DataSet();

try
{
//1. 新建数据库连通道
conn = new SqlConnection();
conn.ConnectionString = _context.Database.Connection.ConnectionString;

//2.新建查询命令
SqlCommand command = conn.CreateCommand();
command.CommandType = sqlType;
command.CommandText = sql;
command.Parameters.AddRange(param);
if (conn.State != ConnectionState.Open)
conn.Open();

//3.数据读取
var dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(ds);
command.Parameters.Clear();
}
catch (Exception ex)
{
//记录日志
LogHelper.LogInfo("执行失败" + ex.Message.ToString());
return null;
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();
}

return ds;
}

#region 10.0 事务执行
public int ExecTran(string sqlstr, params SqlParameter[] parameters)
{
var conn = new SqlConnection(_context.Database.Connection.ConnectionString);
try
{
conn.Open();
var tran = conn.BeginTransaction();
var cmd = new SqlCommand
{
Connection = conn,
Transaction = tran
};
try
{
cmd.CommandText = sqlstr;
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
tran.Commit();
return 1;
}
catch(Exception ex)
{
tran.Rollback();
return 0;
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();
tran.Dispose();
}
}
catch
{
return 0;
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();
}
}
#endregion

#region 11.0 执行sql语句返回结合
public List<T> GetBySql(string sql, params SqlParameter[] parameters)
{
return _context.Database.SqlQuery<T>(sql, parameters).ToList();
}
#endregion
}