/// <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[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 (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()); }
/// <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) { value = ""; } Values[SQLUtil.AddBackQuotes(field)] = SQLUtil.ToSQLValue(value, isFlag, noQuotes); }
/// <summary> /// <para>Adds a value to this row</para> /// <remarks>null value will be 0</remarks> /// </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) { value = 0; } _values.Add(SQLUtil.ToSQLValue(value, isFlag, noQuotes).ToString()); FieldNames.Add(field); }
/// <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()); }
/// <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 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))); } }