/// <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>
/// 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>
/// 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(params string[] columns)
{
_rowValues = new List <BuiltValueList <T> >();
_table = SqlTableHelper.GetTableName <T>();
_columns = new List <string>(columns);
}
internal BuiltInsertCommand()
{
_rowValues = new List <BuiltValueList <T> >();
_table = SqlTableHelper.GetTableName <T>();
_columns = SqlTableHelper.GetColumnNames <T>();
}