/// <summary>
/// Build ORMTableInfo class instance from dynamic query result with default provider binding
/// </summary>
/// <param name="query_result">IEnumerable<dynamic>dapper query result</param>
/// <param name="table_name">Table name</param>
/// <param name="As">as short name</param>
/// <returns>ORMTableInfo</returns>
public static ORMTableInfo ORMFromDynamicQueryResult(IEnumerable <dynamic> query_result, string table_name, string As = null)
{
var columnset = new List <column_set_item>();
var first_round = true;
foreach (var row in query_result)
{
if (first_round)
{
first_round = false;
foreach (var pair in row)
{
columnset.Add(new column_set_item()
{
Name = row.Key
});
}
}
var i = 0;
foreach (var pair in row)
{
var colname = pair.Key;
var column_value = pair.Value;
columnset[i].Name = colname;
if (column_value == null)
{
columnset[i].Nullable = true;
}
else
{
columnset[i].Type = column_value.GetType();
}
i++;
}
}
for (int i = 0, c = columnset.Count; i < c; i++)
{
Type coltype = columnset[i].Type;
if (coltype == null)
{
Console.WriteLine(columnset[i].Name + " no values, no type defined! Default: nullable string?");
columnset[i].Type = typeof(string);
}
}
var orm = new ORMTableInfo();
// attributes
var for_table_name = RemoveBrackets(table_name);
orm.Name = ToValidNameRegex.Replace(for_table_name, "_");
orm.TableName = for_table_name;
orm.As = As;
orm.Title = ByViewRule(orm.Name, orm.Rules);
// properties
orm.Props = columnset.Select(row =>
{
var property_name = ToValidNameRegex.Replace(row.Name, "_");
var value_type = row.Type;
if ((value_type.IsValueType || value_type == typeof(DateTime)) && row.Nullable)
{
value_type = Type.GetType(value_type.CSTypeSyntax() + "?");
}
var orm_pi = new ORMPropertyInfo(orm, property_name, row.Type)
{
Field = ByDBRule(property_name, orm.Rules),
Title = ByViewRule(property_name, orm.Rules)
};
return(orm_pi);
}).ToArray();
orm.Keys = orm.Props.Where(prop => prop.isKey).ToArray();
orm.References = orm.Props.Where(prop => prop.RefType != null).ToArray();
return(orm);
}
/// <summary>
/// Link types only for reference to existing type,
/// this link may be incorrect due to duplicate names or assembly binding is incorrect.
/// Eerrors will be detected during compilation of builded souce code and runtime.
/// If referebce target ORM type hasn't created yet
/// need a second pass with existing types to bind.
/// By default reference may writed as string type.
/// Expensive.
/// </summary>
/// <param name="conn"></param>
/// <param name="table_name">name of database table</param>
/// <param name="assemblies">assemblies for metadata reference resolving</param>
/// <param name="parent_list">optional list of ORMTableInfo for iteration and reqursive calling</param>
/// <returns></returns>
public static IList <ORMTableInfo> ORMFromSCHEMA(IDbConnection conn, string table_name, IEnumerable <Assembly> assemblies, IList <ORMTableInfo> parent_list = null)
{
var result = new List <ORMTableInfo>();
var orm_table_name = RemoveBrackets(table_name);
var orm_name = ToValidNameRegex.Replace(orm_table_name, "_");
var already_exist = parent_list?.FirstOrDefault(orm => RemoveBrackets(orm.TableName) == orm_table_name);
if (already_exist != null)
{
result.Add(already_exist);
return(result);
}
// Construct orm & attribytes
ORMTableInfo.SetDefaultDBProvider(DBProviderEnum.MSSql);
var orm = new ORMTableInfo()
{
Name = orm_name, TableName = table_name, As = orm_name.ToLower()
};
try
{
// types linking to existing type only for reference
// this link may be incorrect due to duplicate names or assembly binding is incorrect
// errors will be detected during compilation of builded souce code and runtime
// try by table name
foreach (var assembly in assemblies)
{
var types = assembly.GetTypes();
foreach (var type in types)
{
var ormtable_attr = type.GetCustomAttribute <ORMTableAttribute>();
if (ormtable_attr != null && RemoveBrackets(ormtable_attr.TableName) == table_name)
{
orm.Type = type;
break;
}
}
}
// try by structure or class name
foreach (var assembly in assemblies)
{
var types = assembly.GetTypes();
foreach (var type in types)
{
var ormtable_attr = type.GetCustomAttribute <ORMTableAttribute>();
if (ormtable_attr != null && (type.Name == table_name || type.Name == ToValidNameRegex.Replace(table_name, "_")) && ormtable_attr.TableName.Blank())
{
orm.Type = type;
break;
}
}
}
}
catch (Exception e)
{
Console.WriteLine($@"Exception during explore metadata for resolving orm type for {orm.Name}");
Console.WriteLine(e.Message);
}
result.Add(orm);
// load schema
var schema_columns = conn.Query <SCHEMA_COLUMNS>($@"select * from INFORMATION_SCHEMA.COLUMNS");
var columns = schema_columns.Where(row => row.TABLE_NAME.Trim() == table_name);
var schema_pks = conn.Query <sp_pkeys>($@"EXEC sp_pkeys [{table_name}]");
var schema_fks = conn.Query <sp_foreign_keys_rowset2>($@"EXEC sp_foreign_keys_rowset2 [{table_name}]");
// construct orm properties
var properties = new List <ORMPropertyInfo>();
foreach (var row in columns)
{
// orm property attributes
var type = ParseDataType(row.DATA_TYPE);
var name = ToValidNameRegex.Replace(row.COLUMN_NAME, "_");
var title = ByViewRule(row.COLUMN_NAME, orm.Rules);
var field = ByDBRule(row.COLUMN_NAME, orm.Rules);
var orm_pi = new ORMPropertyInfo(orm, name, type)
{
Title = title, Field = field
};
// from schema
orm_pi.isKey = schema_pks.Any(row => row.COLUMN_NAME == field);
var target_table_name = (schema_fks.FirstOrDefault(row => row.FK_COLUMN_NAME == field))?.PK_TABLE_NAME;
if (target_table_name != null)
{
// create referenced orm info types
var orm_list = ORMFromSCHEMA(conn, target_table_name, assemblies, result);
result.AddRange(orm_list.Where(orm => !result.Contains(orm)));
var ref_target_orm = result.FirstOrDefault(orm => RemoveBrackets(orm.TableName) == target_table_name);
if (ref_target_orm == null)
{
ref_target_orm = result.FirstOrDefault(orm => (orm.Name == target_table_name || type.Name == ToValidNameRegex.Replace(table_name, "_")));
}
if (ref_target_orm != null && ref_target_orm.Type != null)
{
// check ref target type exists
orm_pi.RefType = ref_target_orm.Type;
// there:
// link type only for reference to existing type
// this link may be incorrect due to duplicate names or assembly binding is incorrect
// errors will be detected during compilation of builded souce code
}
if (orm_pi.RefType == null)
{
Console.WriteLine($@"Unresolved target orm type for reference property {orm.Name}.{orm_pi.Name}");
// The target ORM type hasn't created yet
// need a second pass with existing types to bind.
// For default write as string:
orm_pi.RefType = typeof(string);
}
}
properties.Add(orm_pi);
}
orm.Props = properties.ToArray();
orm.Keys = properties.Where(prop => prop.isKey).ToArray();
orm.References = properties.Where(prop => prop.RefType != null).ToArray();
return(result);
}
