/// <summary>
/// Alter Table with new Column
/// </summary>
/// <param name="table"></param>
/// <param name="col"></param>
/// <param name="sqlObject"></param>
/// <returns></returns>
internal static bool Table_Alter_Add_Column(string table, string col, SqlObject sqlObject)
{
SqlColumnAttribute sqlColumnAttrib = null;
foreach (PropertyInfo property in Converter.GetPropertiesWithSqlColumnAttribute(sqlObject.AttachedObject.GetType()))
{
if (property != null)
{
sqlColumnAttrib = (property.GetCustomAttributes(false).FirstOrDefault(x => x.GetType() == typeof(SqlColumnAttribute) && (x as SqlColumnAttribute).ColumnName == col) as SqlColumnAttribute);
if (sqlColumnAttrib != null)
{
break;
}
}
}
Logger.Verbose($"Alter Table: {table}, Add Column {col}");
using (MySqlConnection sqlConnection = new MySqlConnection(ConnectionString))
{
sqlConnection.Open();
using (MySqlCommand Command = sqlConnection.CreateCommand())
{
Command.CommandText = $"ALTER TABLE `{table}` ADD COLUMN `{col}` {sqlColumnAttrib.PropertyType}{((sqlColumnAttrib.AllowNull) ? "" : " NOT NULL")};";
Command.ExecuteNonQuery();
if (Column_Exists(table, col))
{
return(true);
}
return(false);
}
}
}
/// <summary>
/// Convert C# Object into readable data for MySqlHandler to manipulate, specifically for creating a table
/// </summary>
/// <param name="sqlObject"></param>
/// <returns></returns>
internal static string Convert(SqlObject sqlObject)
{
string query = $"CREATE TABLE `{sqlObject.Table}` (";
Type type = Converter.GetTypesWithSqlTableAttributeByName(sqlObject.Table);
if (type == null)
{
throw new Exception("Type is null");
}
SqlColumnAttribute sqlPrimaryColumn = null;
foreach (PropertyInfo property in Converter.GetPropertiesWithSqlColumnAttribute(type))
{
if (property != null)
{
SqlColumnAttribute sqlColumn = (property.GetCustomAttributes(false).FirstOrDefault(x => x.GetType() == typeof(SqlColumnAttribute)) as SqlColumnAttribute);
if (sqlColumn.Primary)
{
sqlPrimaryColumn = sqlColumn;
}
if (!sqlColumn.IsTable && sqlColumn.ColumnName != null)
{
query += $"`{sqlColumn.ColumnName}` {sqlColumn.PropertyType} {((sqlColumn.AllowNull) ? "" : "NOT NULL")}{((sqlColumn.Primary ? " AUTO_INCREMENT," : ","))}";
}
}
}
if (sqlPrimaryColumn != null)
{
query += $"PRIMARY KEY (`{sqlPrimaryColumn.ColumnName}`) );";
}
return(query);
}
/// <summary>
/// Convert C# object to readable data for MySqlHandler to manipulate
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="obj"></param>
/// <returns></returns>
internal static List <SqlObject> Convert <T>(T obj)
{
SqlObject sqlObject = new SqlObject(obj);
List <SqlObject> SqlObjects = new List <SqlObject>()
{
sqlObject
};
Type type = obj.GetType();
// Table Name
if (!(type.GetCustomAttributes(typeof(SqlTableAttribute), true).FirstOrDefault() is SqlTableAttribute table))
{
Exception exception = new Exception($"Table is null on Type {type}");
throw exception;
}
sqlObject.Table = table.TableName;
// Column Names & Values
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 /*&& property.GetValue(obj) != null*/)
{
SqlColumnAttribute col = column as SqlColumnAttribute;
if (col.IsTable)
{
SqlObjects.AddRange(Convert(property.GetValue(obj)));
}
else if (!col.Primary || (col.Primary && property.GetValue(obj) != null))
{
if (property.PropertyType != typeof(byte[]) && property.GetValue(obj) is IList enumerable)
{
foreach (object listObj in (property.GetValue(obj) as IList))
{
SqlObjects.AddRange(Convert(listObj));
}
}
else
{
Type valType = property.PropertyType;
object val = null;
if (valType.IsEnum)
{
val = System.Convert.ToInt32(property.GetValue(obj));
}
else if (valType == typeof(bool))
{
val = System.Convert.ToInt32(property.GetValue(obj));
}
else
{
val = property.GetValue(obj);
}
SqlColumn sqlItem = new SqlColumn(col.ColumnName, RelevantType(col.SqlVarType), val, col.Primary, property.Name);
sqlObject.Items.Add(sqlItem);
}
}
}
}
return(SqlObjects);
}
/// <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);
}
