private void AddParam(DbCommand cmd, PropertyValue propertyValue)
{
if (propertyValue.Raw is ValueBehavior)
{
switch (propertyValue.Raw as ValueBehavior?)
{
case ValueBehavior.Now:
cmd.AddParam(DateTime.Now);
break;
case ValueBehavior.UtcNow:
cmd.AddParam(DateTime.UtcNow);
break;
case ValueBehavior.CurrentUserId:
cmd.AddParam((int)_user.CurrentId());
break;
case ValueBehavior.CurrentUserName:
cmd.AddParam(_user.CurrentUserName());
break;
}
}
else
{
if (propertyValue.Property.TypeInfo.IsFileStoredInDb)
cmd.AddParam(propertyValue.Raw, DbType.Binary);
else
cmd.AddParam(propertyValue.Raw);
}
}
private IList<string> GetDeleteRelatedEntityDeleteSql(DbCommand cmd, RecordHierarchy record)
{
// {0} - Foreign table
// {1} - Primary key
// {2} - Key value
const string deleteFormat = "DELETE FROM {0} WHERE {1} = @{2};";
var paramIndex = cmd.Parameters.Count;
cmd.AddParam(record.KeyValue);
var sql = deleteFormat.Fill(
record.Entity.TableName,
record.Entity.Key.ColumnName,
paramIndex);
var sqls = new List<string>() { sql };
foreach (var subRecord in record.SubRecordsHierarchies)
{
sqls.AddRange(GetDeleteRelatedEntityDeleteSql(cmd, subRecord));
}
return sqls;
}
private string GetSetToNullUpdateSql(DbCommand cmd, Entity entity, RecordHierarchy subRecord)
{
// {0} - Foreign table
// {1} - Foreign key
// {2} - Primary key
// {3} - Key value
const string updateFormat = "UPDATE {0} SET {1} = @{2} WHERE {3} = @{4};";
//UPDATE Products SET CategoryID = null WHERE ProductID = 7
var foreignTable = subRecord.Entity.TableName;
var foreignKey = subRecord.Entity.Properties.FirstOrDefault(x => x.ForeignEntity == entity).ColumnName;
var primaryKey = subRecord.Entity.Key.ColumnName;
var paramIndex = cmd.Parameters.Count;
cmd.AddParam(null);
cmd.AddParam(subRecord.KeyValue);
var updateSql = updateFormat.Fill(
foreignTable,
foreignKey,
paramIndex++,
primaryKey,
paramIndex);
return updateSql;
}
private static void AddParam(DbCommand cmd, Property property)
{
if (property.TypeInfo.IsFileStoredInDb)
cmd.AddParam(property.Value.Raw, DbType.Binary);
else
{
if (property.Value.Raw.IsBehavior(DefaultValueBehavior.Now) ||
property.Value.Raw.IsBehavior(DefaultValueBehavior.NowOnCreate))
{
cmd.AddParam(DateTime.Now);
}
else if (property.Value.Raw.IsBehavior(DefaultValueBehavior.UtcNow) ||
property.Value.Raw.IsBehavior(DefaultValueBehavior.UtcNowOnCreate))
{
cmd.AddParam(DateTime.UtcNow);
}
else
{
cmd.AddParam(property.Value.Raw);
}
}
}
private void AddManyToManyForeignsUpdate(DbCommand cmd, EntityRecord entityRecord)
{
if (entityRecord.Keys.Count > 1)
return;
var sbUpdates = new StringBuilder();
var paramIndex = cmd.Parameters.Count;
foreach (var propertyValue in entityRecord.Values.WhereOneToMany()
.Where(x => x.Property.IsManyToMany))
{
var selectedValues = propertyValue.Values.Select(x => x.ToStringSafe()).ToList();
var mtmEntity = GetEntityToLoad(propertyValue.Property);
var idsToAdd = selectedValues
.ToList();
if (idsToAdd.Any())
{
sbUpdates.AppendLine();
sbUpdates.AppendLine("-- add many to many records");
foreach (var idToAdd in idsToAdd)
{
var foreignEntity = propertyValue.Property.ForeignEntity;
var key1 =
foreignEntity.ForeignKeys.FirstOrDefault(
x => x.ForeignEntity == propertyValue.Property.Entity);
var key2 =
foreignEntity.ForeignKeys.FirstOrDefault(
x => x.ForeignEntity == mtmEntity);
cmd.AddParam(idToAdd);
sbUpdates.AppendLine($"INSERT INTO {foreignEntity.Table} ({key1.Column}, {key2.Column}) VALUES(@newID, @{paramIndex++})");
}
}
}
cmd.CommandText += Environment.NewLine + sbUpdates;
}
private static void AddParam(DbCommand cmd, Property property)
{
if (property.TypeInfo.IsFileStoredInDb)
cmd.AddParam(property.Value.Raw, DbType.Binary);
else
cmd.AddParam(property.Value.Raw);
}
private string GetSetToNullUpdateSql(DbCommand cmd, Entity entity, RecordHierarchy subRecord)
{
// {0} - Foreign table
// {1} - Foreign key
// {2} - Primary key
// {3} - Key value
const string updateFormat = "UPDATE {0} SET {1} = @{2} WHERE {3};";
//UPDATE Products SET CategoryID = null WHERE ProductID = 7
var foreignTable = subRecord.Entity.TableName;
var foreignKey = subRecord.Entity.Properties.FirstOrDefault(x => x.ForeignEntity == entity).ColumnName;
var nullIndex = cmd.Parameters.Count;
var paramIndex = nullIndex + 1;
cmd.AddParam(null);
cmd.AddParams(subRecord.KeyValue.ToArray());
var whereParts = new List<string>();
foreach (var key in subRecord.Entity.Key)
{
whereParts.Add("{0} = @{1}".Fill(key.ColumnName, paramIndex++));
}
var wherePart = string.Join(" AND ", whereParts);
var updateSql = updateFormat.Fill(
foreignTable,
foreignKey,
nullIndex,
wherePart);
return updateSql;
}
private void AddForeignsUpdate(DbCommand cmd, EntityRecord entityRecord)
{
var sbUpdates = new StringBuilder();
var paramIndex = cmd.Parameters.Count;
foreach (var propertyValue in entityRecord.Values.WhereOneToMany())
{
var actualRecords = _source.GetRecords(
propertyValue.Property.ForeignEntity,
new List<BaseFilter>
{
new ForeignEntityFilter(
entityRecord.Entity.Key.FirstOrDefault(),
entityRecord.Key.FirstOrDefault().Raw.ToStringSafe())
}).Records;
var idsToRemoveRelation = actualRecords
.Select(x => x.JoinedKeyValue)
.Except(propertyValue.Values.Select(x => x.ToStringSafe()))
.ToList();
if (idsToRemoveRelation.Any())
{
var values2 =
idsToRemoveRelation.Select(
x => x.Split(Const.KeyColSeparator).Select(y => y.Trim()).ToList()).ToList();
var whereParts2 = new List<string>();
for (int i = 0; i < propertyValue.Property.ForeignEntity.Key.Count; i++)
{
var key = propertyValue.Property.ForeignEntity.Key[i];
var joinedValues = string.Join(",", values2.Select(x => "@" + paramIndex++));
whereParts2.Add("{0} In ({1})".Fill(key.Column, joinedValues));
cmd.AddParams(values2.Select(x => x[i]).OfType<object>().ToArray());
}
var wherePart2 = string.Join(" AND ", whereParts2);
sbUpdates.AppendLine();
sbUpdates.AppendLine("-- set to null update");
sbUpdates.AppendFormat(BuildForeignUpdateSql(
propertyValue.Property.ForeignEntity.Table,
entityRecord.Entity.Key.FirstOrDefault().Column,
(paramIndex++).ToString(),
wherePart2));
cmd.AddParam(null);
}
var values =
propertyValue.Values.Select(
x => x.ToStringSafe().Split(Const.KeyColSeparator).Select(y => y.Trim()).ToList()).ToList();
var whereParts = new List<string>();
for (int i = 0; i < propertyValue.Property.ForeignEntity.Key.Count; i++)
{
var key = propertyValue.Property.ForeignEntity.Key[i];
var joinedValues = string.Join(",", values.Select(x => "@" + paramIndex++));
whereParts.Add("{0} In ({1})".Fill(key.Column, joinedValues));
cmd.AddParams(values.Select(x => x[i]).OfType<object>().ToArray());
}
var wherePart = string.Join(" AND ", whereParts);
sbUpdates.AppendLine();
sbUpdates.Append(BuildForeignUpdateSql(
propertyValue.Property.ForeignEntity.Table,
entityRecord.Entity.Key.FirstOrDefault().Column,
(paramIndex++).ToString(),
wherePart));
cmd.AddParam(entityRecord.Key.FirstOrDefault().Raw);
}
cmd.CommandText += sbUpdates.ToString();
}
private void AddForeignsUpdate(DbCommand cmd, Entity entity)
{
var sbUpdates = new StringBuilder();
var paramIndex = cmd.Parameters.Count;
foreach (var property in entity.GetForeignsForUpdate())
{
var actualRecords = _source.GetRecords(
property.ForeignEntity,
new List<IEntityFilter>
{
new ForeignEntityFilter(
entity.Key,
entity.Key.Value.Raw.ToStringSafe())
});
var idsToRemoveRelation = actualRecords
.Select(x => x.KeyValue)
.Except(property.Value.Values.OfType<string>())
.ToList();
if (idsToRemoveRelation.Any())
{
var removeValues = string.Join(",", idsToRemoveRelation.Select(x => "@" + paramIndex++));
sbUpdates.AppendLine();
sbUpdates.AppendFormat(
RelatedRecordsUpdateSqlFormat,
property.ForeignEntity.TableName,
entity.Key.ColumnName,
paramIndex++,
property.ForeignEntity.Key.ColumnName,
removeValues);
cmd.AddParams(idsToRemoveRelation);
cmd.AddParams(null);
}
var values = string.Join(",", property.Value.Values.Select(x => "@" + paramIndex++));
cmd.CommandText +=
Environment.NewLine +
RelatedRecordsUpdateSqlFormat.Fill(
property.ForeignEntity.TableName,
entity.Key.ColumnName,
paramIndex++,
property.ForeignEntity.Key.ColumnName,
values);
cmd.AddParams(property.Value.Values.ToArray());
cmd.AddParam(entity.Key.Value.Raw);
}
}
private void AddForeignsSql(
DbCommand cmd,
EntityRecord entityRecord,
IDictionary<string, PropertyDeleteOption> options)
{
if (options.Where(x => x.Value.Level == 0)
.All(x => x.Value.DeleteOption == CascadeOption.Nothing ||
x.Value.DeleteOption == CascadeOption.AskUser))
return;
var entityHierarchy = _hierarchySource.GetEntityHierarchy(entityRecord.Entity);
var keyConstraint = BuildKeyConstraint(entityRecord, entityHierarchy.Alias);
var sqlNullParameterName = "";
if (options.Any(x => x.Value.DeleteOption == CascadeOption.Detach))
{
sqlNullParameterName = cmd.Parameters.Count.ToString();
cmd.AddParam(null);
}
var sqlsBuilder = new StringBuilder();
GetForeignSql(
entityHierarchy,
options,
sqlsBuilder,
keyConstraint,
sqlNullParameterName);
cmd.CommandText = sqlsBuilder + cmd.CommandText;
}
private void AddParam(DbCommand cmd, Property property)
{
if (property.OnDeleteDefaultValue is ValueBehavior)
{
switch (property.OnDeleteDefaultValue as ValueBehavior?)
{
case ValueBehavior.Now:
cmd.AddParam(DateTime.Now);
break;
case ValueBehavior.UtcNow:
cmd.AddParam(DateTime.UtcNow);
break;
case ValueBehavior.Guid:
cmd.AddParam(Guid.NewGuid());
break;
case ValueBehavior.CurrentUserId:
cmd.AddParam((int)_user.CurrentId());
break;
case ValueBehavior.CurrentUserName:
cmd.AddParam(_user.CurrentUserName());
break;
}
}
else
{
if (property.TypeInfo.IsFileStoredInDb)
cmd.AddParam(property.OnDeleteDefaultValue, DbType.Binary);
else
cmd.AddParam(property.OnDeleteDefaultValue);
}
}
private void AddManyToManyForeignsUpdate(DbCommand cmd, EntityRecord entityRecord)
{
if (entityRecord.Keys.Count > 1)
return;
var sbUpdates = new StringBuilder();
var paramIndex = cmd.Parameters.Count;
foreach (var propertyValue in entityRecord.Values.WhereOneToMany()
.Where(x => x.Property.IsManyToMany))
{
var recordKey = entityRecord.Keys.FirstOrDefault().AsString;
var actualRecords = _source.GetRecords(
propertyValue.Property.ForeignEntity,
new List<BaseFilter>
{
new ForeignEntityFilter(
entityRecord.Entity.Keys.FirstOrDefault(),
recordKey)
}).Records;
var selectedValues = propertyValue.Values.Select(x => x.ToStringSafe()).ToList();
var mtmEntity = GetEntityToLoad(propertyValue.Property);
var dbIds = actualRecords
.Select(x => x.Keys.FirstOrDefault(y => y.Property.ForeignEntity == mtmEntity).AsString)
.ToList();
var idsToRemove = dbIds
.Except(selectedValues)
.ToList();
if (idsToRemove.Any())
{
sbUpdates.AppendLine();
sbUpdates.AppendLine("-- delete many to many records");
foreach (var idToRemove in idsToRemove)
{
var foreignEntity = propertyValue.Property.ForeignEntity;
var key1 =
foreignEntity.ForeignKeys.FirstOrDefault(
x => x.ForeignEntity == propertyValue.Property.Entity);
var key2 =
foreignEntity.ForeignKeys.FirstOrDefault(
x => x.ForeignEntity == mtmEntity);
cmd.AddParam(recordKey);
cmd.AddParam(idToRemove);
sbUpdates.AppendLine($"DELETE {foreignEntity.Table} WHERE {key1.Column} = @{paramIndex++} and {key2.Column} = @{paramIndex++}");
}
}
var idsToAdd = selectedValues
.Except(dbIds)
.ToList();
if (idsToAdd.Any())
{
sbUpdates.AppendLine();
sbUpdates.AppendLine("-- add many to many records");
foreach (var idToAdd in idsToAdd)
{
var foreignEntity = propertyValue.Property.ForeignEntity;
var key1 =
foreignEntity.ForeignKeys.FirstOrDefault(
x => x.ForeignEntity == propertyValue.Property.Entity);
var key2 =
foreignEntity.ForeignKeys.FirstOrDefault(
x => x.ForeignEntity == mtmEntity);
cmd.AddParam(recordKey);
cmd.AddParam(idToAdd);
sbUpdates.AppendLine($"INSERT INTO {foreignEntity.Table} ({key1.Column}, {key2.Column}) VALUES(@{paramIndex++}, @{paramIndex++})");
}
}
}
cmd.CommandText += Environment.NewLine + sbUpdates;
}
private string GetConcurrencyCheckValueSql(
DbCommand cmd,
EntityRecord entityRecord,
Property property,
string concurrencyCheckParam)
{
string sql;
if (property == null)
{
var entityChange = _admin.ChangeEntity;
var changedOn = entityChange[nameof(IEntityChange.ChangedOn)];
var changedEntityName = entityChange[nameof(IEntityChange.EntityName)];
var changedEntityKey = entityChange[nameof(IEntityChange.EntityKey)];
var changedEntityNameParam = cmd.Parameters.Count;
cmd.AddParam(entityRecord.Entity.Name);
var changedEntityKeyParam = cmd.Parameters.Count;
cmd.AddParam(entityRecord.JoinedKeysValues);
var constraints = GetConstraints(entityRecord.Keys, cmd, "[t0]");
sql =
$@"@{concurrencyCheckParam} <=
(SELECT TOP 1 [ec].{changedOn.Column}
FROM {entityChange
.Table} as [ec]
INNER JOIN {entityRecord.Entity.Table} as [t0] ON (
[ec].{changedEntityName
.Column} = @{changedEntityNameParam}
AND [ec].{changedEntityKey.Column} = @{changedEntityKeyParam}
)
WHERE {constraints}
ORDER BY [ec].{changedOn
.Column} DESC)";
}
else
{
var constraints = GetConstraints(entityRecord.Keys, cmd);
sql =
$@"@{concurrencyCheckParam} <>
(SELECT {property.Column}
FROM {entityRecord.Entity.Table}
WHERE {constraints})";
}
return sql;
}
protected virtual void AddConcurrencyCheck(
DbCommand cmd,
EntityRecord entityRecord,
object concurrencyCheckValue)
{
if (entityRecord.Entity.ConcurrencyCheckEnabled == false)
return;
if (concurrencyCheckValue == null)
throw new InvalidOperationException(IlaroAdminResources.EmptyConcurrencyCheckValue);
var property = entityRecord.Entity.Properties.FirstOrDefault(x => x.IsConcurrencyCheck);
var concurrencyCheckParam = cmd.Parameters.Count.ToString();
cmd.AddParam(concurrencyCheckValue);
var concurrencyCheckConstraint =
GetConcurrencyCheckValueSql(cmd, entityRecord, property, concurrencyCheckParam);
var concurrencyCheckSql =
$@"-- concurrency check
IF({concurrencyCheckConstraint})
BEGIN
SELECT {Const
.ConcurrencyCheckError_ReturnValue};
RETURN;
END
";
cmd.CommandText = concurrencyCheckSql + cmd.CommandText;
}
