internal static void Add <T>(this DbConnection conn, T obj, int?commandTimeout = null) where T : BaseDBModel
{
if (obj.GetDBModel_ChangeList().Count == 0)
{
throw new LnskyDBException("没有修改列");
}
var sql = $"INSERT INTO {obj.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(obj)}{obj.GetDBModel_SqlProvider().GetProviderOption().CloseQuote}({string.Join(',', obj.GetDBModel_ChangeList())}) VALUES(@{string.Join(",@", obj.GetDBModel_ChangeList())});";
try
{
if (!string.IsNullOrEmpty(obj.GetDBModel_IncrementCol()))
{
sql += obj.GetDBModel_SqlProvider().GetSelectIncrement();
var v = conn.ExecuteScalar <int>(sql: sql, param: obj, commandTimeout: commandTimeout, transaction: DBTool.GetTransaction(conn));
obj.SetIncrementValue(v);
}
else
{
conn.Execute(sql: sql, param: obj, commandTimeout: commandTimeout, transaction: DBTool.GetTransaction(conn));
}
obj.GetDBModel_ChangeList().Clear();
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e));
throw;
}
}
internal static void Add <T>(this DbConnection conn, T obj, int?commandTimeout = null) where T : BaseDBModel
{
if (obj.GetDBModel_ChangeList().Count == 0)
{
throw new Exception("没有修改列");
}
var sql = $"INSERT INTO [{DBTool.GetTableName(obj)}]({string.Join(',', obj.GetDBModel_ChangeList())}) VALUES(@{string.Join(",@", obj.GetDBModel_ChangeList())});";
try
{
if (!string.IsNullOrEmpty(obj.GetDBModel_IncrementCol()))
{
sql += "select LAST_INSERT_ID() as Id";
var v = conn.ExecuteScalar <int>(sql: sql, param: obj, commandTimeout: commandTimeout);
obj.SetIncrementValue(v);
}
else
{
conn.Execute(sql: sql, param: obj, commandTimeout: commandTimeout);
}
obj.GetDBModel_ChangeList().Clear();
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e));
throw;
}
}
internal static List <T> GetAll <T>(this DbConnection conn, int?commandTimeout = null) where T : BaseDBModel, new()
{
T obj = new T();
var sql = $"SELECT * FROM {obj.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(obj)}{obj.GetDBModel_SqlProvider().GetProviderOption().CloseQuote} ";
try
{
var lst = conn.Query <T>(sql: sql, param: obj, commandTimeout: commandTimeout, transaction: DBTool.GetTransaction(conn)).AsList();
lst.ForEach(m => { m.GetDBModel_ChangeList().Clear(); m.BeginChange(); });
return(lst);
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e));
throw;
}
}
internal static List <T> GetAll <T>(this DbConnection conn, int?commandTimeout = null) where T : BaseDBModel, new()
{
T obj = new T();
var sql = $"SELECT * FROM [{DBTool.GetTableName(obj)}] ";
try
{
var lst = conn.Query <T>(sql: sql, param: obj, commandTimeout: commandTimeout).AsList();
lst.ForEach(m => m.GetDBModel_ChangeList().Clear());
return(lst);
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e));
throw;
}
}
internal static bool Delete <T>(this DbConnection conn, T obj, int?commandTimeout = null) where T : BaseDBModel
{
if (obj.GetDBModel_PKCols().Count == 0)
{
throw new LnskyDBException("没有主键");
}
var sql = $"DELETE FROM {obj.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(obj)}{obj.GetDBModel_SqlProvider().GetProviderOption().CloseQuote} WHERE {GetSql(obj, obj.GetDBModel_PKCols(), "AND")}";
try
{
return(conn.Execute(sql: sql, param: obj, commandTimeout: commandTimeout, transaction: DBTool.GetTransaction(conn)) == 1);
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e));
throw;
}
}
internal static bool Delete <T>(this DbConnection conn, T obj, int?commandTimeout = null) where T : BaseDBModel
{
if (obj.GetDBModel_PKCols().Count == 0)
{
throw new Exception("没有主键");
}
var sql = $"DELETE FROM [{DBTool.GetTableName(obj)}] WHERE {GetSql(obj.GetDBModel_PKCols(), "AND")}";
try
{
return(conn.Execute(sql: sql, param: obj, commandTimeout: commandTimeout) == 1);
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e));
throw;
}
}
internal static T Get <T>(this DbConnection conn, T obj, int?commandTimeout = null) where T : BaseDBModel
{
if (obj.GetDBModel_ChangeList().Count == 0)
{
throw new LnskyDBException("没有筛选条件");
}
var sql = $"SELECT Top 1 * FROM {obj.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(obj)}{obj.GetDBModel_SqlProvider().GetProviderOption().CloseQuote} {DBTool.GetTableWith(obj)} WHERE " + GetSql(obj, obj.GetDBModel_ChangeList(), "AND");
try
{
var res = conn.QueryFirstOrDefault <T>(sql: sql, param: obj, commandTimeout: commandTimeout, transaction: DBTool.GetTransaction(conn));
res?.GetDBModel_ChangeList().Clear();
return(res);
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e));
throw;
}
}
internal static T Get <T>(this DbConnection conn, T obj, int?commandTimeout = null) where T : BaseDBModel
{
if (obj.GetDBModel_ChangeList().Count == 0)
{
throw new Exception("没有筛选条件");
}
var sql = $"SELECT * FROM [{DBTool.GetTableName(obj)}] WHERE " + GetSql(obj.GetDBModel_ChangeList(), "AND");
try
{
var res = conn.QueryFirstOrDefault <T>(sql: sql, param: obj, commandTimeout: commandTimeout);
res?.GetDBModel_ChangeList().Clear();
return(res);
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e));
throw;
}
}
/// <summary>
/// 关闭当前请求的连接
/// </summary>
public static void CloseConnections()
{
var reqModel = GetRequestConnLst();
if (reqModel.ConnLst.Count > 0)
{
foreach (var conn in reqModel.ConnLst)
{
try
{
conn.Value.Close();
}
catch (Exception e)
{
DbError(e, ExceptionTool.ToString(e));
}
}
reqModel.ConnLst.Clear();
}
ThreadLnskyDBConnLst = null;
}
/// <summary>
/// 将异步返回的异常构建一个大异常抛出去,当然你也可以自己擦屁股
/// </summary>
/// <param name="results"></param>
public void BuildBigException(MultiTaskResult[] results)
{
var sb = new StringBuilder();
var errors = results.Where(a => a.exp != null).ToList();
if (errors.Any())
{
foreach (var error in errors)
{
if (error.exp is BusinessException)
{
sb.Append(error.exp.Message + ",");
}
else
{
sb.Append(ExceptionTool.GetSimpleErrorMsgByException(error.exp) + ",");
}
}
string sbString = sb.ToString();
throw new Exception(sbString.Substring(0, sbString.Length - 1));
}
}
internal static bool Update <T>(this DbConnection conn, T obj, int?commandTimeout = null) where T : BaseDBModel
{
if (obj.GetDBModel_ChangeList().Count == 0)
{
throw new LnskyDBException("没有修改列");
}
if (obj.GetDBModel_PKCols().Count == 0)
{
throw new LnskyDBException("没有主键");
}
var elst = obj.GetDBModel_PKCols().AddRange(obj.GetDBModel_ExcludeColsForUpdate());
var updateList = obj.GetDBModel_ChangeList().Where(m => m != obj.GetDBModel_IncrementCol() && !elst.Contains(m)).ToList();
var sql = $"UPDATE {obj.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(obj)}{obj.GetDBModel_SqlProvider().GetProviderOption().CloseQuote} SET {GetSql(obj, updateList, ",")} WHERE {GetSql(obj, obj.GetDBModel_PKCols(), "AND")}";
try
{
return(conn.Execute(sql: sql, param: obj, commandTimeout: commandTimeout, transaction: DBTool.GetTransaction(conn)) > 0);
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ExceptionTool.ToString(e));
throw;
}
}
internal static long Count <T>(this DbConnection conn, IQuery <T> query, int?commandTimeout = null) where T : BaseDBModel
{
var sql = new StringBuilder($"SELECT COUNT(1) FROM {query.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(query.DBModel)}{query.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote} {DBTool.GetTableWith(query)} WHERE 1=1 ");
DynamicParameters dynamicParameters = SetWhereSql(query, sql, true);
try
{
return(conn.QuerySingleOrDefault <long>(sql: sql.ToString(), param: dynamicParameters, commandTimeout: commandTimeout, transaction: DBTool.GetTransaction(conn)));
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + ToIQueryJSON(dynamicParameters, query) + ExceptionTool.ToString(e));
throw;
}
}
internal static int Delete <T>(this DbConnection conn, IQuery <T> where, int?commandTimeout = null) where T : BaseDBModel
{
var sql = new StringBuilder($"DELETE FROM {where.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(where.DBModel)}{where.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote} WHERE 1=1 ");
DynamicParameters dynamicParameters = SetWhereSql(where, sql);
try
{
return(conn.Execute(sql: sql.ToString(), param: dynamicParameters, commandTimeout: commandTimeout, transaction: DBTool.GetTransaction(conn)));
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + ToIQueryJSON(dynamicParameters, where) + ExceptionTool.ToString(e));
throw;
}
}
internal static T Get <T>(this DbConnection conn, IQuery <T> query, int?commandTimeout = null) where T : BaseDBModel
{
var sql = new StringBuilder($"SELECT Top 1 * FROM {query.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(query.DBModel)}{query.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote} {DBTool.GetTableWith(query)} WHERE 1=1 ");
DynamicParameters dynamicParameters = SetWhereSql(query, sql);
try
{
var lst = conn.Query <T>(sql: sql.ToString(), param: dynamicParameters, commandTimeout: commandTimeout, transaction: DBTool.GetTransaction(conn)).AsList();
lst.ForEach(m => { m.GetDBModel_ChangeList().Clear(); m.BeginChange(); });
return(lst.FirstOrDefault());
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + ToIQueryJSON(dynamicParameters, query) + ExceptionTool.ToString(e));
throw;
}
}
internal static List <R> GetList <R, T>(this DbConnection conn, IQuery <T> query, int?commandTimeout = null) where T : BaseDBModel
{
var sql = new StringBuilder($"SELECT * FROM [{DBTool.GetTableName(query.DBModel)}] WHERE 1=1 ");
DynamicParameters dynamicParameters = SetWhereSql(query, sql);
try
{
var lst = conn.Query <R>(sql: sql.ToString(), param: dynamicParameters, commandTimeout: commandTimeout).AsList();
return(lst);
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + ToIQueryJSON(dynamicParameters, query) + ExceptionTool.ToString(e));
throw;
}
}
internal static IEnumerable <R> GetList <R, T>(this DbConnection conn, IQuery <T> query, int?commandTimeout = null) where T : BaseDBModel
{
var sql = new StringBuilder($"SELECT * FROM {query.DBModel.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(query.DBModel)}{query.DBModel.GetDBModel_SqlProvider().GetProviderOption().CloseQuote} {DBTool.GetTableWith(query)} WHERE 1=1 ");
DynamicParameters dynamicParameters = SetWhereSql(query, sql);
try
{
var lst = conn.Query <R>(sql: sql.ToString(), param: dynamicParameters, commandTimeout: commandTimeout, transaction: DBTool.GetTransaction(conn));
return(lst);
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + ToIQueryJSON(dynamicParameters, query) + ExceptionTool.ToString(e));
throw;
}
}
internal static int Update <T>(this DbConnection conn, T obj, IQuery <T> where, int?commandTimeout = null) where T : BaseDBModel
{
if (obj.GetDBModel_ChangeList().Count == 0)
{
throw new LnskyDBException("没有修改列");
}
var elst = obj.GetDBModel_PKCols().AddRange(obj.GetDBModel_ExcludeColsForUpdate());
var updateList = obj.GetDBModel_ChangeList().Where(m => m != obj.GetDBModel_IncrementCol() && !elst.Contains(m)).ToList();
var sql = new StringBuilder($"UPDATE {obj.GetDBModel_SqlProvider().GetProviderOption().OpenQuote}{DBTool.GetTableName(obj)}{obj.GetDBModel_SqlProvider().GetProviderOption().CloseQuote} SET {GetSql(obj, updateList, ",")} WHERE 1=1");
DynamicParameters dynamicParameters = SetWhereSql(where, sql);
dynamicParameters.AddDynamicParams(obj);
try
{
return(conn.Execute(sql: sql.ToString(), param: dynamicParameters, commandTimeout: commandTimeout, transaction: DBTool.GetTransaction(conn)));
}
catch (Exception e)
{
DBTool.DbError(e, sql.ToString() + JsonHelper.ToJSON(obj) + ToIQueryJSON(dynamicParameters, where) + ExceptionTool.ToString(e));
throw;
}
}
/// <summary>
/// 是当前DBContext 提交到数据库中
/// </summary>
/// <exception cref="BusinessException">数据已经被更改，请重新加载操作</exception>
/// <exception cref="BusinessException">DbUpdateException 提取 InnerException 抛出，开发更容易看懂</exception>
/// <exception cref="BusinessException">DbEntityValidationException 提取EntityValidationErrors 抛出，开发更容易看懂</exception>
public void Commit()
{
try
{
var isDbTran = (_dbContext.Database.CurrentTransaction != null) ||
_dbContext.Database.IsInMemory(); // 内存数据库不支持Transaction 只能直接提交
if (isDbTran)
{
//var audit = new Z.EntityFramework.Plus.Audit();
_dbContext.SaveChanges();
//OnDbCommit?.Invoke(_dbContext, audit);
}
else
{
//手工增加事物,是为了不使用默认的事物隔离级别 RepeateableRead , 强制使用 ReadCommitted .
using (var tran = _dbContext.Database.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
try
{
//var audit = new Z.EntityFramework.Plus.Audit();
_dbContext.SaveChanges();
//OnDbCommit?.Invoke(_dbContext, audit);
tran.Commit();
}
catch (Exception ex)
{
try
{
tran.Rollback();
}
catch
{
// 防止回滚的异常将提交异常冲掉
}
throw ex;
}
}
}
}
catch (DbUpdateConcurrencyException ex)
{
StringBuilder builder = new StringBuilder();
builder.Append("系统并发日志：");
if (ex.Entries != null)
{
foreach (var entrie in ex.Entries)
{
builder.Append("{");
builder.Append("Type:" + entrie.Entity.GetType().Name + ",");
builder.Append("EntityState:" + entrie.State + ",");
builder.Append("Propertys:{");
var currentValues = entrie.CurrentValues;
foreach (var name in currentValues.Properties)
{
if (currentValues[name] != null)
{
string value = currentValues[name].ToString();
builder.AppendFormat("{0}:{1},", name.Name, value);
}
}
builder.Append("}}");
}
}
var newEx = new Exception(builder.ToString().Replace("}{", "},{").Replace(",}", "}"), ex);
//并发处理控制，捕获并发异常信息，以业务异常抛出
throw new BusinessException("数据已经被更改，请重新加载操作", newEx);
}
catch (Exception e)
{
var errorMsg = "数据提交出错。";
if (e is ValidationException || e is DbUpdateException)
{
//获取根部异常信息
//抛出内部异常，开发更容易看懂
var newEx = new Exception();
if (e.InnerException != null)
{
newEx = e.InnerException;
}
while (newEx.InnerException != null)
{
newEx = newEx.InnerException;
}
if (newEx != null)
{
errorMsg = ExceptionTool.GetEasyErrorMessage(newEx.Message);
}
}
throw new BusinessException(errorMsg, e);
}
}