/// <summary>
/// Constructs the actual query
/// </summary>
/// <returns>Full insert AND delete queries</returns>
public string Build()
{
// If we only have rows with comment, do not print any query
if (Rows.All(row => row.NoData)) // still true if row count = 0
{
return("-- " + SQLUtil.AddBackQuotes(Table) + " has empty data." + Environment.NewLine);
}
var query = new StringBuilder();
query.Append(Delete); // Can be empty
query.Append(InsertHeader);
var count = 0;
foreach (var row in Rows)
{
if (count >= MaxRowsPerInsert && !_deleteDuplicates)
{
query.ReplaceLast(',', ';');
query.Append(InsertHeader);
count = 0;
}
query.Append(row.Build());
count++;
}
query.Append(Environment.NewLine);
// This is easier to implement that comparing raw objects in each row
// and certainly faster. Imagine comparing 1k rows of <string, int, int, emote, YouGotIt>
if (_deleteDuplicates)
{
var str = String.Join("\n", query.ToString().Split('\n').Distinct()); // Do not use Enviroment.NewLine
query.Clear();
query.Append(str);
}
query.ReplaceLast(',', ';');
return(query.ToString());
}
/// <summary>
/// Constructs the actual query
/// </summary>
/// <returns>Full insert AND delete queries</returns>
public string Build()
{
// If we only have rows with comment, do not print any query
if (Rows.All(row => row.NoData)) // still true if row count = 0
{
return("-- " + SQLUtil.AddBackQuotes(Table) + " has empty data." + Environment.NewLine);
}
var query = new StringBuilder();
query.Append(Delete); // Can be empty
query.Append(InsertHeader);
var count = 0;
HashSet <string> rowStrings = new HashSet <string>();
foreach (var row in Rows)
{
if (count >= MaxRowsPerInsert && !_deleteDuplicates)
{
query.ReplaceLast(',', ';');
query.Append(Environment.NewLine);
query.Append(InsertHeader);
count = 0;
}
string rowString = row.Build();
if (_deleteDuplicates && !rowStrings.Add(rowString))
{
continue;
}
query.Append(rowString);
count++;
}
query.Append(Environment.NewLine);
query.ReplaceLast(',', ';');
return(query.ToString());
}
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 _databaseFields)
{
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());
}
/// <summary>
/// String representation of the field or group of fields
/// </summary>
/// <returns>name</returns>
public override string ToString()
{
if (Name == null)
{
return(string.Empty);
}
if (!_multipleFields)
{
return(SQLUtil.AddBackQuotes(Name));
}
StringBuilder result = new StringBuilder();
for (int i = 1; i <= Count; i++)
{
result.Append(SQLUtil.AddBackQuotes(Name + (StartAtZero ? i - 1 : i)));
if (i != Count)
{
result.Append(SQLUtil.CommaSeparator);
}
}
return(result.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 _databaseFields)
{
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, noQuotes: field.Item3.Any(a => a.NoQuotes)));
query.Append(SQLUtil.CommaSeparator);
hasValues = true;
}
continue;
}
if (value == null)
{
continue;
}
if (field.Item2.Name != "VerifiedBuild" || !Settings.SkipOnlyVerifiedBuildUpdateRows)
{
hasValues = true;
}
query.Append(field.Item1);
query.Append("=");
query.Append(SQLUtil.ToSQLValue(value, noQuotes: field.Item3.Any(a => a.NoQuotes)));
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 string Build()
{
if (_conditions == null || _conditions.Count == 0)
{
return(string.Empty);
}
StringBuilder whereClause = new StringBuilder();
if (_onlyPrimaryKeys && _conditions.GetPrimaryKeyCount() == 1)
{
var field = _databaseFields.Single(f => f.Item2 == _primaryKeyReflectionField);
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
{
if (_conditions.Count > 1)
{
var result = BuildDistinct();
if (result != null)
{
return(result);
}
// Fallback to standard AND where clauses if the distinct one failed
}
foreach (Row <T> condition in _conditions)
{
whereClause.Append("(");
foreach (var field in _databaseFields)
{
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());
}
private string BuildDistinct()
{
// 1. Get a list of all conditions as list of field/value pairs
List <WhereCondition> whereConditions = new List <WhereCondition>();
foreach (Row <T> condition in _conditions)
{
var whereCondition = new WhereCondition();
foreach (var field in _databaseFields)
{
object value = field.Item2.GetValue(condition.Data);
if (value == null ||
(_onlyPrimaryKeys &&
field.Item3.Any(a => !a.IsPrimaryKey)))
{
continue;
}
whereCondition.Add(field.Item1, SQLUtil.ToSQLValue(value));
}
if (!whereCondition.IsEmpty)
{
whereConditions.Add(whereCondition);
}
}
// 2. Check if all conditions share the same fields
bool allShareSameFields = true;
var baselineConditionFields = whereConditions.First().FieldValuePairs.Keys;
foreach (var condition in whereConditions)
{
if (!condition.FieldValuePairs.Keys.SequenceEqual(baselineConditionFields))
{
allShareSameFields = false;
}
}
if (!allShareSameFields)
{
return(null);
}
// ToDo: support the case with more than 2 fields
if (baselineConditionFields.Count != 2)
{
return(null);
}
// 3. Get a distinct of values for each field
var fieldsWithDistinctValues = (from condition in whereConditions
from fieldValuePair in condition.FieldValuePairs
group fieldValuePair by fieldValuePair.Key into byField
select new { Field = byField.Key, Values = byField.ToList().Select(pair => pair.Value).Distinct().ToList() }
).OrderBy(field => field.Values.Count()).ToList();
// 4. Get the field with lowest different values
var fieldWithLowestDifferentValues = fieldsWithDistinctValues.First().Field;
// ToDo: support the case with more than 2 fields
var secondField = fieldsWithDistinctValues.Skip(1).First().Field;
// 5. Group conditions by the field with lowest different values
var conditionsByFieldWithLowestDifferentValues = whereConditions.GroupBy(cond => cond.FieldValuePairs[fieldWithLowestDifferentValues])
.ToDictionary(g => g.Key, g => g.ToList());
// 6. Build the where clause using the field with lowest different values for the first condition
StringBuilder whereClause = new StringBuilder();
foreach (var pair in conditionsByFieldWithLowestDifferentValues)
{
whereClause.Append("(");
whereClause.Append(fieldWithLowestDifferentValues);
whereClause.Append("=");
whereClause.Append(pair.Key);
whereClause.Append(" AND ");
whereClause.Append(secondField);
if (pair.Value.Select(cond => cond.FieldValuePairs[secondField]).Count() == 1)
{
whereClause.Append("=");
whereClause.Append(pair.Value.Select(cond => cond.FieldValuePairs[secondField]).First());
}
else
{
whereClause.Append(" IN (");
whereClause.Append(string.Join(',', pair.Value.Select(cond => cond.FieldValuePairs[secondField])));
whereClause.Append(")");
}
whereClause.Append(")");
whereClause.Append(" OR ");
}
whereClause.Length -= 4; // remove last " OR ";
return(whereClause.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);
}
/// <summary>
/// Constructs the actual query
/// </summary>
/// <returns>Delete query</returns>
public string Build()
{
var query = new StringBuilder();
if (_between)
{
query.Append("DELETE FROM ");
query.Append(SQLUtil.AddBackQuotes(Table));
query.Append(" WHERE ");
switch (_primaryKeyNumber)
{
case 2:
query.Append(SQLUtil.AddBackQuotes(PrimaryKey));
query.Append(" BETWEEN ");
query.Append(ValuesBetweenDouble.Item1);
query.Append(" AND ");
query.Append(ValuesBetweenDouble.Item2);
query.Append(";");
break;
case 3:
query.Append(SQLUtil.AddBackQuotes(PrimaryKey));
query.Append(" BETWEEN ");
query.Append(ValuesBetweenTriple.Item1);
query.Append(" AND ");
query.Append(ValuesBetweenTriple.Item2);
query.Append(" AND ");
query.Append(ValuesBetweenTriple.Item3);
query.Append(";");
break;
}
}
else
{
switch (_primaryKeyNumber)
{
case 2:
{
var counter = 0;
var rowsPerDelete = 0;
query.Append("DELETE FROM ");
query.Append(SQLUtil.AddBackQuotes(Table));
query.Append(" WHERE ");
foreach (var tuple in ValuesDouble)
{
counter++;
rowsPerDelete++;
query.Append("(");
query.Append(SQLUtil.AddBackQuotes(PrimaryKeyDouble.Item1));
query.Append("=");
query.Append(tuple.Item1);
query.Append(" AND ");
query.Append(SQLUtil.AddBackQuotes(PrimaryKeyDouble.Item2));
query.Append("=");
query.Append(tuple.Item2);
query.Append(")");
// Append an OR if not end of items
if (rowsPerDelete < 25 && ValuesDouble.Count != counter)
{
query.Append(" OR ");
}
else if (rowsPerDelete == 25)
{
rowsPerDelete = 0;
query.Append(";");
if (ValuesDouble.Count != counter)
{
query.Append(Environment.NewLine);
query.Append("DELETE FROM ");
query.Append(SQLUtil.AddBackQuotes(Table));
query.Append(" WHERE ");
}
}
else if (ValuesDouble.Count == counter)
{
query.Append(";");
}
}
break;
}
case 3:
{
var counter = 0;
var rowsPerDelete = 0;
query.Append("DELETE FROM ");
query.Append(SQLUtil.AddBackQuotes(Table));
query.Append(" WHERE ");
foreach (var tuple in ValuesTriple)
{
counter++;
rowsPerDelete++;
query.Append("(");
query.Append(SQLUtil.AddBackQuotes(PrimaryKeyTriple.Item1));
query.Append("=");
query.Append(tuple.Item1);
query.Append(" AND ");
query.Append(SQLUtil.AddBackQuotes(PrimaryKeyTriple.Item2));
query.Append("=");
query.Append(tuple.Item2);
query.Append(" AND ");
query.Append(SQLUtil.AddBackQuotes(PrimaryKeyTriple.Item3));
query.Append("=");
query.Append(tuple.Item3);
query.Append(")");
// Append an OR if not end of items
if (rowsPerDelete < 25 && ValuesTriple.Count != counter)
{
query.Append(" OR ");
}
else if (rowsPerDelete == 25)
{
rowsPerDelete = 0;
query.Append(";");
if (ValuesTriple.Count != counter)
{
query.Append(Environment.NewLine);
query.Append("DELETE FROM ");
query.Append(SQLUtil.AddBackQuotes(Table));
query.Append(" WHERE ");
}
}
else if (ValuesTriple.Count == counter)
{
query.Append(";");
}
}
break;
}
default:
{
query.Append("DELETE FROM ");
query.Append(SQLUtil.AddBackQuotes(Table));
query.Append(" WHERE ");
query.Append(SQLUtil.AddBackQuotes(PrimaryKey));
query.Append(Values.Count == 1 ? "=" : " IN (");
var counter = 0;
var rowsPerDelete = 0;
foreach (var entry in Values)
{
counter++;
rowsPerDelete++;
query.Append(entry);
// Append comma if not end of items
if (Values.Count != counter)
{
query.Append(SQLUtil.CommaSeparator);
}
else if (Values.Count != 1 && Values.Count != counter)
{
query.Append(")");
}
else if (rowsPerDelete == 25)
{
rowsPerDelete = 0;
query.Append(";");
if (Values.Count != counter)
{
query.Append(Environment.NewLine);
query.Append("DELETE FROM ");
query.Append(SQLUtil.AddBackQuotes(Table));
query.Append(" WHERE ");
query.Append(SQLUtil.AddBackQuotes(PrimaryKey));
query.Append(Values.Count == 1 ? "=" : " IN (");
}
}
else if (Values.Count == counter)
{
if (Values.Count != 1)
{
query.Append(")");
}
query.Append(";");
}
}
break;
}
}
}
query.Append(Environment.NewLine);
return(query.ToString());
}
/// <summary>
/// Adds a field-value pair to be updated. If value is equal to defaultValue, value will NOT be added to this update row
/// </summary>
/// <param name="field">The field name associated with the value</param>
/// <param name="value">Any value (string, number, enum, ...)</param>
/// <param name="defaultValue">Default value (usually defined in database structure)</param>
/// <param name="isFlag">If set to true the value, "0x" will be append to value</param>
/// <param name="noQuotes">If value is a string and this is set to true, value will not be 'quoted' (SQL variables)</param>
public void AddValue <T>(string field, T value, T defaultValue, bool isFlag = false, bool noQuotes = false)
{
// T used because it is compile time safe. We know that value and defaultValue got the same type
// ReSharper disable CompareNonConstrainedGenericWithNull
if (value == null)
{
// ReSharper restore CompareNonConstrainedGenericWithNull
return;
}
if (value is float || value is double)
{
if (Math.Abs(Convert.ToDouble(value) - Convert.ToDouble(defaultValue)) < 0.000001)
{
return;
}
}
if (value.Equals(defaultValue))
{
return;
}
_values.Add(new KeyValuePair <string, object>(SQLUtil.AddBackQuotes(field), SQLUtil.ToSQLValue(value, isFlag, noQuotes)));
}
/// <summary>
/// Adds to this row what will be updated
/// </summary>
/// <param name="field">The field name associated with the value</param>
/// <param name="value">The value used in the where clause</param>
public void AddWhere(string field, object value)
{
WhereClause.Add(new KeyValuePair <string, object>(SQLUtil.AddBackQuotes(field), value));
}
/// <summary>
/// Constructs the actual query
/// </summary>
/// <returns>Delete query</returns>
public string Build()
{
var query = new StringBuilder();
query.Append("DELETE FROM ");
query.Append(SQLUtil.AddBackQuotes(Table));
query.Append(" WHERE ");
if (_between)
{
query.Append(SQLUtil.AddBackQuotes(PrimaryKey));
query.Append(" BETWEEN ");
query.Append(ValuesBetween.Item1);
query.Append(" AND ");
query.Append(ValuesBetween.Item2);
query.Append(";");
}
else
{
if (_double)
{
var counter = 0;
foreach (var tuple in ValuesDouble)
{
counter++;
query.Append("(");
query.Append(SQLUtil.AddBackQuotes(PrimaryKeys.Item1));
query.Append("=");
query.Append(tuple.Item1);
query.Append(" AND ");
query.Append(SQLUtil.AddBackQuotes(PrimaryKeys.Item2));
query.Append("=");
query.Append(tuple.Item2);
query.Append(")");
// Append an OR if not end of items
if (ValuesDouble.Count != counter)
{
query.Append(" OR ");
}
}
query.Append(";");
}
else
{
query.Append(SQLUtil.AddBackQuotes(PrimaryKey));
query.Append(Values.Count == 1 ? "=" : " IN (");
var counter = 0;
foreach (var entry in Values)
{
counter++;
query.Append(entry);
// Append comma if not end of items
if (Values.Count != counter)
{
query.Append(SQLUtil.CommaSeparator);
}
else if (Values.Count != 1)
{
query.Append(")");
}
}
query.Append(";");
}
}
query.Append(Environment.NewLine);
return(query.ToString());
}
/// <summary>
/// Gets from `world` database a dictionary of the given struct/class.
/// Structs fields type must match the type of the DB columns.
/// DB columns names are set by using DBFieldNameAttribute.
/// </summary>
/// <typeparam name="T">Type of the elements of the list of entries (usually uint)</typeparam>
/// <typeparam name="TK">Type of the struct</typeparam>
/// <param name="entries">List of entries to select from DB</param>
/// <param name="primaryKeyName"></param>
/// <param name="database"></param>
/// <returns>Dictionary of structs of type TK</returns>
public static StoreDictionary <T, TK> GetDict <T, TK>(List <T> entries, string primaryKeyName = "entry", string database = null)
{
if (entries.Count == 0)
{
return(null);
}
// TODO: Add new config option "Verify data against DB"
if (!SQLConnector.Enabled)
{
return(null);
}
var tableAttrs = (DBTableNameAttribute[])typeof(TK).GetCustomAttributes(typeof(DBTableNameAttribute), false);
if (tableAttrs.Length <= 0)
{
return(null);
}
var tableName = tableAttrs[0].Name;
var fields = Utilities.GetFieldsAndAttribute <TK, DBFieldNameAttribute>();
fields.RemoveAll(field => field.Item2.Name == null);
var fieldCount = 1;
var fieldNames = new StringBuilder();
fieldNames.Append(SQLUtil.AddBackQuotes(primaryKeyName) + ",");
foreach (var field in fields)
{
fieldNames.Append(field.Item2);
fieldNames.Append(",");
fieldCount += field.Item2.Count;
}
var query = string.Format("SELECT {0} FROM {1}.{2} WHERE {3} IN ({4})",
fieldNames.ToString().TrimEnd(','), database ?? Settings.TDBDatabase, tableName, primaryKeyName, String.Join(",", entries));
var dict = new Dictionary <T, TK>(entries.Count);
using (var reader = SQLConnector.ExecuteQuery(query))
{
if (reader == null)
{
return(null);
}
while (reader.Read())
{
var instance = (TK)Activator.CreateInstance(typeof(TK));
var values = new object[fieldCount];
var count = reader.GetValues(values);
if (count != fieldCount)
{
throw new InvalidConstraintException(
"Number of fields from DB is different of the number of fields with DBFieldName attribute");
}
var i = 1;
foreach (var field in fields)
{
if (values[i] is DBNull && field.Item1.FieldType == typeof(string))
{
field.Item1.SetValueDirect(__makeref(instance), string.Empty);
}
else if (field.Item1.FieldType.BaseType == typeof(Enum))
{
field.Item1.SetValueDirect(__makeref(instance), Enum.Parse(field.Item1.FieldType, values[i].ToString()));
}
else if (field.Item1.FieldType.BaseType == typeof(Array))
{
var arr = Array.CreateInstance(field.Item1.FieldType.GetElementType(), field.Item2.Count);
for (var j = 0; j < arr.Length; j++)
{
var elemType = arr.GetType().GetElementType();
var val = elemType.IsEnum ?
Enum.Parse(elemType, values[i + j].ToString()) :
Convert.ChangeType(values[i + j], elemType);
arr.SetValue(val, j);
}
field.Item1.SetValueDirect(__makeref(instance), arr);
}
else if (field.Item1.FieldType == typeof(bool))
{
field.Item1.SetValueDirect(__makeref(instance), Convert.ToBoolean(values[i]));
}
else
{
field.Item1.SetValueDirect(__makeref(instance), values[i]);
}
i += field.Item2.Count;
}
T key = (T)values[0];
if (!dict.ContainsKey(key))
{
dict.Add(key, instance);
}
}
}
return(new StoreDictionary <T, TK>(dict));
}
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>
/// Adds a field-value pair to be updated
/// </summary>
/// <param name="field">The field name associated with the value</param>
/// <param name="value">Any value (string, number, enum, ...)</param>
/// <param name="isFlag">If set to true the value, "0x" will be append to value</param>
/// <param name="noQuotes">If value is a string and this is set to true, value will not be 'quoted' (SQL variables)</param>
public void AddValue(string field, object value, bool isFlag = false, bool noQuotes = false)
{
if (value != null)
{
_values.Add(new KeyValuePair <string, object>(SQLUtil.AddBackQuotes(field), SQLUtil.ToSQLValue(value, isFlag, noQuotes)));
}
}
/// <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 (var field in _databaseFields)
{
object value = field.Item2.GetValue(_row.Data);
if (value == null)
{
if (field.Item3.Any(a => a.Nullable))
{
query.Append("NULL");
query.Append(SQLUtil.CommaSeparator);
}
else
{
query.Append("UNKNOWN");
query.Append(SQLUtil.CommaSeparator);
}
}
else
{
if (value is Blob blob)
{
query.Append(SQLUtil.ToSQLValue(blob));
query.Append(SQLUtil.CommaSeparator);
}
else if (value is Array arr)
{
foreach (object v in arr)
{
if (v == null)
{
if (field.Item3.Any(a => a.Nullable))
{
query.Append("NULL");
}
else
{
query.Append("UNKNOWN");
}
}
else
{
query.Append(SQLUtil.ToSQLValue(v, noQuotes: field.Item3.Any(a => a.NoQuotes)));
}
query.Append(SQLUtil.CommaSeparator);
}
}
else
{
query.Append(SQLUtil.ToSQLValue(value, noQuotes: field.Item3.Any(a => a.NoQuotes == true)));
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 int GetPrimaryKeyCount()
{
return(SQLUtil.GetFields <T>().Count(f => f.Item3.Any(g => g.IsPrimaryKey)));
}
