public static DataTable ToDataTable <EO>(this IEnumerable <EO> data, DataTable table = null) where EO : class, new()
{
IEnumerable <PropertyInfo> propertyInfos = typeof(EO).GetProperties().Where(x => Attribute.IsDefined(x, typeof(DbColumnAttribute)));
if (table == null)
{
table = new DataTable();
foreach (PropertyInfo prop in propertyInfos)
{
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
}
table.Clear();
foreach (EO item in data)
{
DataRow row = table.NewRow();
foreach (PropertyInfo prop in propertyInfos)
{
DbColumnAttribute attributeInfo = prop.GetCustomAttribute <DbColumnAttribute>();
string columnName = !string.IsNullOrEmpty(attributeInfo.ColumnName) ? attributeInfo.ColumnName : prop.Name;
row[columnName] = prop.GetValue(item) ?? DBNull.Value;
}
table.Rows.Add(row);
}
return(table);
}
public void BuildLogicalPhyisical()
{
for (int index = 0; index < typeof(T).GetProperties().Count(); index++)
{
DbColumnAttribute l_columnAttr = null;
foreach (DbColumnAttribute attr in typeof(T).GetProperties()[index].GetCustomAttributes(typeof(DbColumnAttribute), false))
{
l_columnAttr = attr;
}
if (l_columnAttr != null)
{
if (l_columnAttr.Name == "ID")
{
Context.mKeyVal.Add(new ColumnVaue
{
ColumnName = l_columnAttr.Name,
Value = typeof(T).InvokeMember(typeof(T).GetProperties()[index].Name, BindingFlags.GetProperty, null, Context.mEntity, null),
IsAutoKey = l_columnAttr.IsAuto,
IsDateField = l_columnAttr.IsDate,
IsPrimaryKey = l_columnAttr.IsPrimary,
});
}
}
}
}
private string GetPrimaryKey()
{
var query = "";
for (int index = 0; index < typeof(T).GetProperties().Count(); index++)
{
DbColumnAttribute l_columnAttr = null;
foreach (DbColumnAttribute attr in typeof(T).GetProperties()[index].GetCustomAttributes(typeof(DbColumnAttribute), false))
{
l_columnAttr = attr;
}
if (l_columnAttr != null)
{
if (l_columnAttr.IsPrimary == true)
{
if (l_columnAttr.IsAuto)
{
query += " WHERE " + l_columnAttr.Name + " =" + typeof(T).InvokeMember(typeof(T).GetProperties()[index].Name, BindingFlags.GetProperty, null, mContext.mEntity, null);
}
else
{
query += " WHERE " + l_columnAttr.Name + " =@" + l_columnAttr.Name;
}
}
}
}
return(query);
}
public void BuildLogicalPhyisical()
{
for (int index = 0; index < typeof(T).GetProperties().Count(); index++)
{
DbColumnAttribute l_columnAttr = null;
foreach (DbColumnAttribute attr in typeof(T).GetProperties()[index].GetCustomAttributes(typeof(DbColumnAttribute), false))
{
l_columnAttr = attr;
}
if (l_columnAttr != null)
{
if (l_columnAttr.IsAuto != true) // Filter unecessary field for insert
{
mContext.mKeyVal.Add(new ColumnVaue
{
ColumnName = l_columnAttr.Name, // column name
Value = typeof(T).InvokeMember(typeof(T).GetProperties()[index].Name, BindingFlags.GetProperty, null, mContext.mEntity, null), // value
IsAutoKey = l_columnAttr.IsAuto, // attribute
IsDateField = l_columnAttr.IsDate, // attribute
IsPrimaryKey = l_columnAttr.IsPrimary, // attribute
});
}
}
}
}
private void BuildColumnParameter(DbColumnAttribute column, MySqlCommand cmd, EntityType entity)
{
if (column.DbType == System.Data.DbType.String && column.Property.PropertyType == typeof(List <int>))
{
var o = column.Property.GetValue(entity);
if (null == o)
{
column.BuildParameter(cmd).Value = "";
}
else
{
column.BuildParameter(cmd).Value = string.Join(",", ((List <int>)o).Select(item => item.ToString()).ToArray());
}
}
else
{
object value = column.Property.GetValue(entity);
if (null == value && column.IsOptional)
{
switch (column.DbType)
{
case System.Data.DbType.String: value = ""; break;
case System.Data.DbType.Currency: value = 0.0m; break;
case System.Data.DbType.Int32: value = 0; break;
}
}
column.BuildParameter(cmd).Value = value;
}
}
private static object ExtractValue(IDataReader reader, PropertyInfo prop)
{
Type type = prop.PropertyType;
string readerValue = string.Empty;
string dbColumnName = prop.Name;
DbColumnAttribute dbColumnAttr = prop.GetCustomAttribute <DbColumnAttribute>();
if (null != dbColumnAttr)
{
dbColumnName = dbColumnAttr.ColumnName;
}
if (type.IsEnum)
{
return(Enum.Parse(type, reader.GetByte(reader.GetOrdinal(dbColumnName)).ToString(), true));
}
if (reader[dbColumnName] != DBNull.Value)
{
readerValue = reader[dbColumnName].ToString();
}
if (!string.IsNullOrEmpty(readerValue))
{
return(Convert.ChangeType(readerValue, type));
}
return(null);
}
public static EntityInfo <TEntity> Create()
{
var entityInfo = new EntityInfo <TEntity>();
entityInfo.TableName = typeof(TEntity).GetAttributeValue((DbNameAttribute tbn) => tbn.Name);
var propertyInfos = new List <KintaPropertyInfo>();
PropertyInfo[] props = typeof(TEntity).GetProperties();
foreach (PropertyInfo prop in props)
{
object[] attrs = prop.GetCustomAttributes(true);
foreach (object attr in attrs)
{
DbColumnAttribute attribute = attr as DbColumnAttribute;
propertyInfos.Add(new KintaPropertyInfo
{
Name = prop.Name,
ColumnName = attribute.Name,
DbType = attribute.DbType,
Length = attribute.Length,
IsKey = attribute.IsKey,
//Value =
});
}
}
entityInfo.PropertyInfos = propertyInfos;
return(entityInfo);
}
public static IList <T> GetExchangeInfo <T> (Type type) where T : ExchangePropertyInfo, new ()
{
List <T> props = new List <T> ();
foreach (PropertyInfo property in type.GetProperties())
{
DbColumnAttribute boAttribute = null;
ExchangePropertyAttribute exAttribute = null;
foreach (object attrib in property.GetCustomAttributes(true))
{
if (boAttribute == null && attrib is DbColumnAttribute)
{
boAttribute = (DbColumnAttribute)attrib;
}
if (exAttribute == null && attrib is ExchangePropertyAttribute)
{
exAttribute = (ExchangePropertyAttribute)attrib;
}
if (boAttribute != null && exAttribute != null)
{
break;
}
}
if (exAttribute == null)
{
continue;
}
string exchangeName;
if (boAttribute != null)
{
exchangeName = Translator.GetExchangeFieldName(boAttribute.DbField);
}
else if (exAttribute.FieldName != DataField.NotSet)
{
exchangeName = Translator.GetExchangeFieldName(exAttribute.FieldName);
}
else
{
exchangeName = exAttribute.DefaultExchangeName;
}
props.Add(new T
{
Name = exchangeName,
IsRequired = exAttribute.Required,
DefaultMapping = exAttribute.DefaultExchangeName,
PropertyInfo = property
});
}
props.Sort((x, y) => string.Compare(x.Name, y.Name));
return(props);
}
private static object ConvertType(object rawValue, PropertyInfo property, DbColumnAttribute attribute)
{
try
{
bool toNullable = Nullable.GetUnderlyingType(property.PropertyType) == rawValue.GetType();
bool numericToBool = property.PropertyType.ContainsType(typeof(bool), typeof(bool?)) &&
rawValue.GetType().ContainsType(typeof(int), typeof(decimal), typeof(Int16));
bool decimalToInt = property.PropertyType.ContainsType(typeof(int), typeof(int?)) &&
rawValue.GetType().ContainsType(typeof(decimal));
bool toGuid = property.PropertyType.ContainsType(typeof(Guid), typeof(Guid?)) &&
rawValue.GetType() == typeof(byte[]);
if (toNullable)
{
return(rawValue);
}
else if (numericToBool)
{
rawValue = (Convert.ToInt16(rawValue) == 1) ? true : false;
}
else if (decimalToInt)
{
rawValue = Convert.ToInt32(rawValue);
}
else if (toGuid)
{
rawValue = new Guid(HexHelper.ToHexString((byte[])rawValue));
}
else if (attribute.ColumnConversionHint == DbColumnAttribute.ColumnConversionHints.ToListByPipe)
{
rawValue = rawValue.ToString().Split('|').ToList();
}
else if (rawValue.GetType() != property.GetType())
{
rawValue = Convert.ChangeType(rawValue, property.PropertyType);
}
return(rawValue);
}
catch (InvalidCastException ex)
{
log.Debug(string.Format("Type Conversion Exception:\n\tRaw Value:{0}(Type:{1})\n\tProperty:{2}(Type:{3})\nSTACKTRACE:\n{4}",
rawValue.ToString(),
rawValue.GetType(),
property.Name,
property.PropertyType.ToString(),
ex.StackTrace));
throw;
}
catch (Exception ex)
{
log.Error($"Message => {ex.Message}\nStacktrace => {ex.StackTrace}");
throw;
}
}
private void BuildLikeParameter(MySqlCommand cmd, StringBuilder sqlBuilder, DbColumnAttribute column, object value)
{
if (column.DbType == System.Data.DbType.String)
{
var s = value as string;
if (null != s)// && s.Contains("|"))
{
string[] values = s.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries).Distinct().ToArray();
if (values.Length != 0)
{
sqlBuilder.Append("(");
try
{
string paramName = string.Format("@param__{0}", cmd.Parameters.Count);// + ConvertToParamName(values[0]);
BuildFuzzyOrLiteralCommand(sqlBuilder, cmd, paramName, column, values[0]);
for (int i = 1; i < values.Length; i++)
{
sqlBuilder.Append(" OR ");
paramName = string.Format("@param__{0}", cmd.Parameters.Count);//paramName = "@" + ConvertToParamName(values[i]);
BuildFuzzyOrLiteralCommand(sqlBuilder, cmd, paramName, column, values[i]);
}
}
finally
{
sqlBuilder.Append(")");
}
return;
}
}
}
else if (column.DbType == System.Data.DbType.Currency)
{
//todo: it will be a string if it is separated by a character
var s = value as string;
if (null != s)
{
string[] minMax = s.Split(new char[] { '-', ':', '|' }, StringSplitOptions.RemoveEmptyEntries);
if (minMax.Length == 2)
{
decimal min = decimal.Parse(minMax[0], System.Globalization.NumberStyles.Currency);
decimal max = decimal.Parse(minMax[1], System.Globalization.NumberStyles.Currency);
sqlBuilder.AppendFormat("(@Min{0} <= {0} AND {0} <= @Max{0})", column.ColumnName);
cmd.Parameters.Add("@Min" + column.ColumnName, column.DbType).Value = min;
cmd.Parameters.Add("@Max" + column.ColumnName, column.DbType).Value = max;
return;
}
}
}
sqlBuilder.AppendFormat("{0} = @{0}", column.ColumnName);
column.BuildParameter(cmd).Value = value;
}
/// <summary>
/// Selects records using partial matching of data. Supports paging through a large number of records.
/// </summary>
/// <param name="start"></param>
/// <param name="count"></param>
/// <param name="filter"></param>
/// <returns></returns>
public IEnumerable <EntityType> Fetch(int start, int count, IDictionary <string, object> filter)
{
if (null == filter || filter.Count == 0)
{
foreach (EntityType entity in Fetch(start, count))
{
yield return(entity);
}
yield break;
}
string[] keys = filter.Keys.ToArray();
List <DbColumnAttribute> filterColumns = new List <DbColumnAttribute>();
foreach (string columnName in keys)
{
DbColumnAttribute column = Columns.Where(item => item.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
if (null == column)
{
yield break;
}
filterColumns.Add(column);
}
using (var db = new Connection(_configuration))
{
using (var cmd = db.connection.CreateCommand() as MySqlCommand)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("select * from {0} where ", TableName);
BuildLikeParameter(cmd, sqlBuilder, filterColumns[0], filter[keys[0]]);
for (int i = 1; i < filterColumns.Count; i++)
{
sqlBuilder.Append(" AND ");
BuildLikeParameter(cmd, sqlBuilder, filterColumns[i], filter[keys[i]]);
}
sqlBuilder.AppendFormat(" order by {0} {1} limit {2}, {3}", OrderColumnName, (OrderAscending ? "asc" : "desc"), start, count);
cmd.CommandText = sqlBuilder.ToString();
using (var reader = cmd.ExecuteReader() as MySqlDataReader)
{
while (reader.Read())
{
yield return(ReadEntity(reader));
}
}
}
}
}
/// <summary>
/// Selects records exactly matching the data in the provided entity
/// </summary>
/// <param name="entity"></param>
/// <param name="columnNames"></param>
/// <returns></returns>
public IEnumerable <EntityType> Where(EntityType entity, params string[] columnNames)
{
if (null == columnNames || columnNames.Length == 0)
{
yield break;
}
List <DbColumnAttribute> filterColumns = new List <DbColumnAttribute>();
foreach (string columnName in columnNames)
{
DbColumnAttribute column = Columns.Where(item => item.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
if (null == column)
{
yield break;
}
filterColumns.Add(column);
}
using (var db = new Connection(_configuration))
{
using (var cmd = db.connection.CreateCommand() as MySqlCommand)
{
StringBuilder sqlBuilder = new StringBuilder();
DbColumnAttribute column = filterColumns[0];
sqlBuilder.AppendFormat("select * from {0} where {1} = @{1}", TableName, column.ColumnName);
BuildColumnParameter(column, cmd, entity);
for (int i = 1; i < filterColumns.Count; i++)
{
column = filterColumns[i];
sqlBuilder.AppendFormat(" and {0} = @{0}", column.ColumnName);
BuildColumnParameter(column, cmd, entity);
}
cmd.CommandText = sqlBuilder.ToString();
using (var reader = cmd.ExecuteReader() as MySqlDataReader)
{
while (reader.Read())
{
yield return(ReadEntity(reader));
}
}
}
}
}
public int Count(IDictionary <string, object> filter)
{
string[] keys = null;
if (null != filter)
{
keys = filter.Keys.ToArray();
}
List <DbColumnAttribute> filterColumns = new List <DbColumnAttribute>();
foreach (string columnName in keys)
{
DbColumnAttribute column = Columns.Where(item => item.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
if (null == column)
{
return(0);
}
filterColumns.Add(column);
}
using (var db = new Connection(_configuration))
{
using (var cmd = db.connection.CreateCommand() as MySqlCommand)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.AppendFormat("select count('x') from {0}", TableName);
if (null != filter && filter.Count != 0)
{
sqlBuilder.Append(" where ");
BuildLikeParameter(cmd, sqlBuilder, filterColumns[0], filter[keys[0]]);
for (int i = 1; i < filterColumns.Count; i++)
{
sqlBuilder.Append(" AND ");
BuildLikeParameter(cmd, sqlBuilder, filterColumns[i], filter[keys[i]]);
}
}
cmd.CommandText = sqlBuilder.ToString();
long result = (long)cmd.ExecuteScalar();
return((int)result);
}
}
}
//todo: eventually include a fuzzy property in the query object and only switch to like if fuzzy is true
private void BuildFuzzyOrLiteralCommand(StringBuilder sqlBuilder, MySqlCommand cmd, string paramName, DbColumnAttribute column, string value)
{
if (value != null && value.Contains("%"))
{
sqlBuilder.AppendFormat("{0} like {1}", column.ColumnName, paramName);
}
else
{
sqlBuilder.AppendFormat("{0} = {1}", column.ColumnName, paramName);
}
cmd.Parameters.Add(paramName, column.DbType).Value = value;
}
/// <summary>
/// Вернуть описание модели в формате JS
///
/// {
/// имя папки: {
/// _type:'folder'
/// _header: 'заголовок'
/// имя класса: {
/// _type:'class'
/// _header: 'заголовок'
/// имя свойства: {
/// _type:'class',
/// _header: 'заголовок',
/// _datatype: 'тип данных',
/// _element: {
/// описателе для elements в формах
/// }
/// _column: {
/// описателе для columns в таблицах и т.д.
/// }
/// },
/// ...
/// имя свойства: {
/// описатель свойства
/// }
/// },
/// ...
/// имя класса: {
/// описатель класса
/// }
/// },
/// ...
/// имя папки: {
/// описатель папки
/// }
/// }
///
/// Папки могут быть вложенные и используются просто для структурирования.
/// При описании классов и свойств можно использовать макро
/// </summary>
/// <returns></returns>
public IActionResult ModelClasses()
{
/*
* string model = "";
* try
* {
* foreach (string filename in Directory.GetFiles(ModelPath, "*.js", SearchOption.AllDirectories))
* {
* model += System.IO.File.ReadAllText(filename) + "\r\n";
* }
* }
* catch (Exception e)
* {
* model = $@"{{'_error_': 'Ошибки при начитке модели ({e})'}} ";
* }
* Response.Headers.Add("Cache-Control", "no-cache");
* return Content(model, "application/javascript", Encoding.UTF8);
*/
JObject stub = new JObject();
try
{
// Набрать все классы какие есть
HashSet <Type> classes = new HashSet <Type>();
foreach (Type t in Model.MainDic.Keys)
{
Type t1 = t;
while (t1 != null && !t1.GetTypeInfo().IsAbstract&& !classes.Contains(t1))
{
classes.Add(t1);
t1 = t1.GetTypeInfo().BaseType;
}
}
// Проход по классам
foreach (Type t in classes)
{
TypeInfo ti = t.GetTypeInfo();
DbTableAttribute ta = ti.GetCustomAttribute <DbTableAttribute>();
EDMClassAttribute ca = ti.GetCustomAttribute <EDMClassAttribute>();
if (ti.IsSubclassOf(typeof(MEDMObj)))
{
JObject jclass = new JObject();
jclass.Add("_mtype", new JValue("class"));
jclass.Add("_header", new JValue(ca.Header));
jclass.Add("_base", new JValue(ti.BaseType.Name));
// Проход по свойствам
foreach (PropertyInfo pi in t.GetProperties())
{
DbColumnAttribute da = pi.GetCustomAttribute <DbColumnAttribute>();
EDMPropertyAttribute pa = pi.GetCustomAttribute <EDMPropertyAttribute>();
if (da != null || pa != null)
{
string n = JFunc.WebixName(pi.Name);
try
{
TypeInfo pt = pi.PropertyType.GetTypeInfo();
if (pi.GetCustomAttribute(typeof(JsonIgnoreAttribute)) == null && pi.GetCustomAttribute(typeof(IgnoreAttribute)) == null)
{
JObject jprop = new JObject();
jprop.Add("_mtype", new JValue("prop"));
if (pa != null)
{
jprop.Add("_header", new JValue(pa.Header));
jprop.Add("_type", new JValue(pa.ItemType.Name));
jprop.Add("_ptype", new JValue(pa.PropType));
jprop.Add("_default", new JValue(MJsonDataConverter.GetDefaulValue(pi.PropertyType)));
jprop.Add("_visible", new JValue(true));
}
else
{
jprop.Add("_default", new JValue(MJsonDataConverter.GetDefaulValue(pi.PropertyType)));
jprop.Add("_visible", new JValue(false));
}
jclass.Add(JFunc.WebixName(n), jprop);
}
}
catch (Exception e1)
{
stub = JObject.Parse($@"{{'_error_': ' Ошибки при формировании базовых классов модели [{t.Name}({n})] ({e1})'}} ");
}
}
}
stub.Add(ti.Name, jclass);
}
}
}
catch (Exception e)
{
string mess = e.Message.Replace(@"\", @"\\").Replace(@"'", @"\'");
stub = JObject.Parse($@"{{'_error_': 'Ошибки при формировании базовых классов модели ({e})'}} ");
}
Response.Headers.Add("Cache-Control", "no-cache");
return(Json(stub));
}