/// <summary>
/// Translates the posted form-data into a model
/// </summary>
/// <param name="tableName">the table-name form which to read the model-fields</param>
/// <param name="form">the form that contains the posted data</param>
/// <returns>a dictionary containing the converted data that was posted</returns>
protected IDictionary <string, object> GetModel(string tableName, IFormCollection form)
{
var retVal = dataSource.GetEntity();
var cols = dataSource.DescribeTable(tableName, true, out _);
foreach (var tmp in cols)
{
if (form.ContainsKey(tmp.ColumnName))
{
var val = form[tmp.ColumnName].FirstOrDefault();
var cvf = TypeConverter.TryConvert(val, tmp.Type.ManagedType);
if (cvf != null)
{
retVal.Add(tmp.ColumnName, cvf);
}
}
}
return(retVal);
}
public IEnumerable ReadForeignKey(string tableName, string id = null, Dictionary <string, object> postedFilter = null)
{
var desc = src.DescribeTable(tableName, true, out _);
var idColumnCount = desc.Count(n => n.IsPrimaryKey);
if (idColumnCount != 1)
{
throw new InvalidOperationException("No Primary-Key was found!");
}
var stringColumnCount = desc.Count(n => n.Type.ManagedType == typeof(string));
if (stringColumnCount == 0)
{
throw new InvalidOperationException("Unable to select a proper foreignkey.");
}
var idColumn = desc.First(n => n.IsPrimaryKey);
var stringCol = desc.First(n => n.Type.ManagedType == typeof(string));
var t = typeof(ForeignKeyData <>).MakeGenericType(idColumn.Type.ManagedType);
var keyAlias = src.SyntaxProvider.FormatObjectName("Key");
var labelAlias = src.SyntaxProvider.FormatObjectName("Label");
if (id != null)
{
return(src.SqlQuery($"Select {src.SyntaxProvider.FormatObjectName(idColumn.ColumnName)} {keyAlias}, {src.SyntaxProvider.FormatObjectName(stringCol.ColumnName)} {labelAlias} from {src.SyntaxProvider.FormatObjectName(tableName)} where {src.SyntaxProvider.FormatObjectName(idColumn.ColumnName)} = [->p0]", t, id));
}
var filterValue = (postedFilter?.ContainsKey("Filter") ?? false) ? postedFilter["Filter"] : null;
if (filterValue != null)
{
List <object> values = new List <object>();
List <string> andFilters = new List <string>();
List <string> orFilters = new List <string>();
string linkOp;
foreach (var col in desc)
{
var target = orFilters;
linkOp = " AND ";
var pnNull = "is not null";
var cv = filterValue;
if (postedFilter.ContainsKey(col.ColumnName))
{
cv = postedFilter[col.ColumnName];
linkOp = " OR ";
pnNull = "is null";
target = andFilters;
}
var pn = $"[->p{values.Count}]";
var op = "=";
if (col.Type.ManagedType == typeof(string))
{
values.Add($"%{cv}%");
op = "like";
}
else
{
values.Add(TypeConverter.TryConvert(cv, col.Type.ManagedType) ?? DBNull.Value);
}
target.Add($"({pn} {pnNull}{linkOp}{src.SyntaxProvider.FormatObjectName(col.ColumnName)} {op} {pn})");
}
List <string> final = new List <string>();
if (andFilters.Count != 0)
{
final.Add($"({string.Join(" AND ", andFilters)})");
}
if (orFilters.Count != 0)
{
final.Add($"({string.Join(" OR ", orFilters)})");
}
return(src.SqlQuery($"Select {src.SyntaxProvider.FormatObjectName(idColumn.ColumnName)} {keyAlias}, {src.SyntaxProvider.FormatObjectName(stringCol.ColumnName)} {labelAlias} from {src.SyntaxProvider.FormatObjectName(tableName)} {(final.Count != 0 ? $"where {string.Join(" AND ", final)}" : "")}", t, values.ToArray()));
}
return(src.SqlQuery($"Select {src.SyntaxProvider.FormatObjectName(idColumn.ColumnName)} {keyAlias}, {src.SyntaxProvider.FormatObjectName(stringCol.ColumnName)} {labelAlias} from {src.SyntaxProvider.FormatObjectName(tableName)}", t));
}