public override bool Delete(string tableName, IDictionary <string, object> record)
{
var desc = DescribeTable(tableName, true, out _);
var pks = desc.Where(n => n.IsPrimaryKey).ToArray();
DynamicTableFilter filter;
if (pks.Length > 1)
{
var tmpAgg = new DynamicCompositeFilter(DynamicCompositeFilterType.And);
foreach (var t in pks)
{
tmpAgg.AddFilter(new DynamicTableColumnFilter(t.ColumnName, BinaryCompareFilterOperator.Equal, record[t.ColumnName]));
}
filter = tmpAgg;
}
else
{
filter = new DynamicTableColumnFilter(pks[0].ColumnName, BinaryCompareFilterOperator.Equal, record[pks[0].ColumnName]);
}
using (Facade.UseConnection(out DbCommand cmd))
{
cmd.CommandText = $"delete from [{tableName}] where {BuildWhereClause(pks, filter, cmd)}";
return(cmd.ExecuteNonQuery() == 1);
}
}
private DynamicTableFilter BuildFilters(ICollection <IFilterDescriptor> filters, DynamicCompositeFilterType compositeType)
{
var retVal = new DynamicCompositeFilter(compositeType);
foreach (var filter in filters)
{
if (filter is CompositeFilterDescriptor cp)
{
retVal.AddFilter(BuildFilters(cp.FilterDescriptors, cp.LogicalOperator == FilterCompositionLogicalOperator.And?DynamicCompositeFilterType.And:DynamicCompositeFilterType.Or));
}
else if (filter is FilterDescriptor fd)
{
var value = fd.ConvertedValue;
retVal.AddFilter(new DynamicTableColumnFilter(fd.Member, TranslateOperator(fd.Operator, ref value), value));
}
else
{
throw new InvalidOperationException("Unable to completely translate filter!");
}
}
return(retVal);
}
public override IDictionary <string, object> Update(string tableName, IDictionary <string, object> values)
{
var desc = DescribeTable(tableName, true, out _);
var pks = desc.Where(n => n.IsPrimaryKey).ToArray();
DynamicTableFilter filter;
Dictionary <string, object> paramHolder = new Dictionary <string, object>();
if (pks.Length > 1)
{
var tmpAgg = new DynamicCompositeFilter(DynamicCompositeFilterType.And);
foreach (var t in pks)
{
tmpAgg.AddFilter(new DynamicTableColumnFilter(t.ColumnName, BinaryCompareFilterOperator.Equal, values[t.ColumnName]));
}
filter = tmpAgg;
}
else
{
filter = new DynamicTableColumnFilter(pks[0].ColumnName, BinaryCompareFilterOperator.Equal, values[pks[0].ColumnName]);
}
var sort = new DynamicTableSort {
ColumnName = desc[0].ColumnName, SortOrder = SortOrder.Asc
};
var origin = QueryDynamicTable(tableName, filter, new[] { sort }, out _);
if (origin.Count == 1)
{
var oriItem = origin[0];
var l = new List <(string name, TableColumnDefinition col, object changedValue)>();
foreach (var t in values.Keys)
{
var tmp = BrowseColumns(desc, t, null, null, out var col);
if (tmp != null)
{
var nv = TypeConverter.TryConvert(values[t], col.Type.ManagedType);
var ov = oriItem[t];
var equal = nv == null && ov == null || nv != null && ov != null && nv.Equals(ov);
if (!equal)
{
l.Add(new(col.ColumnName, col, nv));
}
}
}
if (l.Count != 0)
{
using (Facade.UseConnection(out DbCommand cmd))
{
var paramId = 0;
StringBuilder bld = new StringBuilder($"Update [{tableName}] set ");
bool first = true;
foreach (var item in l)
{
if (!first)
{
bld.Append(", ");
}
var param = CreateParameter(cmd, ref paramId, item.changedValue, paramHolder);
bld.Append($"{item.name} = {param.ParameterName}");
cmd.Parameters.Add(param);
first = false;
}
bld.Append($" where {BuildWhereClause(pks, filter, cmd, paramId)}");
cmd.CommandText = paramHolder.FormatText(bld.ToString(), ParameterFilter);
var ok = cmd.ExecuteNonQuery();
if (ok == 1)
{
return(values);
}
}
}
}
return(null);
}
