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