/// <summary> 执行事务https://msdn.microsoft.com/en-us/data/dn456843
/// 执行事务
/// </summary>
/// <param name="lsSql"></param>
/// <param name="lsParas"></param>
public void ExecuteTransaction(List <String> lsSql, List <Object[]> lsParas)
{
using (SysDb db = new SysDb(strConn))
{
using (var tran = db.Database.BeginTransaction())
{
try
{
for (int i = 0; i < lsSql.Count; i++)
{
if (lsParas != null && lsParas.Count > 0)
{
db.Database.ExecuteSqlCommand(lsSql[i], lsParas[i]);
}
}
foreach (String item in lsSql)
{
db.Database.ExecuteSqlCommand(item);
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
}
}
/// <summary> 查询列表
/// 查询列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public List <T> QueryList <T>(string sql, params object[] paras) where T : class
{
using (SysDb db = new SysDb(strConn))
{
return(db.Database.SqlQuery <T>(sql, paras).ToList());
}
}
/// <summary>查询单个
/// 查询单个
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public T QuerySingle <T>(string sql, params object[] paras) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
return(db.Database.SqlQuery <T>(sql, paras).FirstOrDefault());
}
}
/// <summary>查找单个
/// 查找单个
/// </summary>
/// <param name="seleWhere">查询条件</param>
/// <returns></returns>
public T GetSingle <T>(Expression <Func <T, bool> > seleWhere) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
return(db.Set <T>().AsExpandable().FirstOrDefault(seleWhere));
}
}
/// <summary>执行操作
/// 执行操作
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
public void ExecuteSql(string sql, params object[] paras)
{
using (SysDb db = new SysDb(strConn))
{
db.Database.ExecuteSqlCommand(sql, paras);
}
}
/// <summary>根据条件查询实体集合(单个字段排序)
/// 根据条件查询实体集合(单个字段排序)
/// </summary>
/// <param name="seleWhere">查询条件 lambel表达式</param>
/// <returns></returns>
public List <T> GetList <T, Tkey>(Expression <Func <T, bool> > seleWhere, Expression <Func <T, Tkey> > orderWhere, bool isDesc) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
return(CommonSort(db.Set <T>().AsExpandable().Where(seleWhere), orderWhere, isDesc).ToList());
}
}
/// <summary>根据条件查询实体集合(多个字段排序)
/// 根据条件查询实体集合(多个字段排序)
/// </summary>
/// <param name="seleWhere">查询条件 lambel表达式</param>
/// <returns></returns>
public List <T> GetList <T>(Expression <Func <T, bool> > seleWhere, params OrderModelField[] orderByExpression) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
return(CommonSort(db.Set <T>().AsExpandable().Where(seleWhere), orderByExpression).ToList());
}
}
/// <summary>/ 根据条件查询实体集合
/// 根据条件查询实体集合
/// </summary>
/// <param name="seleWhere">查询条件 lambel表达式</param>
/// <returns></returns>
public List <T> GetList <T>(Expression <Func <T, bool> > seleWhere) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
return(db.Set <T>().AsExpandable().Where(seleWhere).ToList());
}
}
/// <summary>根据条件查询实体集合
/// 根据条件查询实体集合
/// </summary>
/// <param name="seleWhere">查询条件 lambel表达式</param>
/// <returns></returns>
public List <T> GetList <T, TValue>(Expression <Func <T, TValue> > seleWhere, IEnumerable <TValue> conditions) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
return(db.Set <T>().AsExpandable().WhereIn <T, TValue>(seleWhere, conditions).ToList());
}
}
/// <summary>获取所有实体集合(多个排序)AsNoTracking
/// 获取所有实体集合(多个排序)AsNoTracking
/// </summary>
/// <returns></returns>
public List <T> GetAllAsNoTracking <T>(params OrderModelField[] orderByExpression) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
return(CommonSort(db.Set <T>().AsExpandable().AsNoTracking(), orderByExpression).ToList());
}
}
/// <summary> 获取所有实体集合(单个排序)AsNoTracking
/// 获取所有实体集合(单个排序)AsNoTracking
/// </summary>
/// <returns></returns>
public List <T> GetAllAsNoTracking <T, Tkey>(Expression <Func <T, Tkey> > orderWhere, bool isDesc) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
return(CommonSort(db.Set <T>().AsExpandable().AsNoTracking(), orderWhere, isDesc).ToList <T>());
}
}
/// <summary>获取所有实体集合AsNoTracking
/// 获取所有实体集合AsNoTracking
/// </summary>
/// <returns></returns>
public List <T> GetAllAsNoTracking <T>() where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
return(db.Set <T>().AsExpandable().AsNoTracking().ToList <T>());
}
}
/// <summary>批量插入
/// 批量插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ts"></param>
public void AddEx <T>(IEnumerable <T> ts) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
db.BulkInsert(ts);
}
}
/// <summary> 查找单个
/// 查找单个
/// </summary>
/// <param name="id">主键</param>
/// <returns></returns>
public T GetSingleById <T>(int id) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
return(db.Set <T>().Find(id));
}
}
/// <summary>新增
/// 新增
/// </summary>
/// <param name="entity">实体</param>
/// <returns>返回受影响行数</returns>
public bool Add <T>(T entity) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
db.Entry <T>(entity).State = EntityState.Added;
return(db.SaveChanges() > 0);
}
}
/// <summary>获取分页集合(无条件多字段排序)
/// 获取分页集合(无条件多字段排序)
/// </summary>
/// <returns></returns>
public List <T> GetListPaged <T>(int pageIndex, int pageSize, out int totalcount, params OrderModelField[] orderByExpression) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
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>
public List <T> GetListPaged <T, Tkey>(int pageIndex, int pageSize, Expression <Func <T, bool> > seleWhere, out int totalcount) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
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>
/// <param name="deleWhere">删除条件</param>
/// <returns>返回受影响行数</returns>
public bool DeleteByConditon <T>(Expression <Func <T, bool> > deleWhere) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
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="entity">实体</param>
/// <returns>返回受影响行数</returns>
public bool Delete <T>(T entity) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
db.Set <T>().Attach(entity);
db.Entry <T>(entity).State = EntityState.Deleted;
return(db.SaveChanges() > 0);
}
}
/// <summary>批量插入
/// 批量插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ts"></param>
public void AddBulkEx <T>(IEnumerable <T> ts) where T : class
{
var options = new BulkInsertOptions
{
EnableStreaming = true,
};
using (SysDb <T> db = new SysDb <T>(strConn))
{
db.BulkInsert(ts);
}
}
/// <summary>批量删除
/// 批量删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Iqueryable"></param>
/// <returns></returns>
public int DeleteEx <T>(Expression <Func <T, bool> > expression) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
IQueryable <T> Iqueryable = db.Set <T>().Where(expression);
int intResult = Iqueryable.Delete();
return(intResult);
//或者
// db.tb.Delete(u => u.name == "101");
//不需要db.SaveChanges();
}
}
/// <summary>批量更新
/// 批量更新
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Iqueryable"></param>
/// <param name="updateExpression"></param>
/// <returns></returns>
public int UpdateEx <T>(Expression <Func <T, bool> > expression, Expression <Func <T, T> > updateExpression) where T : class
{
using (SysDb <T> db = new SysDb <T>(strConn))
{
IQueryable <T> Iqueryable = db.Set <T>().Where(expression);
int intResult = Iqueryable.Update(updateExpression);
return(intResult);
//db.tb.Update(
// u => u.name.Contains("2"),
// u2 => new tb { password = "******" });
//或者 db.tb.Where(u => u.name == "2").Update(u => new tb { password = "******" });
//不需要db.SaveChanges();
}
}
/// <summary>
/// 添加日志
/// </summary>
/// <param name="info"></param>
public void AddLogger(string info)
{
if (info != "\r\n" && (!info.Contains("Sys_EventLog")))
{
string strTemp = info.ToUpper().Trim();
if (isNew)
{
//记录增删改
if (strTemp.StartsWith("INSERT") || strTemp.StartsWith("UPDATE") || strTemp.StartsWith("DELETE"))
{
strMsg = info;
isNew = false;
}
}
else
{
if (strTemp.StartsWith("CLOSED CONNECTION"))
{
//增加新日志
using (SysDb db = new SysDb(strConn))
{
try
{
//保存日志到数据库或其他地方
}
catch (Exception ex)
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(AppDomain.CurrentDomain.BaseDirectory + "//logError.txt"))
{
sw.Write(ex.Message);
sw.Flush();
}
}
}
//清空
strMsg = "";
isNew = true;
}
else
{
strMsg += info;
}
}
}
}
