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