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