public bool Delete(Entity entity, string key, IList <PropertyDeleteViewModel> propertiesDeleteOptions) { var deleteOptions = propertiesDeleteOptions.ToDictionary(x => x.PropertyName, x => x.DeleteOption); foreach (var property in entity.Properties.Where(x => x.IsForeignKey && x.IsCollection)) { if (!deleteOptions.ContainsKey(property.ForeignEntity.Name)) { deleteOptions[property.ForeignEntity.Name] = property.DeleteOption; } } var table = new DynamicModel(AdminInitialise.ConnectionString, tableName: entity.TableName, primaryKeyField: entity.Key.ColumnName); var keyObject = GetKeyObject(entity, key); var result = 0; if (deleteOptions.All(x => x.Value == DeleteOption.Nothing)) { result = table.Delete(keyObject); } else { var sql = String.Empty; var recordHierarchy = GetRecordHierarchy(entity); foreach (var subRecord in recordHierarchy.SubRecordsHierarchies) { var deleteOption = DeleteOption.Nothing; if (deleteOptions.ContainsKey(subRecord.Entity.Name)) { deleteOption = deleteOptions[subRecord.Entity.Name]; } if (deleteOption == DeleteOption.SetNull) { sql += GetSetToNullUpdateSql(entity, subRecord) + Environment.NewLine; } else if (deleteOption == DeleteOption.CascadeDelete) { sql += string.Join(Environment.NewLine, GetDeleteRelatedEntityDeleteSql(subRecord).Reverse()) + Environment.NewLine; } } var cmd = table.CreateDeleteCommand(key: keyObject); cmd.CommandText = sql + cmd.CommandText; result = table.Execute(cmd); } if (result < 1) { Error(IlaroAdminResources.EntityNotExist); return(false); } AddEntityChange(entity.Name, key, EntityChangeType.Delete); return(true); }
public int Edit(Entity entity) { if (entity.Key.Value == null) { Error(IlaroAdminResources.EntityKeyIsNull); return(0); } var existingItem = GetEntity(entity, entity.Key.Value); if (existingItem == null) { Error(IlaroAdminResources.EntityNotExist); return(0); } var table = new DynamicModel(AdminInitialise.ConnectionString, tableName: entity.TableName, primaryKeyField: entity.Key.ColumnName); var expando = new ExpandoObject(); var filler = expando as IDictionary <String, object>; foreach (var property in entity.CreateProperties(getForeignCollection: false)) { filler[property.ColumnName] = property.Value; } var cmd = table.CreateUpdateCommand(expando, entity.Key.Value); foreach (var property in entity.Properties.Where(x => x.IsForeignKey && x.IsCollection)) { var actualRecords = GetRecords(property.ForeignEntity, new List <IEntityFilter> { new ForeignEntityFilter(entity.Key, entity.Key.Value.ToStringSafe()) }); var idsToRemoveRelation = actualRecords.Select(x => x.KeyValue); idsToRemoveRelation = idsToRemoveRelation.Except(property.Values.OfType <string>()); // UPDATE {TableName} SET {ForeignKey} = {FKValue} WHERE {PrimaryKey} In ({PKValues}); var updateFormat = Environment.NewLine + ";UPDATE {0} SET {1} = {2} WHERE {3} In ({4})"; if (idsToRemoveRelation.Any()) { cmd.CommandText += Environment.NewLine + string.Format(updateFormat, property.ForeignEntity.TableName, entity.Key.ColumnName, "NULL", property.ForeignEntity.Key.ColumnName, DecorateSqlValue(idsToRemoveRelation.OfType <object>().ToList(), property.ForeignEntity.Key)); } cmd.CommandText += Environment.NewLine + string.Format(updateFormat, property.ForeignEntity.TableName, entity.Key.ColumnName, DecorateSqlValue(entity.Key.Value, entity.Key), property.ForeignEntity.Key.ColumnName, DecorateSqlValue(property.Values, property.ForeignEntity.Key)); } var savedItems = table.Execute(cmd); // TODO: get info about changed properties AddEntityChange(entity.Name, entity.Key.StringValue, EntityChangeType.Update); ClearProperties(entity); return(savedItems); }
public static void BuildTables() { // plan is to have adaptive diff and generate accordingly. var db = new DynamicModel(ConnectionString); StringBuilder sb = new StringBuilder(); // we can store the scheme for later use. foreach (Type entity in RegisteredTypes) { string query = entity.CreateTableQuery(); // drop before create. query = string.Format("DROP TABLE {0}; {1}", entity.Name, query); sb.Append(query); } SqlCommand cmd = new SqlCommand(sb.ToString()); db.Execute(cmd); }
public object Create(Entity entity) { var existingItem = GetEntity(entity, entity.Key.Value); if (existingItem != null) { Error(IlaroAdminResources.EntityAlreadyExist); return(null); } FileHandle(entity); var table = new DynamicModel(AdminInitialise.ConnectionString, tableName: entity.TableName, primaryKeyField: entity.Key.ColumnName); var expando = new ExpandoObject(); var filler = expando as IDictionary <String, object>; foreach (var property in entity.CreateProperties(getForeignCollection: false)) { filler[property.ColumnName] = property.Value; } var cmd = table.CreateInsertCommand(expando); cmd.CommandText += Environment.NewLine + ";DECLARE @newID int; SELECT @newID = SCOPE_IDENTITY()"; foreach (var property in entity.Properties.Where(x => x.IsForeignKey && x.IsCollection && !x.Values.IsNullOrEmpty())) { // UPDATE {TableName} SET {ForeignKey} = {FKValue} WHERE {PrimaryKey} In ({PKValues}); var updateFormat = Environment.NewLine + ";UPDATE {0} SET {1} = @newID WHERE {2} In ({3})"; cmd.CommandText += string.Format(updateFormat, property.ForeignEntity.TableName, entity.Key.ColumnName, property.ForeignEntity.Key.ColumnName, DecorateSqlValue(property.Values, property.ForeignEntity.Key)); } cmd.CommandText += Environment.NewLine + ";SELECT @newID"; FixParamsSqlType(entity, cmd, filler); var item = table.Execute(cmd); AddEntityChange(entity.Name, item.ToString(), EntityChangeType.Insert); ClearProperties(entity); return(item); }
public static void CreateBin(string name) { _db.Execute("INSERT INTO bins(NAME) VALUES(@0)", name); }