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