/// <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());
        }
Exemple #9
0
        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());
            }
Exemple #14
0
        /// <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));
        }
Exemple #15
0
        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());
        }
Exemple #18
0
 public int GetPrimaryKeyCount()
 {
     return(SQLUtil.GetFields <T>().Count(f => f.Item3.Any(g => g.IsPrimaryKey)));
 }