private void AddForeignsUpdate(DbCommand cmd, Entity entity)
{
var sbUpdates = new StringBuilder();
var paramIndex = cmd.Parameters.Count;
foreach (var property in
entity.GetForeignsForUpdate().Where(x => x.Value.Values.IsNullOrEmpty<object>() == false))
{
var values =
property.Value.Values.Select(
x => x.ToStringSafe().Split(Const.KeyColSeparator).Select(y => y.Trim()).ToList()).ToList();
var whereParts = new List<string>();
for (int i = 0; i < property.ForeignEntity.Key.Count; i++)
{
var key = property.ForeignEntity.Key[i];
var joinedValues = string.Join(",", values.Select(x => "@" + paramIndex++));
whereParts.Add("{0} In ({1})".Fill(key.ColumnName, joinedValues));
cmd.AddParams(values.Select(x => x[i]).OfType<object>().ToArray());
}
var wherePart = string.Join(" AND ", whereParts);
sbUpdates.AppendLine();
sbUpdates.AppendFormat(
RelatedRecordsUpdateSqlFormat,
property.ForeignEntity.TableName,
entity.Key.FirstOrDefault().ColumnName,
wherePart);
}
cmd.CommandText += sbUpdates.ToString();
}
private void AddForeignsUpdate(DbCommand cmd, EntityRecord entityRecord)
{
var sbUpdates = new StringBuilder();
var paramIndex = cmd.Parameters.Count;
foreach (var propertyValue in entityRecord.Values
.WhereOneToMany()
.Where(value => value.Values.IsNullOrEmpty() == false))
{
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 constraintSeparator = Environment.NewLine + " AND ";
var constraints = string.Join(constraintSeparator, whereParts);
sbUpdates.AppendLine();
var table = propertyValue.Property.ForeignEntity.Table;
var foreignKey = entityRecord.Entity.Key.FirstOrDefault().Column;
sbUpdates.Append($@"UPDATE {table}
SET {foreignKey} = @newID
WHERE {constraints};");
}
cmd.CommandText += sbUpdates.ToString();
}
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 IList<string> GetDeleteRelatedEntityDeleteSql(DbCommand cmd, RecordHierarchy record)
{
// {0} - Foreign table
// {1} - Primary key
// {2} - Key value
const string deleteFormat = "DELETE FROM {0} WHERE {1};";
var paramIndex = cmd.Parameters.Count;
cmd.AddParams(record.KeyValue.ToArray());
var whereParts = new List<string>();
foreach (var key in record.Entity.Key)
{
whereParts.Add("{0} = @{1}".Fill(key.ColumnName, paramIndex++));
}
var wherePart = string.Join(" AND ", whereParts);
var sql = deleteFormat.Fill(
record.Entity.TableName,
wherePart);
var sqls = new List<string>() { sql };
foreach (var subRecord in record.SubRecordsHierarchies)
{
sqls.AddRange(GetDeleteRelatedEntityDeleteSql(cmd, subRecord));
}
return sqls;
}
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 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();
}
protected virtual string GetConstraints(
IEnumerable<PropertyValue> keys,
DbCommand cmd,
string alias = null)
{
if (alias.HasValue())
alias += ".";
var counter = cmd.Parameters.Count;
var whereParts = new List<string>();
foreach (var key in keys)
{
var column = key.Property.Column;
var parameterName = (counter++).ToString();
whereParts.Add($"{alias}{key.Property.Column} = @{parameterName}");
}
var constraintSeparator = Environment.NewLine + " AND ";
var constraints = string.Join(constraintSeparator, whereParts);
cmd.AddParams(keys.Select(value => value.Raw).ToArray());
return constraints;
}