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 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 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 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 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 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 <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;
}
}
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 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;
}
}