/// <summary>
        /// Adds or sets a value for a specific column.
        /// </summary>
        /// <param name="column">The column that is going to hold the value.</param>
        /// <param name="value">The value to be inserted.</param>
        public BuiltValueList <T> AddValueFor(string column, string value)
        {
            if (_columnsWithValues.All(col => col.Column != column))
            {
                throw new Exception($"This BuiltInsertValue does not contain a column named \"{column}\"");
            }

            if (SqlTableHelper.GetColumnNames <T>().All(columnName => columnName != column))
            {
                throw new Exception($"Table \"{typeof(T).FullName}\" does not contain a column named \"{column}\"");
            }

            if (ContainsColumn(column))
            {
                var columnWithValue = _columnsWithValues.Single(col => col.Column == column);
                _columnsWithValues.Remove(columnWithValue);
                columnWithValue.Value = value;
                _columnsWithValues.Add(columnWithValue);
            }
            else
            {
                _columnsWithValues.Add(new ColumnWithValue
                {
                    Column = column,
                    Value  = value
                });
            }

            return(this);
        }
 /// <summary>
 /// Checks if the provided column is NULL.
 /// </summary>
 /// <param name="table"></param>
 /// <param name="column"></param>
 public ConditionExpression(Type table, string column)
 {
     Type          = ConditionExpressionType.IsNull;
     LeftTable     = table;
     LeftTableName = SqlTableHelper.GetTableName(table);
     LeftColumn    = column;
 }
Exemple #3
0
        /// <summary>
        /// Creates an UPDATE command from the provided connection using DbParameters.
        /// </summary>
        /// <param name="command"></param>
        public DbCommand GenerateCommand(DbConnection connection)
        {
            if (_newColumnValues.Count == 0)
            {
                throw new Exception("Can't update table without columns to be updated.");
            }
            var           command = connection.CreateCommand();
            StringBuilder sb      = new StringBuilder($"UPDATE {SqlTableHelper.GetTableName<T>()} SET ");

            for (int i = 0; i < _newColumnValues.Count; i++)
            {
                var colVal    = _newColumnValues[i];
                var param     = command.CreateParameter();
                var paramName = Util.GetUniqueParameterName();
                param.ParameterName = paramName;
                param.Value         = colVal.Value;
                param.DbType        = colVal.ValueType;
                command.Parameters.Add(param);

                sb.Append($"{Util.FormatSQL(colVal.Column)}={ProviderSpecific.ParameterPrefix}{paramName}");
                if (i < _newColumnValues.Count - 1)
                {
                    sb.Append(", ");
                }
            }
            command.CommandText += sb.ToString();
            _condition?.GenerateCommand(command);
            return(command);
        }
Exemple #4
0
 /// <summary>
 /// Creates a CREATE command from the table class template. Uses the first int column as primary key.
 /// </summary>
 public BuiltCreateCommand()
 {
     _tableName = SqlTableHelper.GetTableName <T>();
     _columns   = SqlTableHelper.GetColumnAttributes <T>();
     if (_columns.Count == 0)
     {
         throw new Exception("Can't create empty table.");
     }
 }
 public ConditionExpression(Type leftTable, string leftColumn, string comparisonOperator, string value, DbType valueType)
 {
     Type               = ConditionExpressionType.CompareToValue;
     LeftTable          = leftTable;
     LeftTableName      = SqlTableHelper.GetTableName(leftTable);
     LeftColumn         = leftColumn;
     ComparisonOperator = comparisonOperator;
     Value              = value;
     ValueType          = valueType;
 }
 public ConditionExpression(Type leftTable, string leftColumn, string comparisonOperator, Type rightTable, string rightColumn)
 {
     Type               = ConditionExpressionType.CompareToColumn;
     LeftTable          = leftTable;
     LeftTableName      = SqlTableHelper.GetTableName(leftTable);
     LeftColumn         = leftColumn;
     ComparisonOperator = comparisonOperator;
     RightTable         = rightTable;
     RightTableName     = SqlTableHelper.GetTableName(rightTable);
     RightColumn        = rightColumn;
 }
Exemple #7
0
 /// <summary>
 /// Add all columns from the specified table to be selected.
 /// </summary>
 /// <typeparam name="T">The table containing the columns.</typeparam>
 public BuiltSelectCommand AddColumns <T>()
 {
     foreach (string column in SqlTableHelper.GetColumnNames <T>())
     {
         string fullyQualified = $"{Util.FormatSQL(SqlTableHelper.GetTableName<T>(), column)}";
         if (!_selectedColumns.Contains(fullyQualified))
         {
             _selectedColumns.Add(fullyQualified);
         }
     }
     return(this);
 }
Exemple #8
0
        /// <summary>
        /// Adds a table to used in the FROM clause.
        /// </summary>
        /// <param name="tableType">The table to be added.</param>
        /// <param name="selectAllColumns">Whether all columns from this table should be added to the selection.</param>
        public BuiltSelectCommand AddTable(Type tableType, bool selectAllColumns = true)
        {
            string tableName = SqlTableHelper.GetTableName(tableType);

            _fromTables.Add(tableName);

            if (selectAllColumns)
            {
                AddColumns(tableType, SqlTableHelper.GetColumnNames(tableType).ToArray());
            }

            return(this);
        }
 /// <summary>
 /// Creates a new value list using all columns from the table.
 /// </summary>
 public BuiltValueList()
 {
     _columnAttributes = SqlTableHelper.GetColumnAttributes <T>();
     foreach (var column in _columnAttributes)
     {
         _columnsWithValues.Add(new ColumnWithValue
         {
             Column    = column.ColumnName,
             Value     = "",
             ValueType = column.Type
         });
     }
 }
        public BuiltInsertCommand <T> AddItem(T item)
        {
            var value = new BuiltValueList <T>(_columns);

            foreach (var p2c in SqlTableHelper.PropertiesToColumnNames <T>())
            {
                if (!_columns.Contains(p2c.Value))
                {
                    continue;
                }

                value.AddValueFor(p2c.Value, p2c.Key.GetValue(item, null).ToString());
            }
            return(this);
        }
Exemple #11
0
        /// <summary>
        /// Adds a JOIN to this BuiltSelectCommand.
        /// </summary>
        /// <param name="toJoin">The new table of this join.</param>
        /// <param name="on">The existing table to be used for comparison.</param>
        /// <param name="toJoinColumn">The column to use from the new table.</param>
        /// <param name="onColumn">The column to use from the existing table.</param>
        public BuiltSelectCommand Join(Type toJoin, Type on, string toJoinColumn, string onColumn)
        {
            if (!SqlTableHelper.ContainsColumn(toJoin, toJoinColumn) ||
                !SqlTableHelper.ContainsColumn(on, onColumn))
            {
                throw new Exception("The specified tables do not contain the specified columns.");
            }

            _joins.Add(new SqlJoin
            {
                FirstTable   = SqlTableHelper.GetTableName(on),
                SecondTable  = SqlTableHelper.GetTableName(toJoin),
                FirstColumn  = onColumn,
                SecondColumn = toJoinColumn
            });
            return(this);
        }
Exemple #12
0
        /// <summary>
        /// Add a column to be sorted.
        /// </summary>
        /// <typeparam name="T">The table that the column resides in.</typeparam>
        /// <param name="column">The column to be used for sorting.</param>
        /// <param name="mode">The sorting mode to be used for this column.</param>
        public BuiltSqlSort SortBy <T>(string column, SqlSortMode mode)
        {
            switch (mode)
            {
            case SqlSortMode.ASCENDING:
                _sortingParametersAscending.Add($"{Util.FormatSQL(SqlTableHelper.GetTableName<T>(), column)}");
                break;

            case SqlSortMode.DESCENDING:
                _sortingParametersDescending.Add($"{Util.FormatSQL(SqlTableHelper.GetTableName<T>(), column)}");
                break;

            default:
                throw new ArgumentOutOfRangeException(nameof(mode), mode, null);
            }

            return(this);
        }
Exemple #13
0
        /// <summary>
        /// Add columns to be selected.
        /// </summary>
        /// <param name="tableType">The table containing the columns.</param>
        /// <param name="columns">The columns to be selected.</param>
        public BuiltSelectCommand AddColumns(Type tableType, params string[] columns)
        {
            string tableName = SqlTableHelper.GetTableName(tableType);

            if (!SqlTableHelper.ContainsAllColumns(tableType, columns))
            {
                throw new Exception($"Table \"{SqlTableHelper.GetTableName(tableType)}\" does not contain all columns specified.");
            }

            foreach (string column in columns)
            {
                string fullyQualified = $"{Util.FormatSQL(tableName, column)}";
                if (!_selectedColumns.Contains(fullyQualified))
                {
                    _selectedColumns.Add(fullyQualified);
                }
            }
            return(this);
        }
        /// <summary>
        /// Creates a new value list using the specified columns.
        /// </summary>
        public BuiltValueList(List <string> columns)
        {
            _columnAttributes = SqlTableHelper.GetColumnAttributes <T>();
            foreach (var col in columns)
            {
                SqlColumn columnAttribute = _columnAttributes.FirstOrDefault(attr => attr.ColumnName == col);
                if (columnAttribute == null)
                {
                    throw new Exception($"Table \"{typeof(T).FullName}\" does not contain a column named \"{col}\"");
                }

                _columnsWithValues.Add(new ColumnWithValue
                {
                    Column    = col,
                    Value     = "",
                    ValueType = columnAttribute.Type
                });
            }
        }
Exemple #15
0
        /// <summary>
        /// Generates the actual UPDATE command string.
        /// </summary>
        public string GenerateStatement()
        {
            if (_newColumnValues.Count == 0)
            {
                throw new Exception("Can't update table without columns to be updated.");
            }

            StringBuilder sb = new StringBuilder($"UPDATE {SqlTableHelper.GetTableName<T>()} SET ");

            for (int i = 0; i < _newColumnValues.Count; i++)
            {
                var colVal = _newColumnValues[i];

                sb.Append($"{Util.FormatSQL(colVal.Column)}={colVal.Value}");
                if (i < _newColumnValues.Count - 1)
                {
                    sb.Append(", ");
                }
            }

            sb.Append(_condition?.GenerateStatement());
            return(sb.ToString());
        }
Exemple #16
0
 /// <summary>
 /// Specify the table to delete from.
 /// </summary>
 /// <typeparam name="T">The table you want to delete from.</typeparam>
 public BuiltDeleteCommand From <T>()
 {
     _table = SqlTableHelper.GetTableName <T>();
     return(this);
 }
 internal BuiltAlterTableCommand()
 {
     _table      = SqlTableHelper.GetTableName <T>();
     _components = new List <string>();
 }
 internal BuiltInsertCommand()
 {
     _rowValues = new List <BuiltValueList <T> >();
     _table     = SqlTableHelper.GetTableName <T>();
     _columns   = SqlTableHelper.GetColumnNames <T>();
 }
        /// <summary>
        /// Takes the current row that the reader is on and uses it to construct a new entity of type T.<para />
        /// Does not alter the reader's cursor at all.
        /// May return an incomplete entity in case of failure.
        /// </summary>
        /// <param name="tableType">The type of entity to be constructed.</param>
        /// <param name="reader">The reader to be used. Make sure .HasRows is true before passing this in.</param>
        /// <returns>The constructed entity of type T.</returns>
        internal object BuildSingle(Type tableType, DbDataReader reader)
        {
            var propToCol = SqlTableHelper.PropertiesToColumnNames(tableType);
            var entity    = Activator.CreateInstance(tableType);

            foreach (var property in propToCol)
            {
                var sqlColumnAttribute = property.Key.GetCustomAttribute <SqlColumn>();
                if (sqlColumnAttribute != null && property.Key.GetCustomAttribute <SqlForeignKey>() == null)
                {
                    switch (sqlColumnAttribute.Type)
                    {
                    case DbType.Int16:
                    case DbType.Int32:
                    case DbType.Int64:
                        property.Key.SetValue(entity, Convert.ToInt32(reader[property.Value]), null);
                        break;

                    case DbType.String:
                    case DbType.StringFixedLength:
                    case DbType.AnsiString:
                    case DbType.AnsiStringFixedLength:
                        string value = reader[property.Value].ToString();
                        property.Key.SetValue(entity, value, null);
                        break;

                    case DbType.Date:
                    case DbType.DateTime:
                    case DbType.DateTime2:
                    case DbType.DateTimeOffset:
                        string   stringValue = reader[property.Value].ToString();
                        DateTime dateValue   = DateTime.ParseExact(stringValue, ProviderSpecific.DotNetDateFormatString, CultureInfo.InvariantCulture);
                        property.Key.SetValue(entity, dateValue, null);
                        break;

                    default:
                        throw new Exception("Unsupported column type.");
                    }
                }

                if (sqlColumnAttribute == null)
                {
                    continue;
                }
                var sqlForeignKeyAttributes = property.Key.GetCustomAttributes <SqlForeignKey>();
                foreach (var foreignKey in sqlForeignKeyAttributes)
                {
                    Type foreignTable = foreignKey.ForeignTable;
                    var  properties   = foreignTable.GetProperties();
                    foreach (var prop in properties)
                    {
                        var attrib = prop.GetCustomAttribute <SqlColumn>();
                        if (attrib == null)
                        {
                            continue;
                        }
                        if (attrib.ColumnName == foreignKey.ForeignTableColumnName)
                        {
                            var foreignSelect = SqlBuild.Select(foreignTable)
                                                .Join(tableType, foreignTable, sqlColumnAttribute.ColumnName, foreignKey.ForeignTableColumnName)
                                                .Where(
                                new BuiltSqlCondition()
                                .AddCondition(tableType, sqlColumnAttribute.ColumnName, "=", (string)reader[sqlColumnAttribute.ColumnName], sqlColumnAttribute.Type));
                            var foreignReader = foreignSelect.GenerateCommand(_connection).ExecuteReader();
                            if (!foreignReader.Read())
                            {
                                continue;
                            }

                            var foreignEntity = BuildSingle(foreignTable, foreignReader);
                            property.Key.SetValue(entity, foreignEntity);
                            break;
                        }
                    }
                }
            }
            return(entity);
        }
 internal BuiltInsertCommand(params string[] columns)
 {
     _rowValues = new List <BuiltValueList <T> >();
     _table     = SqlTableHelper.GetTableName <T>();
     _columns   = new List <string>(columns);
 }