/// <summary>
/// Constructs the actual query
/// </summary>
/// <returns>Delete query</returns>
public string Build()
{
StringBuilder query = new StringBuilder();
if (_between)
{
var pk = SQLUtil.GetFields <T>().Single(f => f.Item2 == SQLUtil.GetFirstPrimaryKey <T>());
query.Append("DELETE FROM ");
query.Append(SQLUtil.GetTableName <T>());
query.Append(" WHERE ");
query.Append(pk.Item1);
query.Append(" BETWEEN ");
query.Append(_valuesBetweenDouble.Item1);
query.Append(" AND ");
query.Append(_valuesBetweenDouble.Item2);
query.Append(";");
}
else
{
query.Append("DELETE FROM ");
query.Append(SQLUtil.GetTableName <T>());
query.Append(" WHERE ");
query.Append(new SQLWhere <T>(_rows, true).Build());
query.Append(";");
}
query.Append(Environment.NewLine);
return(query.ToString());
}
/// <summary>
/// Constructs the actual query
/// </summary>
/// <returns>Single insert row (data)</returns>
public string Build()
{
if (NoData)
{
return("-- " + _headerComment + Environment.NewLine);
}
StringBuilder query = new StringBuilder();
if (_row.CommentOut)
{
query.Append("-- ");
}
query.Append("(");
foreach (object value in SQLUtil.GetFields <T>().Select(field => field.Item2.GetValue(_row.Data)))
{
if (value == null)
{
query.Append("UNKNOWN");
query.Append(SQLUtil.CommaSeparator);
}
else
{
Array arr = value as Array;
if (arr != null)
{
foreach (object v in arr)
{
if (v == null)
{
query.Append("UNKNOWN");
}
else
{
query.Append(SQLUtil.ToSQLValue(v));
}
query.Append(SQLUtil.CommaSeparator);
}
}
else
{
query.Append(SQLUtil.ToSQLValue(value));
query.Append(SQLUtil.CommaSeparator);
}
}
}
query.Remove(query.Length - SQLUtil.CommaSeparator.Length, SQLUtil.CommaSeparator.Length); // remove last ", "
query.Append("),");
if (!string.IsNullOrWhiteSpace(_row.Comment))
{
query.Append(" -- " + _row.Comment);
}
return(query.ToString());
}
public static RowList <T> Get <T>(RowList <T> rowList = null, string database = null)
where T : IDataModel, new()
{
// TODO: Add new config option "Verify data against DB"
if (!SQLConnector.Enabled)
{
return(null);
}
if (!SQLUtil.IsTableVisible <T>())
{
return(null);
}
var result = new RowList <T>();
using (var command = SQLConnector.CreateCommand(new SQLSelect <T>(rowList, database).Build()))
{
if (command == null)
{
return(null);
}
var fields = SQLUtil.GetFields <T>();
var fieldsCount = fields.Select(f => f.Item3.First().Count).Sum();
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var instance = (T)Activator.CreateInstance(typeof(T));
var values = GetValues(reader, fieldsCount);
var i = 0;
foreach (var field in fields)
{
SQLUtil.SetFieldValueByDB(instance, field, values, i);
i += field.Item3.First().Count;
}
result.Add(instance);
}
}
}
return(result);
}
public string Build()
{
string tableName = SQLUtil.GetTableName <T>();
var fields = SQLUtil.GetFields <T>();
StringBuilder fieldNames = new StringBuilder();
foreach (var field in fields)
{
fieldNames.Append(field.Item1);
fieldNames.Append(SQLUtil.CommaSeparator);
}
fieldNames.Remove(fieldNames.Length - 2, 2); // remove last ", "
if (_whereClause.HasConditions)
{
return($"SELECT {fieldNames} FROM {_database ?? Settings.TDBDatabase}.{tableName} WHERE {_whereClause.Build()}");
}
return($"SELECT {fieldNames} FROM {_database ?? Settings.TDBDatabase}.{tableName}");
}
/// <summary>
/// Constructs the actual query
/// </summary>
/// <returns>Insert query header</returns>
public string Build()
{
StringBuilder query = new StringBuilder();
query.Append("INSERT ");
query.Append(_ignore ? "IGNORE " : string.Empty);
query.Append("INTO ");
query.Append(SQLUtil.GetTableName <T>());
query.Append(" (");
foreach (var field in SQLUtil.GetFields <T>())
{
query.Append(field.Item1);
query.Append(SQLUtil.CommaSeparator);
}
query.Remove(query.Length - SQLUtil.CommaSeparator.Length, SQLUtil.CommaSeparator.Length); // remove last ", "
query.Append(")");
query.Append(" VALUES" + Environment.NewLine);
return(query.ToString());
}
public static RowList <T> Get <T>(RowList <T> rowList = null, string database = null)
where T : IDataModel, new()
{
// TODO: Add new config option "Verify data against DB"
if (!SQLConnector.Enabled)
{
return(null);
}
var result = new RowList <T>();
using (var reader = SQLConnector.ExecuteQuery(new SQLSelect <T>(rowList, database).Build()))
{
if (reader == null)
{
return(null);
}
var fields = SQLUtil.GetFields <T>();
var fieldsCount = fields.Select(f => f.Item3.First().Count).Sum();
while (reader.Read())
{
var instance = (T)Activator.CreateInstance(typeof(T));
var values = GetValues(reader, fieldsCount);
var i = 0;
foreach (var field in fields)
{
if (values[i] is DBNull && field.Item2.FieldType == typeof(string))
{
field.Item2.SetValue(instance, string.Empty);
}
else if (field.Item2.FieldType.BaseType == typeof(Enum))
{
field.Item2.SetValue(instance, Enum.Parse(field.Item2.FieldType, values[i].ToString()));
}
else if (field.Item2.FieldType.BaseType == typeof(Array))
{
var arr = Array.CreateInstance(field.Item2.FieldType.GetElementType(), field.Item3.First().Count);
for (var j = 0; j < arr.Length; j++)
{
var elemType = arr.GetType().GetElementType();
if (elemType.IsEnum)
{
arr.SetValue(Enum.Parse(elemType, values[i + j].ToString()), j);
}
else if (Nullable.GetUnderlyingType(elemType) != null) //is nullable
{
arr.SetValue(Convert.ChangeType(values[i + j], Nullable.GetUnderlyingType(elemType)), j);
}
else
{
arr.SetValue(Convert.ChangeType(values[i + j], elemType), j);
}
}
field.Item2.SetValue(instance, arr);
}
else if (field.Item2.FieldType == typeof(bool))
{
field.Item2.SetValue(instance, Convert.ToBoolean(values[i]));
}
else if (Nullable.GetUnderlyingType(field.Item2.FieldType) != null) // is nullable
{
var uType = Nullable.GetUnderlyingType(field.Item2.FieldType);
field.Item2.SetValue(instance,
uType.IsEnum
? Enum.Parse(uType, values[i].ToString())
: Convert.ChangeType(values[i], Nullable.GetUnderlyingType(field.Item2.FieldType)));
}
else
{
field.Item2.SetValue(instance, values[i]);
}
i += field.Item3.First().Count;
}
result.Add(instance);
}
}
return(result);
}
public string Build()
{
if (_conditions == null || _conditions.Count == 0)
{
return(string.Empty);
}
StringBuilder whereClause = new StringBuilder();
if (_onlyPrimaryKeys && _conditions.GetPrimaryKeyCount() == 1)
{
var field = SQLUtil.GetFields <T>().Single(f => f.Item2 == SQLUtil.GetFirstPrimaryKey <T>());
whereClause.Append(field.Item1);
if (_conditions.Count == 1)
{
whereClause.Append("=");
whereClause.Append(field.Item2.GetValue(_conditions.First().Data));
}
else
{
whereClause.Append(" IN (");
foreach (Row <T> condition in _conditions)
{
object value = field.Item2.GetValue(condition.Data);
whereClause.Append(SQLUtil.ToSQLValue(value));
if (!string.IsNullOrEmpty(condition.Comment))
{
whereClause.Append(" /*" + condition.Comment + "*/");
}
whereClause.Append(SQLUtil.CommaSeparator);
}
whereClause.Remove(whereClause.Length - SQLUtil.CommaSeparator.Length, SQLUtil.CommaSeparator.Length); // remove last ", "
whereClause.Append(")");
}
}
else
{
foreach (Row <T> condition in _conditions)
{
whereClause.Append("(");
foreach (var field in SQLUtil.GetFields <T>())
{
object value = field.Item2.GetValue(condition.Data);
if (value == null ||
(_onlyPrimaryKeys &&
field.Item3.Any(a => !a.IsPrimaryKey)))
{
continue;
}
whereClause.Append(field.Item1);
whereClause.Append("=");
whereClause.Append(SQLUtil.ToSQLValue(value));
whereClause.Append(" AND ");
}
whereClause.Remove(whereClause.Length - 5, 5); // remove last " AND "
whereClause.Append(")");
whereClause.Append(" OR ");
}
whereClause.Remove(whereClause.Length - 4, 4); // remove last " OR ";
}
return(whereClause.ToString());
}
/// <summary>
/// Constructs the actual query
/// </summary>
/// <returns>A single update query</returns>
public virtual string Build()
{
StringBuilder query = new StringBuilder();
if (CommentOut)
{
query.Append("-- ");
}
// Return empty if there are no values or where clause or no table name set
if (!WhereClause.HasConditions)
{
return(string.Empty);
}
query.Append("UPDATE ");
query.Append(SQLUtil.GetTableName <T>());
query.Append(" SET ");
bool hasValues = false;
foreach (var field in SQLUtil.GetFields <T>())
{
object value = field.Item2.GetValue(_value.Data);
Array arr = value as Array;
if (arr != null)
{
for (int i = 0; i < arr.Length; i++)
{
object v = arr.GetValue(i);
if (v == null)
{
continue;
}
query.Append(SQLUtil.AddBackQuotes(field.Item3.First().Name + (field.Item3.First().StartAtZero ? i : i + 1)));
query.Append("=");
query.Append(SQLUtil.ToSQLValue(v));
query.Append(SQLUtil.CommaSeparator);
}
continue;
}
if (value == null)
{
continue;
}
hasValues = true;
query.Append(field.Item1);
query.Append("=");
query.Append(SQLUtil.ToSQLValue(value));
query.Append(SQLUtil.CommaSeparator);
}
if (!hasValues)
{
return(string.Empty);
}
query.Remove(query.Length - SQLUtil.CommaSeparator.Length, SQLUtil.CommaSeparator.Length); // remove last ", "
query.Append(" WHERE ");
query.Append(WhereClause.Build());
query.Append(";");
if (!string.IsNullOrWhiteSpace(_value.Comment))
{
query.Append(" -- " + _value.Comment);
}
return(query.ToString());
}
public int GetPrimaryKeyCount()
{
return(SQLUtil.GetFields <T>().Count(f => f.Item3.Any(g => g.IsPrimaryKey)));
}