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