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