private object DbParameterValue_Statement(object propertyValue, DatabaseEntityPropertyDef info)
{
if (propertyValue == null)
{
return(DBNull.Value);
}
return(info.TypeConverter == null?
_databaseEngine.GetDbValueStatement(propertyValue, needQuoted : false) :
info.TypeConverter.TypeValueToDbValue(propertyValue));
}
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} ");
}
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 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 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));
}
