private string GetSelectClauseStatement <T>()
{
DatabaseEntityDef modelDef = _entityDefFactory.GetDef <T>();
string cacheKey = string.Format(GlobalSettings.Culture, "{0}_{1}_SELECT", modelDef.DatabaseName, modelDef.TableName);
if (_sqlStatementDict.ContainsKey(cacheKey))
{
return(_sqlStatementDict[cacheKey]);
}
StringBuilder argsBuilder = new StringBuilder();
foreach (DatabaseEntityPropertyDef info in modelDef.Properties)
{
if (info.IsTableProperty)
{
argsBuilder.AppendFormat(GlobalSettings.Culture, "{0}.{1},", modelDef.DbTableReservedName, info.DbReservedName);
//argsBuilder.AppendFormat("{0},", info.DbReservedName);
}
}
if (argsBuilder.Length > 0)
{
argsBuilder.Remove(argsBuilder.Length - 1, 1);
}
string selectClause = string.Format(GlobalSettings.Culture, "SELECT {0} ", argsBuilder.ToString());
_sqlStatementDict.TryAdd(cacheKey, selectClause);
return(selectClause);
}
public long Count <T>(SelectExpression <T> selectCondition, FromExpression <T> fromCondition, WhereExpression <T> whereCondition, TransactionContext transContext)
where T : DatabaseEntity, new()
{
#region Argument Adjusting
if (selectCondition != null)
{
selectCondition.Select(t => t.Id).Select(t => t.Deleted).Select(t => t.LastTime).Select(t => t.LastUser).Select(t => t.Version);
}
if (whereCondition == null)
{
whereCondition = Where <T>();
}
whereCondition.And(t => t.Deleted == false);
#endregion
long count = -1;
DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>();
try
{
IDbCommand command = _sqlBuilder.CreateCountCommand(fromCondition, whereCondition);
object countObj = _databaseEngine.ExecuteCommandScalar(transContext?.Transaction, entityDef.DatabaseName, command, transContext != null);
count = Convert.ToInt32(countObj, GlobalSettings.Culture);
}
catch (DbException ex)
{
throw ex;// _logger.LogCritical(ex.Message);
}
return(count);
}
public IList <Tuple <TSource, TTarget1, TTarget2> > Retrieve <TSource, TTarget1, TTarget2>(FromExpression <TSource> fromCondition, WhereExpression <TSource> whereCondition, TransactionContext transContext)
where TSource : DatabaseEntity, new()
where TTarget1 : DatabaseEntity, new()
where TTarget2 : DatabaseEntity, new()
{
if (whereCondition == null)
{
whereCondition = Where <TSource>();
}
switch (fromCondition.JoinType)
{
case SqlJoinType.LEFT:
whereCondition.And(t => t.Deleted == false);
break;
case SqlJoinType.RIGHT:
whereCondition.And <TTarget2>(t => t.Deleted == false);
break;
case SqlJoinType.INNER:
whereCondition.And(t => t.Deleted == false).And <TTarget1>(t => t.Deleted == false).And <TTarget2>(t => t.Deleted == false);
break;
case SqlJoinType.FULL:
break;
case SqlJoinType.CROSS:
whereCondition.And(t => t.Deleted == false).And <TTarget1>(t => t.Deleted == false).And <TTarget2>(t => t.Deleted == false);
break;
}
IList <Tuple <TSource, TTarget1, TTarget2> > result = null;
IDataReader reader = null;
DatabaseEntityDef entityDef = _entityDefFactory.GetDef <TSource>();
try
{
IDbCommand command = _sqlBuilder.CreateRetrieveCommand <TSource, TTarget1, TTarget2>(fromCondition, whereCondition);
reader = _databaseEngine.ExecuteCommandReader(transContext?.Transaction, entityDef.DatabaseName, command, transContext != null);
result = _modelMapper.ToList <TSource, TTarget1, TTarget2>(reader);
}
//catch (DbException ex)
//{
// result = new List<Tuple<TSource, TTarget1, TTarget2>>();
// _logger.LogCritical(ex.Message);
//}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
return(result);
}
public static string CreateUpdateTemplate(DatabaseEntityDef modelDef)
{
StringBuilder args = new StringBuilder();
foreach (DatabaseEntityPropertyDef info in modelDef.Properties)
{
if (info.IsTableProperty)
{
if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime" || info.PropertyName == "Deleted")
{
continue;
}
args.AppendFormat(GlobalSettings.Culture, " {0}={1},", info.DbReservedName, info.DbParameterizedName);
}
}
if (args.Length > 0)
{
args.Remove(args.Length - 1, 1);
}
string statement = string.Format(GlobalSettings.Culture, "UPDATE {0} SET {1}", modelDef.DbTableReservedName, args.ToString());
return(statement);
}
public static string CreateDeleteTemplate(DatabaseEntityDef modelDef)
{
DatabaseEntityPropertyDef deletedProperty = modelDef.GetProperty("Deleted") !;
DatabaseEntityPropertyDef lastUserProperty = modelDef.GetProperty("LastUser") !;
StringBuilder args = new StringBuilder();
args.Append($"{deletedProperty.DbReservedName}=1,");
args.Append($"{lastUserProperty.DbReservedName}={lastUserProperty.DbParameterizedName}");
return($"UPDATE {modelDef.DbTableReservedName} SET {args} ");
}
private FromExpression <T> InternalJoin <Target>(string joinType, Expression joinExpr)
{
DatabaseEntityDef targetDef = entityDefFactory.GetDef(typeof(Target));
_statementBuilder.Append(" ");
_statementBuilder.Append(joinType);
_statementBuilder.Append(" ");
_statementBuilder.Append(targetDef.DbTableReservedName);
_statementBuilder.Append(" ON ");
_statementBuilder.Append(joinExpr.ToStatement(expressionContext));
_statementBuilder.Append(" ");
return(this);
}
private string SQLite_Table_Create_Statement(Type type, bool addDropStatement)
{
StringBuilder sql = new StringBuilder();
DatabaseEntityDef definition = _entityDefFactory.GetDef(type);
if (definition.DbTableReservedName.IsNullOrEmpty())
{
throw new DatabaseException($"Type : {definition.EntityFullName} has null or empty DbTableReservedName");
}
foreach (DatabaseEntityPropertyDef info in definition.Properties)
{
if (!info.IsTableProperty)
{
continue;
}
if (info.PropertyName.IsIn("Id", "Deleted", "LastUser", "LastTime", "Version"))
{
continue;
}
string dbTypeStatement = info.TypeConverter == null
? _databaseEngine.GetDbTypeStatement(info.PropertyType)
: info.TypeConverter.TypeToDbTypeStatement(info.PropertyType);
string nullable = info.IsNullable ? "" : " NOT NULL ";
string defaultValue = info.DbDefaultValue.IsNullOrEmpty() ? "" : " DEFAULT " + info.DbDefaultValue;
string unique = info.IsUnique ? " UNIQUE " : "";
sql.AppendLine($" {info.DbReservedName} {dbTypeStatement} {nullable} {unique} {defaultValue} ,");
}
string dropStatement = addDropStatement ? $"Drop table if exists {definition.DbTableReservedName};" : string.Empty;
return
($@"{dropStatement}
CREATE TABLE {definition.DbTableReservedName} (
""Id"" INTEGER PRIMARY KEY AUTOINCREMENT,
{sql}
""Deleted"" NUMERIC NOT NULL DEFAULT 0,
""LastUser"" TEXT,
""LastTime"" NUMERIC,
""Version"" INTEGER NOT NULL
);");
}
public async Task <IList <TSelect> > RetrieveAsync <TSelect, TFrom, TWhere>(SelectExpression <TSelect> selectCondition, FromExpression <TFrom> fromCondition, WhereExpression <TWhere> whereCondition, TransactionContext transContext = null)
where TSelect : DatabaseEntity, new()
where TFrom : DatabaseEntity, new()
where TWhere : DatabaseEntity, new()
{
#region Argument Adjusting
if (selectCondition != null)
{
selectCondition.Select(t => t.Id).Select(t => t.Deleted).Select(t => t.LastTime).Select(t => t.LastUser).Select(t => t.Version);
}
if (whereCondition == null)
{
whereCondition = Where <TWhere>();
}
whereCondition.And(t => t.Deleted == false).And <TSelect>(ts => ts.Deleted == false).And <TFrom>(tf => tf.Deleted == false);
#endregion
IList <TSelect> result = null;
IDataReader reader = null;
DatabaseEntityDef selectDef = _entityDefFactory.GetDef <TSelect>();
try
{
IDbCommand command = _sqlBuilder.CreateRetrieveCommand(selectCondition, fromCondition, whereCondition);
reader = await _databaseEngine.ExecuteCommandReaderAsync(transContext?.Transaction, selectDef.DatabaseName, command, transContext != null).ConfigureAwait(false);
result = _modelMapper.ToList <TSelect>(reader);
}
//catch (DbException ex)
//{
// result = new List<TSelect>();
// _logger.LogCritical(ex.Message);
//}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
return(result);
}
/// <summary>
/// 修改，建议每次修改前先select，并放置在一个事务中。
/// 版本控制，如果item中Version未赋值，会无法更改
/// </summary>
public DatabaseResult Update <T>(T item, TransactionContext transContext) where T : DatabaseEntity, new()
{
if (!item.IsValid())
{
return(DatabaseResult.Fail("entity check failed."));
}
DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>();
if (!entityDef.DatabaseWriteable)
{
return(DatabaseResult.NotWriteable());
}
WhereExpression <T> condition = Where <T>();
long id = item.Id;
long version = item.Version;
condition.Where(t => t.Id == id).And(t => t.Deleted == false);
//版本控制
condition.And(t => t.Version == version);
try
{
IDbCommand dbCommand = _sqlBuilder.CreateUpdateCommand(condition, item, "default");
long rows = _databaseEngine.ExecuteCommandNonQuery(transContext?.Transaction, entityDef.DatabaseName, dbCommand);
if (rows == 1)
{
item.Version++;
return(DatabaseResult.Succeeded());
}
else if (rows == 0)
{
return(DatabaseResult.NotFound());
}
throw new Exception("Multiple Rows Affected instead of one. Something go wrong.");
}
catch (DbException ex)
{
//_logger.LogCritical(ex.Message);
return(DatabaseResult.Fail(ex));
}
}
public IList <T> Retrieve <T>(SelectExpression <T> selectCondition, FromExpression <T> fromCondition, WhereExpression <T> whereCondition, TransactionContext transContext)
where T : DatabaseEntity, new()
{
#region Argument Adjusting
if (selectCondition != null)
{
selectCondition.Select(t => t.Id).Select(t => t.Deleted).Select(t => t.LastTime).Select(t => t.LastUser).Select(t => t.Version);
}
if (whereCondition == null)
{
whereCondition = Where <T>();
}
whereCondition.And(t => t.Deleted == false);
#endregion
IList <T> result = null;
IDataReader reader = null;
DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>();
try
{
IDbCommand command = _sqlBuilder.CreateRetrieveCommand <T>(selectCondition, fromCondition, whereCondition);
reader = _databaseEngine.ExecuteCommandReader(transContext?.Transaction, entityDef.DatabaseName, command, transContext != null);
result = _modelMapper.ToList <T>(reader);
}
//catch (DbException ex)
//{
// result = new List<T>();
// _logger.LogCritical(ex.Message);
//}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
return(result);
}
public IDbCommand CreateAddOrUpdateCommand <T>(T entity, string lastUser) where T : Entity, new()
{
DatabaseEntityDef modelDef = _entityDefFactory.GetDef <T>();
List <IDataParameter> parameters = new List <IDataParameter>();
string cacheKey = GetCreateAddOrUpdateTemplateCacheKey(modelDef);
if (!_sqlStatementDict.TryGetValue(cacheKey, out string addOrUpdateTemplate))
{
addOrUpdateTemplate = CreateAddOrUpdateTemplate(modelDef, _databaseEngine.EngineType);
_sqlStatementDict.TryAdd(cacheKey, addOrUpdateTemplate);
}
foreach (DatabaseEntityPropertyDef info in modelDef.Properties)
{
if (info.IsTableProperty)
{
if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime")
{
continue;
}
//当IsTableProperty为true时，DbParameterizedName一定不为null
if (info.PropertyName == "Version")
{
parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, entity.Version + 1, info.DbFieldType));
}
else if (info.PropertyName == "Deleted")
{
parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, 0, info.DbFieldType));
}
else if (info.PropertyName == "LastUser")
{
parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, DbParameterValue_Statement(lastUser, info), info.DbFieldType));
}
else
{
parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, DbParameterValue_Statement(info.GetValue(entity), info), info.DbFieldType));
}
}
}
return(AssembleCommand <T, T>(false, addOrUpdateTemplate, null, null, parameters));
}
public static string CreateAddTemplate(DatabaseEntityDef modelDef, DatabaseEngineType engineType)
{
StringBuilder args = new StringBuilder();
StringBuilder selectArgs = new StringBuilder();
StringBuilder values = new StringBuilder();
foreach (DatabaseEntityPropertyDef info in modelDef.Properties)
{
if (info.IsTableProperty)
{
selectArgs.AppendFormat(GlobalSettings.Culture, "{0},", info.DbReservedName);
if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime")
{
continue;
}
args.AppendFormat(GlobalSettings.Culture, "{0},", info.DbReservedName);
values.AppendFormat(GlobalSettings.Culture, " {0},", info.DbParameterizedName);
}
}
if (selectArgs.Length > 0)
{
selectArgs.Remove(selectArgs.Length - 1, 1);
}
if (args.Length > 0)
{
args.Remove(args.Length - 1, 1);
}
if (values.Length > 0)
{
values.Remove(values.Length - 1, 1);
}
DatabaseEntityPropertyDef idProperty = modelDef.GetProperty("Id") !;
return($"insert into {modelDef.DbTableReservedName}({args}) values({values});select {selectArgs} from {modelDef.DbTableReservedName} where {idProperty.DbReservedName} = {GetLastInsertIdStatement(engineType)};");
}
public IDbCommand CreateDeleteCommand <T>(WhereExpression <T> condition, string lastUser) where T : DatabaseEntity, new()
{
DatabaseEntityDef definition = _entityDefFactory.GetDef <T>();
string cacheKey = definition.DatabaseName + ":" + definition.TableName + ":DELETE";
if (!_sqlStatementDict.TryGetValue(cacheKey, out string deleteTemplate))
{
deleteTemplate = CreateDeleteTemplate(definition);
_sqlStatementDict.TryAdd(cacheKey, deleteTemplate);
}
DatabaseEntityPropertyDef lastUserProperty = definition.GetProperty("LastUser");
List <IDataParameter> parameters = new List <IDataParameter>();
parameters.Add(_databaseEngine.CreateParameter(lastUserProperty.DbParameterizedName, DbParameterValue_Statement(lastUser, lastUserProperty), lastUserProperty.DbFieldType));
return(AssembleCommand <T, T>(false, deleteTemplate, null, condition, parameters));
}
public IDbCommand CreateBatchDeleteCommand <T>(IEnumerable <T> entities, string lastUser) where T : Entity, new()
{
ThrowIf.Empty(entities, nameof(entities));
StringBuilder innerBuilder = new StringBuilder();
DatabaseEntityDef definition = _entityDefFactory.GetDef <T>();
string tempTableName = "HBD" + DateTimeOffset.UtcNow.Ticks.ToString(GlobalSettings.Culture);
foreach (T entity in entities)
{
string lastUserValue = lastUser == null ? "null" : _databaseEngine.GetDbValueStatement(lastUser, needQuoted: true);
string args = $"`Deleted` = 1, `LastUser` = {lastUserValue}, `Version` = {entity.Version + 1}";
innerBuilder.Append(
$"UPDATE {definition.DbTableReservedName} set {args} WHERE `Id`={entity.Id} AND `Version`={entity.Version} and `Deleted`=0;{TempTable_Insert(tempTableName, FoundChanges_Statement(_databaseEngine.EngineType), _databaseEngine.EngineType)}");
}
string sql = $"{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}{TempTable_Create(tempTableName, _databaseEngine.EngineType)}{innerBuilder}{TempTable_Select_All(tempTableName, _databaseEngine.EngineType)}{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}";
return(AssembleCommand <T, T>(false, sql, null, null, null));
}
/// <summary>
/// 增加,并且item被重新赋值
/// </summary>
public async Task <DatabaseResult> AddAsync <T>(T item, TransactionContext transContext) where T : DatabaseEntity, new()
{
if (!item.IsValid())
{
//TODO: 给所有使用到IsValid（）方法的地方，都加上GetValidateErrorMessage输出
return(DatabaseResult.Fail($"entity check failed.{item.GetValidateErrorMessage()}"));
}
DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>();
if (!entityDef.DatabaseWriteable)
{
return(DatabaseResult.NotWriteable());
}
IDataReader reader = null;
try
{
IDbCommand dbCommand = _sqlBuilder.CreateAddCommand(item, "default");
reader = await _databaseEngine.ExecuteCommandReaderAsync(transContext?.Transaction, entityDef.DatabaseName, dbCommand, true).ConfigureAwait(false);
_modelMapper.ToObject(reader, item);
return(DatabaseResult.Succeeded());
}
catch (DbException ex)
{
//_logger.LogCritical(ex.Message);
return(DatabaseResult.Fail(ex));
}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
}
public IDbCommand CreateUpdateCommand <T>(WhereExpression <T> condition, T entity, string lastUser) where T : Entity, new()
{
DatabaseEntityDef definition = _entityDefFactory.GetDef <T>();
List <IDataParameter> parameters = new List <IDataParameter>();
string cacheKey = definition.DatabaseName + ":" + definition.TableName + ":UPDATE";
if (!_sqlStatementDict.TryGetValue(cacheKey, out string updateTemplate))
{
updateTemplate = CreateUpdateTemplate(definition);
_sqlStatementDict.TryAdd(cacheKey, updateTemplate);
}
foreach (DatabaseEntityPropertyDef info in definition.Properties)
{
if (info.IsTableProperty)
{
if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime" || info.PropertyName == "Deleted")
{
continue;
}
//当IsTableProperty为true时，DbParameterizedName一定不为null
if (info.PropertyName == "Version")
{
parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, entity.Version + 1, info.DbFieldType));
}
else if (info.PropertyName == "LastUser")
{
parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, DbParameterValue_Statement(lastUser, info), info.DbFieldType));
}
else
{
parameters.Add(_databaseEngine.CreateParameter(info.DbParameterizedName !, DbParameterValue_Statement(info.GetValue(entity), info), info.DbFieldType));
}
}
}
return(AssembleCommand <T, T>(false, updateTemplate, null, condition, parameters));
}
/// <summary>
/// 增加,并且item被重新赋值
/// </summary>
public DatabaseResult Add <T>(T item, TransactionContext transContext) where T : DatabaseEntity, new()
{
if (!item.IsValid())
{
return(DatabaseResult.Fail("entity check failed."));
}
DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>();
if (!entityDef.DatabaseWriteable)
{
return(DatabaseResult.NotWriteable());
}
IDataReader reader = null;
try
{
IDbCommand dbCommand = _sqlBuilder.CreateAddCommand(item, "default");
reader = _databaseEngine.ExecuteCommandReader(transContext?.Transaction, entityDef.DatabaseName, dbCommand, true);
_modelMapper.ToObject(reader, item);
return(DatabaseResult.Succeeded());
}
catch (DbException ex)
{
//_logger.LogCritical(ex.Message);
return(DatabaseResult.Fail(ex));
}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
}
public static string CreateUpdateTemplate(DatabaseEntityDef modelDef)
{
StringBuilder args = new StringBuilder();
foreach (DatabaseEntityPropertyDef info in modelDef.Properties)
{
if (info.IsTableProperty)
{
if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime" || info.PropertyName == "Deleted")
{
continue;
}
args.Append($" {info.DbReservedName}={info.DbParameterizedName},");
}
}
if (args.Length > 0)
{
args.Remove(args.Length - 1, 1);
}
return($"UPDATE {modelDef.DbTableReservedName} SET {args}");
}
/// <summary>
/// 批量更改
/// </summary>
/// <param name="items"></param>
/// <returns></returns>
public DatabaseResult BatchUpdate <T>(IEnumerable <T> items, string lastUser, TransactionContext transContext) where T : DatabaseEntity, new()
{
if (transContext == null)
{
return(DatabaseResult.Fail(new ArgumentNullException(nameof(transContext))));
}
if (!CheckEntities <T>(items))
{
return(DatabaseResult.Fail("entities not valid."));
}
DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>();
if (!entityDef.DatabaseWriteable)
{
return(DatabaseResult.NotWriteable());
}
IDataReader reader = null;
try
{
IDbCommand dbCommand = _sqlBuilder.CreateBatchUpdateStatement(items, lastUser);
reader = _databaseEngine.ExecuteCommandReader(
transContext.Transaction,
entityDef.DatabaseName,
dbCommand,
true);
int count = 0;
while (reader.Read())
{
int matched = reader.GetInt32(0);
if (matched != 1)
{
throw new DatabaseException("BatchUpdate wrong, not find the {" + count + "}th data item. ");
}
count++;
}
if (count != items.Count())
{
throw new DatabaseException("BatchUpdate wrong number return. Some Rows not exists.");
}
return(DatabaseResult.Succeeded());
}
catch (Exception ex)
{
//_logger.LogCritical(ex.Message);
return(DatabaseResult.Fail(ex));
}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
}
public static string CreateAddOrUpdateTemplate(DatabaseEntityDef modelDef, DatabaseEngineType engineType)
{
StringBuilder args = new StringBuilder();
StringBuilder selectArgs = new StringBuilder();
StringBuilder values = new StringBuilder();
StringBuilder exceptGuidAndFixedVersionUpdatePairs = new StringBuilder();
foreach (DatabaseEntityPropertyDef info in modelDef.Properties)
{
if (info.IsTableProperty)
{
selectArgs.AppendFormat(GlobalSettings.Culture, "{0},", info.DbReservedName);
if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "LastTime")
{
continue;
}
args.AppendFormat(GlobalSettings.Culture, "{0},", info.DbReservedName);
values.AppendFormat(GlobalSettings.Culture, " {0},", info.DbParameterizedName);
}
}
foreach (DatabaseEntityPropertyDef info in modelDef.Properties)
{
if (info.IsTableProperty)
{
if (info.IsAutoIncrementPrimaryKey || info.PropertyName == "Version" || info.PropertyName == "Guid" || info.PropertyName == "LastTime" || info.PropertyName == "Deleted")
{
continue;
}
exceptGuidAndFixedVersionUpdatePairs.Append($" {info.DbReservedName}={info.DbParameterizedName},");
}
}
DatabaseEntityPropertyDef versionPropertyDef = modelDef.GetProperty("Version") !;
exceptGuidAndFixedVersionUpdatePairs.Append($" {versionPropertyDef.DbReservedName}={versionPropertyDef.DbReservedName}+1,");
if (selectArgs.Length > 0)
{
selectArgs.Remove(selectArgs.Length - 1, 1);
}
if (args.Length > 0)
{
args.Remove(args.Length - 1, 1);
}
if (values.Length > 0)
{
values.Remove(values.Length - 1, 1);
}
if (exceptGuidAndFixedVersionUpdatePairs.Length > 0)
{
exceptGuidAndFixedVersionUpdatePairs.Remove(exceptGuidAndFixedVersionUpdatePairs.Length - 1, 1);
}
DatabaseEntityPropertyDef guidProperty = modelDef.GetProperty("Guid") !;
return($"insert into {modelDef.DbTableReservedName}({args}) values({values}) {OnDuplicateKeyUpdateStatement(engineType)} {exceptGuidAndFixedVersionUpdatePairs};select {selectArgs} from {modelDef.DbTableReservedName} where {guidProperty.DbReservedName} = {guidProperty.DbParameterizedName};");
}
public async Task <DatabaseResult> BatchDeleteAsync <T>(IEnumerable <T> items, string lastUser, TransactionContext transContext) where T : DatabaseEntity, new()
{
if (transContext == null)
{
return(DatabaseResult.Fail(new ArgumentNullException(nameof(transContext))));
}
if (!CheckEntities <T>(items))
{
return(DatabaseResult.Fail("Entities not valid"));
}
DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>();
if (!entityDef.DatabaseWriteable)
{
return(DatabaseResult.NotWriteable());
}
IDataReader reader = null;
try
{
IDbCommand dbCommand = _sqlBuilder.CreateBatchDeleteStatement(items, lastUser);
reader = await _databaseEngine.ExecuteCommandReaderAsync(
transContext.Transaction,
entityDef.DatabaseName,
dbCommand,
true);
int count = 0;
while (reader.Read())
{
int affected = reader.GetInt32(0);
if (affected != 1)
{
throw new DatabaseException("BatchDelete wrong, not found the {" + count + "}th data item. ");
}
count++;
}
if (count != items.Count())
{
throw new DatabaseException("BatchDelete wrong number return. Some data is not found.");
}
return(DatabaseResult.Succeeded());
}
catch (Exception ex)
{
//_logger.Error_BatchDelete_Thrown(ex, lastUser);
return(DatabaseResult.Fail(ex));
}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
}
private string MySQL_Table_Create_Statement(Type type, bool addDropStatement)
{
StringBuilder sql = new StringBuilder();
DatabaseEntityDef definition = _entityDefFactory.GetDef(type);
if (definition.DbTableReservedName.IsNullOrEmpty())
{
throw new DatabaseException($"Type : {definition.EntityFullName} has null or empty DbTableReservedName");
}
foreach (DatabaseEntityPropertyDef info in definition.Properties)
{
if (!info.IsTableProperty)
{
continue;
}
if (info.PropertyName.IsIn("Id", "Deleted", "LastUser", "LastTime", "Version"))
{
continue;
}
int length = 0;
if (info.DbLength == null || info.DbLength == 0)
{
if (info.DbFieldType == DbType.String ||
info.PropertyType == typeof(string) ||
info.PropertyType == typeof(char) ||
info.PropertyType.IsEnum ||
info.PropertyType.IsAssignableFrom(typeof(IList <string>)) ||
info.PropertyType.IsAssignableFrom(typeof(IDictionary <string, string>)))
{
length = _entityDefFactory.GetVarcharDefaultLength();
}
}
else
{
length = info.DbLength.Value;
}
string binary = "";
if (info.PropertyType == typeof(string) || info.PropertyType == typeof(char) || info.PropertyType == typeof(char?))
{
binary = "";
}
string dbTypeStatement = info.TypeConverter == null
? _databaseEngine.GetDbTypeStatement(info.PropertyType)
: info.TypeConverter.TypeToDbTypeStatement(info.PropertyType);
if (length >= 16383) //因为utf8mb4编码，一个汉字4个字节
{
dbTypeStatement = "MEDIUMTEXT";
}
if (length >= 4194303)
{
throw new DatabaseException($"字段长度太长。{info.EntityDef.EntityFullName} : {info.PropertyName}");
}
if (info.IsLengthFixed)
{
dbTypeStatement = "CHAR";
}
sql.AppendFormat(GlobalSettings.Culture, " {0} {1}{2} {6} {3} {4} {5},",
info.DbReservedName,
dbTypeStatement,
(length == 0 || dbTypeStatement == "MEDIUMTEXT") ? "" : "(" + length + ")",
info.IsNullable == true ? "" : " NOT NULL ",
string.IsNullOrEmpty(info.DbDefaultValue) ? "" : "DEFAULT " + info.DbDefaultValue,
!info.IsAutoIncrementPrimaryKey && !info.IsForeignKey && info.IsUnique ? " UNIQUE " : "",
binary
);
sql.AppendLine();
}
string dropStatement = string.Empty;
if (addDropStatement)
{
dropStatement = string.Format(GlobalSettings.Culture, "Drop table if exists {0};" + Environment.NewLine, definition.DbTableReservedName);
}
return(string.Format(GlobalSettings.Culture,
"{2}" +
"CREATE TABLE {0} (" + Environment.NewLine +
"`Id` bigint(20) NOT NULL AUTO_INCREMENT," + Environment.NewLine +
"`Deleted` bit(1) NOT NULL DEFAULT b'0'," + Environment.NewLine +
"`LastUser` varchar(100) DEFAULT NULL," + Environment.NewLine +
"`LastTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," + Environment.NewLine +
"`Version` int(11) NOT NULL DEFAULT '0'," + Environment.NewLine +
" {1} " +
" PRIMARY KEY (`Id`) " + Environment.NewLine +
" ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
definition.DbTableReservedName, sql.ToString(), dropStatement));
}
private int CreateTable(DatabaseEntityDef def, TransactionContext transContext)
{
string sql = GetTableCreateStatement(def.EntityType, false);
return(_databaseEngine.ExecuteSqlNonQuery(transContext.Transaction, def.DatabaseName, sql));
}
public IDbCommand CreateBatchUpdateCommand <T>(IEnumerable <T> entities, string lastUser) where T : Entity, new()
{
ThrowIf.Empty(entities, nameof(entities));
StringBuilder innerBuilder = new StringBuilder();
DatabaseEntityDef definition = _entityDefFactory.GetDef <T>();
string tempTableName = "HBU" + DateTimeOffset.UtcNow.Ticks.ToString(GlobalSettings.Culture);
IList <IDataParameter> parameters = new List <IDataParameter>();
int number = 0;
foreach (T entity in entities)
{
StringBuilder args = new StringBuilder();
foreach (DatabaseEntityPropertyDef info in definition.Properties)
{
string parameterizedName = info.DbParameterizedName + number.ToString(GlobalSettings.Culture);
if (info.IsTableProperty)
{
if (info.IsAutoIncrementPrimaryKey)
{
continue;
}
if (info.PropertyName == "LastTime")
{
continue;
}
if (info.PropertyName == "Deleted")
{
continue;
}
if (info.PropertyName == "Version")
{
args.AppendFormat(GlobalSettings.Culture, " {0}={1},", info.DbReservedName, parameterizedName);
parameters.Add(_databaseEngine.CreateParameter(parameterizedName, entity.Version + 1, info.DbFieldType));
}
else if (info.PropertyName == "LastUser")
{
args.AppendFormat(GlobalSettings.Culture, " {0}={1},", info.DbReservedName, parameterizedName);
parameters.Add(_databaseEngine.CreateParameter(parameterizedName, DbParameterValue_Statement(lastUser, info), info.DbFieldType));
}
else
{
args.AppendFormat(GlobalSettings.Culture, " {0}={1},", info.DbReservedName, parameterizedName);
parameters.Add(_databaseEngine.CreateParameter(parameterizedName, DbParameterValue_Statement(info.GetValue(entity), info), info.DbFieldType));
}
}
}
if (args.Length > 0)
{
args.Remove(args.Length - 1, 1);
}
innerBuilder.Append($"update {definition.DbTableReservedName} set {args} WHERE `Id`={entity.Id} and `Version`={entity.Version} and `Deleted`=0;{TempTable_Insert(tempTableName, FoundChanges_Statement(_databaseEngine.EngineType), _databaseEngine.EngineType)}");
number++;
}
string sql = $"{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}{TempTable_Create(tempTableName, _databaseEngine.EngineType)}{innerBuilder}{TempTable_Select_All(tempTableName, _databaseEngine.EngineType)}{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}";
return(AssembleCommand <T, T>(false, sql, null, null, parameters));
}
public IDbCommand CreateBatchAddOrUpdateCommand <T>(IEnumerable <T> entities, string lastUser) where T : Entity, new()
{
ThrowIf.Empty(entities, nameof(entities));
DatabaseEntityDef modelDef = _entityDefFactory.GetDef <T>();
DatabaseEntityPropertyDef versionPropertyDef = modelDef.GetProperty("Version") !;
StringBuilder innerBuilder = new StringBuilder();
string tempTableName = "HBARU" + DateTimeOffset.UtcNow.Ticks.ToString(GlobalSettings.Culture);
IList <IDataParameter> parameters = new List <IDataParameter>();
int number = 0;
foreach (T entity in entities)
{
StringBuilder args = new StringBuilder();
StringBuilder values = new StringBuilder();
StringBuilder exceptGuidAndFixedVersionUpdatePairs = new StringBuilder();
foreach (DatabaseEntityPropertyDef info in modelDef.Properties)
{
string parameterizedName = info.DbParameterizedName + number.ToString(GlobalSettings.Culture);
if (info.IsTableProperty)
{
if (info.IsAutoIncrementPrimaryKey)
{
continue;
}
if (info.PropertyName == "LastTime")
{
continue;
}
args.AppendFormat(GlobalSettings.Culture, " {0},", info.DbReservedName);
if (info.PropertyName == "Version")
{
values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName);
parameters.Add(_databaseEngine.CreateParameter(parameterizedName, entity.Version + 1, info.DbFieldType));
}
else if (info.PropertyName == "Deleted")
{
values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName);
parameters.Add(_databaseEngine.CreateParameter(parameterizedName, 0, info.DbFieldType));
}
else if (info.PropertyName == "LastUser")
{
values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName);
parameters.Add(_databaseEngine.CreateParameter(parameterizedName, DbParameterValue_Statement(lastUser, info), info.DbFieldType));
}
else
{
values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName);
parameters.Add(_databaseEngine.CreateParameter(parameterizedName, DbParameterValue_Statement(info.GetValue(entity), info), info.DbFieldType));
}
//update pairs
if (info.PropertyName == "Version" || info.PropertyName == "Guid" || info.PropertyName == "Deleted")
{
continue;
}
exceptGuidAndFixedVersionUpdatePairs.Append($" {info.DbReservedName}={parameterizedName},");
}
}
exceptGuidAndFixedVersionUpdatePairs.Append($" {versionPropertyDef.DbReservedName}={versionPropertyDef.DbReservedName}+1,");
if (args.Length > 0)
{
args.Remove(args.Length - 1, 1);
}
if (values.Length > 0)
{
values.Remove(values.Length - 1, 1);
}
if (exceptGuidAndFixedVersionUpdatePairs.Length > 0)
{
exceptGuidAndFixedVersionUpdatePairs.Remove(exceptGuidAndFixedVersionUpdatePairs.Length - 1, 1);
}
innerBuilder.Append($"insert into {modelDef.DbTableReservedName}({args}) values ({values}) {OnDuplicateKeyUpdateStatement(_databaseEngine.EngineType)} {exceptGuidAndFixedVersionUpdatePairs};{TempTable_Insert(tempTableName, FoundChanges_Statement(_databaseEngine.EngineType), _databaseEngine.EngineType)}");
number++;
}
string sql = $"{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}{TempTable_Create(tempTableName, _databaseEngine.EngineType)}{innerBuilder}{TempTable_Select_All(tempTableName, _databaseEngine.EngineType)}{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}";
return(AssembleCommand <T, T>(false, sql, null, null, parameters));
}
private static string GetCreateAddOrUpdateTemplateCacheKey(DatabaseEntityDef modelDef)
{
return(modelDef.DatabaseName + ":" + modelDef.TableName + ":ADDORUPDATE");
}
public TransactionContext BeginTransaction <T>(IsolationLevel isolationLevel) where T : DatabaseEntity
{
DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>();
return(BeginTransaction(entityDef.DatabaseName, isolationLevel));
}
public IDbCommand CreateBatchAddStatement <T>(IEnumerable <T> entities, string lastUser) where T : DatabaseEntity, new()
{
if (entities == null || entities.Count() == 0)
{
throw new ArgumentNullException(nameof(entities));
}
StringBuilder innerBuilder = new StringBuilder();
DatabaseEntityDef definition = _entityDefFactory.GetDef <T>();
string tempTableName = "HBA" + DateTimeOffset.UtcNow.Ticks.ToString();
IList <IDataParameter> parameters = new List <IDataParameter>();
int number = 0;
foreach (T entity in entities)
{
StringBuilder args = new StringBuilder();
StringBuilder values = new StringBuilder();
foreach (DatabaseEntityPropertyDef info in definition.Properties)
{
string parameterizedName = info.DbParameterizedName + number.ToString();
if (info.IsTableProperty)
{
if (info.IsAutoIncrementPrimaryKey)
{
continue;
}
if (info.PropertyName == "LastTime")
{
continue;
}
args.AppendFormat(GlobalSettings.Culture, " {0},", info.DbReservedName);
if (info.PropertyName == "Version")
{
values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName);
parameters.Add(_databaseEngine.CreateParameter(parameterizedName, entity.Version + 1, info.DbFieldType));
}
else if (info.PropertyName == "Deleted")
{
values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName);
parameters.Add(_databaseEngine.CreateParameter(parameterizedName, 0, info.DbFieldType));
}
else if (info.PropertyName == "LastUser")
{
values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName);
parameters.Add(_databaseEngine.CreateParameter(parameterizedName, DbParameterValue_Statement(lastUser, info), info.DbFieldType));
}
else
{
values.AppendFormat(GlobalSettings.Culture, " {0},", parameterizedName);
parameters.Add(_databaseEngine.CreateParameter(parameterizedName, DbParameterValue_Statement(info.GetValue(entity), info), info.DbFieldType));
}
}
}
if (args.Length > 0)
{
args.Remove(args.Length - 1, 1);
}
if (values.Length > 0)
{
values.Remove(values.Length - 1, 1);
}
innerBuilder.Append($"insert into {definition.DbTableReservedName}({args.ToString()}) values ({values.ToString()});{TempTable_Insert(tempTableName, GetLastInsertIdStatement(_databaseEngine.EngineType), _databaseEngine.EngineType)}");
number++;
}
string sql = $"{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}{TempTable_Create(tempTableName, _databaseEngine.EngineType)}{innerBuilder.ToString()}{TempTable_Select_All(tempTableName, _databaseEngine.EngineType)}{TempTable_Drop(tempTableName, _databaseEngine.EngineType)}";
return(AssembleCommand <T, T>(false, sql, null, null, parameters));
}
/// <summary>
/// 批量添加,返回新产生的ID列表
/// </summary>
/// <param name="items"></param>
/// <returns></returns>
public DatabaseResult BatchAdd <T>(IEnumerable <T> items, string lastUser, TransactionContext transContext) where T : DatabaseEntity, new()
{
if (transContext == null)
{
return(DatabaseResult.Fail(new ArgumentNullException(nameof(transContext))));
}
if (!CheckEntities <T>(items))
{
return(DatabaseResult.Fail("entities not valid."));
}
DatabaseEntityDef entityDef = _entityDefFactory.GetDef <T>();
if (!entityDef.DatabaseWriteable)
{
return(DatabaseResult.NotWriteable());
}
IDataReader reader = null;
try
{
DatabaseResult result = DatabaseResult.Succeeded();
IDbCommand dbCommand = _sqlBuilder.CreateBatchAddStatement(items, lastUser);
reader = _databaseEngine.ExecuteCommandReader(
transContext.Transaction,
entityDef.DatabaseName,
dbCommand,
true);
while (reader.Read())
{
int newId = reader.GetInt32(0);
if (newId <= 0)
{
throw new DatabaseException("BatchAdd wrong new id return.");
}
result.AddId(newId);
}
if (result.Ids.Count != items.Count())
{
throw new DatabaseException("BatchAdd wrong new id number return.");
}
return(result);
}
catch (Exception ex)
{
//_logger.LogCritical(ex.Message);
return(DatabaseResult.Fail(ex));
}
finally
{
if (reader != null)
{
reader.Dispose();
}
}
}
