/// <summary> /// Select *Columns* Where *Fields* Equal Null /// </summary> /// <typeparam name="T"></typeparam> /// <param name="selectOptions"></param> /// <param name="conditionalOptions"></param> /// <param name="columns"></param> /// <param name="fields"></param> /// <param name="convertTo"></param> /// <returns></returns> public static List <T> SelectFieldsWhereFieldNull(string[] columns, string[] fields, SelectLimiter limit = null) { return(Query.BuildSelect <T>(SelectOptions.FIELDS, ConditionalOptions.WHERENULL, columns, fields, null, null, WhereOption.NONE, limit)); }
/// <summary> /// Select *Columns* Where *Fields* Equal Null /// </summary> /// <typeparam name="T"></typeparam> /// <param name="selectOptions"></param> /// <param name="conditionalOptions"></param> /// <param name="column"></param> /// <param name="fields"></param> /// <param name="convertTo"></param> /// <returns></returns> public static List <T> SelectFieldWhereFieldNull(string column, string field, SelectLimiter limit = null) { return(SelectFieldsWhereFieldNull(new string[] { column }, new string[] { field }, limit)); }
/// <summary> /// Select * Where *Fields* Equal Null /// </summary> /// <typeparam name="T"></typeparam> /// <param name="selectOptions"></param> /// <param name="conditionalOptions"></param> /// <param name="columns"></param> /// <param name="convertTo"></param> /// <returns></returns> public static List <T> SelectAllWhereFieldNull(string[] fields, SelectLimiter limit = null) { return(Query.BuildSelect <T>(SelectOptions.ALL, ConditionalOptions.WHERENULL, null, fields, null, null, WhereOption.NONE, limit)); }
/// <summary> /// Select * Where *Fields* Equal Null /// </summary> /// <typeparam name="T"></typeparam> /// <param name="selectOptions"></param> /// <param name="conditionalOptions"></param> /// <param name="columns"></param> /// <param name="convertTo"></param> /// <returns></returns> public static List <T> SelectAllWhereFieldNull(string field, SelectLimiter limit = null) { return(SelectAllWhereFieldNull(new string[] { field }, limit)); }
/// <summary> /// Select *Column* Where *Fields* Equal *Values* /// </summary> /// <typeparam name="T"></typeparam> /// <param name="selectOptions"></param> /// <param name="conditionalOptions"></param> /// <param name="columns"></param> /// <param name="fields"></param> /// <param name="values"></param> /// <param name="convertTo"></param> /// <returns></returns> public static List <T> SelectFieldWhere(string column, string field, object value, WhereOption whereOption = WhereOption.EQUAL, SelectLimiter limit = null) { return(SelectFieldsWhere(new string[] { column }, new string[] { field }, new object[] { value }, whereOption, limit)); }
/// <summary> /// Select * Where *Field* Equals *Value* /// </summary> /// <param name="field"></param> /// <param name="values"></param> /// <param name="limit"></param> /// <returns></returns> public static List <T> SelectAllWhere(string field, object values, WhereOption whereOption = WhereOption.EQUAL, SelectLimiter limit = null) { return(Query.BuildSelect <T>(SelectOptions.ALL, ConditionalOptions.WHERE, null, new string[] { field }, new object[] { values }, null, whereOption, limit)); }
/// <summary> /// Select *Column* Where *Fields* Equal *Values* /// </summary> /// <typeparam name="T"></typeparam> /// <param name="selectOptions"></param> /// <param name="conditionalOptions"></param> /// <param name="columns"></param> /// <param name="fields"></param> /// <param name="values"></param> /// <param name="convertTo"></param> /// <returns></returns> public static List <T> SelectFieldWhere(string column, string[] fields, object[] values, WhereOption whereOption = WhereOption.EQUAL, SelectLimiter limit = null) { return(SelectFieldsWhere(new string[] { column }, fields, values, whereOption, limit)); }
/// <summary> /// Select *Columns* Where *Fields* Equal *Values* /// </summary> /// <typeparam name="T"></typeparam> /// <param name="selectOptions"></param> /// <param name="conditionalOptions"></param> /// <param name="columns"></param> /// <param name="fields"></param> /// <param name="values"></param> /// <param name="convertTo"></param> /// <returns></returns> public static List <T> SelectFieldsWhere(string[] columns, string[] fields, object[] values, WhereOption whereOption = WhereOption.EQUAL, SelectLimiter limit = null) { return(Query.BuildSelect <T>(SelectOptions.FIELDS, ConditionalOptions.WHERE, columns, fields, values, null, whereOption, limit)); }
/// <summary> /// Select *Columns* /// </summary> /// <typeparam name="T"></typeparam> /// <param name="selectOptions"></param> /// <param name="conditionalOptions"></param> /// <param name="columns"></param> /// <param name="convertTo"></param> /// <returns></returns> public static List <T> SelectFields(string columns, SelectLimiter limit = null) { return(SelectFields(new string[] { columns }, limit)); }
/// <summary> /// Select * /// </summary> /// <typeparam name="T"></typeparam> /// <param name="selectOptions"></param> /// <param name="conditionalOptions"></param> /// <param name="convertTo"></param> /// <returns></returns> public static List <T> SelectAll(SelectLimiter limit = null) { return(Query.BuildSelect <T>(SelectOptions.ALL, ConditionalOptions.NONE, null, null, null, null, WhereOption.NONE, limit)); }
/// <summary> /// Select Query Builder /// </summary> /// <typeparam name="T"></typeparam> /// <param name="selectOptions"></param> /// <param name="convertTo"></param> /// <param name="args"></param> /// <returns></returns> internal static List <T> BuildSelect <T>(SelectOptions selectOptions, ConditionalOptions conditionalOptions, string[] columns, string[] fields, object[] values, Type convertTo = null, WhereOption whereOption = WhereOption.EQUAL, SelectLimiter limit = null) { string query; if (selectOptions == SelectOptions.FIELDS) { query = $"SELECT `{string.Join("`, `", columns)}` FROM "; } else { query = $"SELECT * FROM "; } Type type = convertTo ?? typeof(T); // Table Name if (!(type.GetCustomAttributes(typeof(SqlTableAttribute), true).FirstOrDefault() is SqlTableAttribute table)) { Logger.Verbose($"SqlTableAttribute was not found on object type: {type}"); return(new List <T>()); } SqlObject sqlObject = new SqlObject { Table = table.TableName }; query += $"`{Configuration.MySqlDatabase}`.`{sqlObject.Table}`"; switch (conditionalOptions) { case ConditionalOptions.NONE: break; case ConditionalOptions.BY_ID: query += $" WHERE {Converter.GetPrimaryPropertyColumnName(type)} = @param1"; break; case ConditionalOptions.WHERE: query += $" WHERE "; int iter = 1; foreach (string fie in fields) { if (iter != 1) { query += " AND "; } switch (whereOption) { case WhereOption.EQUAL: query += $"`{fie}` = @param{iter}"; break; case WhereOption.NOTEQUAL: query += $"`{fie}` != @param{iter}"; break; case WhereOption.GREATERTHAN: query += $"`{fie}` > @param{iter}"; break; case WhereOption.LESSTHAN: query += $"`{fie}` < @param{iter}"; break; default: throw new ArgumentNullException("Invalid or Missing WhereOption"); } iter++; } break; case ConditionalOptions.WHERENULL: query += $" WHERE `{fields[0]}` IS NULL"; break; default: break; } if (limit != null) { query += limit.QueryData; } else { query += ";"; } sqlObject.CommandText = query; List <T> objects; switch (selectOptions) { case SelectOptions.ALL: switch (conditionalOptions) { case ConditionalOptions.NONE: objects = SqlFunc.Select <T>(sqlObject, null); break; case ConditionalOptions.BY_ID: objects = SqlFunc.Select <T>(sqlObject, null, values); break; case ConditionalOptions.WHERE: objects = SqlFunc.Select <T>(sqlObject, null, values); break; case ConditionalOptions.WHERENULL: objects = SqlFunc.Select <T>(sqlObject, null, values); break; default: Logger.Verbose("Conditional Select Error"); objects = new List <T>(); break; } break; case SelectOptions.FIELDS: switch (conditionalOptions) { case ConditionalOptions.NONE: objects = SqlFunc.Select <T>(sqlObject, columns); break; case ConditionalOptions.BY_ID: objects = SqlFunc.Select <T>(sqlObject, columns, values); break; case ConditionalOptions.WHERE: objects = SqlFunc.Select <T>(sqlObject, columns, values); break; case ConditionalOptions.WHERENULL: objects = SqlFunc.Select <T>(sqlObject, columns, values); break; default: Logger.Verbose("Conditional Select Error"); objects = new List <T>(); break; } break; default: Logger.Verbose("Select Type Error"); objects = new List <T>(); break; } foreach (T obj in objects) { foreach (PropertyInfo property in type.GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)) { object[] attributes = property.GetCustomAttributes(false); object column = attributes .FirstOrDefault(x => x.GetType() == typeof(SqlColumnAttribute)); if (column != null) { SqlColumnAttribute col = column as SqlColumnAttribute; // Should we be getting this derived property? If not, skip if (selectOptions == SelectOptions.FIELDS && !columns.Contains(col.ColumnName)) { continue; } if (col.IsTable) { List <object> data = BuildSelect <object>(SelectOptions.ALL, ConditionalOptions.WHERE, null, new string[] { col.AttachedTable }, new string[] { SqlFunc.ReflectPrimary(obj).ToString() }, property.PropertyType); if (data.Count > 0) { property.SetValue(obj, data[0]); } else { Logger.Verbose($"Dataset for {property.PropertyType} had no value..."); } } else if (property.PropertyType != typeof(byte[]) && typeof(IList).IsAssignableFrom(property.PropertyType)) { Type listOf = property.PropertyType.GetGenericArguments()[0]; property.SetValue(obj, Activator.CreateInstance(property.PropertyType)); foreach (object item in BuildSelect <object>(SelectOptions.ALL, ConditionalOptions.WHERE, null, new string[] { col.AttachedTable }, new string[] { SqlFunc.ReflectPrimary(obj).ToString() }, listOf)) { (property.GetValue(obj) as IList).Add(item); } } } } } return(objects); }