/// <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))); }