public override void Dispose()
{
WhereStatement = null;
Columns = null;
GC.SuppressFinalize(this);
}
/// <summary>
/// Creates a where clause to add to the query's where statement
/// </summary>
/// <param name="field">The column name</param>
/// <returns></returns>
public SqlExpression <SelectWhereStatement> Where(string field)
{
if (WhereStatement.InnerClauseOperator == LogicOperator.And)
{
return(WhereStatement.And(field));
}
else
{
return(WhereStatement.Or(field));
}
}
/// <summary>
/// Creates a where clause to add to the query's where statement
/// </summary>
/// <param name="field">The column name</param>
/// <param name="operator">The Comaparison Operator to use</param>
/// <param name="compareValue">The value, for the column name and comparison operator</param>
/// <returns></returns>
public SelectWhereStatement Where(string field, Comparison @operator, object compareValue)
{
if (WhereStatement.InnerClauseOperator == LogicOperator.And)
{
return(WhereStatement.And(field, @operator, compareValue));
}
else
{
return(WhereStatement.Or(field, @operator, compareValue));
}
}
/// <summary>
/// Builds the query string or SQLiteCommand
/// </summary>
/// <param name="buildCommand"></param>
/// <returns></returns>
protected object BuildQuery(bool buildCommand)
{
// Define local variables
int tableIndex = 0;
int tableCount = Tables.Count;
// Make sure we have a table name
if (Tables.Count == 0 || String.IsNullOrWhiteSpace(Tables[0].Name))
{
throw new Exception("No tables were specified for this query.");
}
// Start Query
StringBuilder query = new StringBuilder("SELECT ", 256);
query.AppendIf(Distinct, "DISTINCT ");
// Append columns from each table
foreach (var table in Tables)
{
// Define local variables
int colCount = table.Columns.Count;
tableIndex++;
tableCount--;
// Create alias for this table if there is none
if (String.IsNullOrWhiteSpace(table.Alias))
{
table.Alias = $"t{tableIndex}";
}
// Check if the user wants to select all columns
if (colCount == 0)
{
query.AppendFormat("{0}.*", Context.QuoteIdentifier(table.Alias));
query.AppendIf(tableCount > 0, ", ");
}
else
{
// Add each result selector to the query
foreach (ColumnIdentifier column in table.Columns.Values)
{
// Use the internal method to append the column string to our query
column.AppendToQuery(query, Context, table.Alias);
// If we have more results to select, append Comma
query.AppendIf(--colCount > 0 || tableCount > 0, ", ");
}
}
}
// === Append main Table === //
var fromTbl = Tables[0];
query.Append($" FROM {Context.QuoteIdentifier(fromTbl.Name)} AS {Context.QuoteIdentifier(fromTbl.Alias)}");
// Append Joined tables
if (Joins.Count > 0)
{
foreach (JoinClause clause in Joins)
{
// Convert join type to string
switch (clause.JoinType)
{
default:
case JoinType.InnerJoin:
query.Append(" JOIN ");
break;
case JoinType.OuterJoin:
query.Append(" OUTER JOIN ");
break;
case JoinType.CrossJoin:
query.Append(" CROSS JOIN ");
break;
case JoinType.LeftJoin:
query.Append(" LEFT JOIN ");
break;
}
// Append the join statement
string alias = Context.QuoteIdentifier(clause.JoiningTable.Alias);
query.Append($"{Context.QuoteIdentifier(clause.JoiningTable.Name)} AS {alias}");
// Do we have an expression?
if (clause.ExpressionType == JoinExpressionType.On)
{
// Try and grab the table
var tbl = Tables.Where(x => x.Name.Equals(clause.FromTable, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
string fromT = tbl?.Alias ?? clause.FromTable;
query.Append(" ON ");
query.Append(
SqlExpression <WhereStatement> .CreateExpressionString(
$"{alias}.{Context.QuoteIdentifier(clause.JoiningColumn)}",
clause.ComparisonOperator,
new SqlLiteral(Context.QuoteIdentifier($"{fromT}.{clause.FromColumn}"))
)
);
}
else if (clause.ExpressionType == JoinExpressionType.Using)
{
var parts = clause.JoiningColumn.Split(',');
query.AppendFormat(" USING({0})", String.Join(", ", parts.Select(x => Context.QuoteIdentifier(x))));
}
}
}
// Append Where Statement
List <SQLiteParameter> parameters = new List <SQLiteParameter>();
if (WhereStatement.HasClause)
{
if (buildCommand)
{
query.Append(" WHERE " + WhereStatement.BuildStatement(parameters));
}
else
{
query.Append(" WHERE " + WhereStatement.BuildStatement());
}
}
// Append GroupBy
if (GroupByColumns.Count > 0)
{
query.Append(" GROUP BY " + String.Join(", ", GroupByColumns.Select(x => Context.QuoteIdentifier(x))));
}
// Append Having
if (HavingStatement.HasClause)
{
if (GroupByColumns.Count == 0)
{
throw new Exception("Having statement was set without Group By");
}
query.Append(" HAVING " + HavingStatement.BuildStatement(parameters));
}
// Append OrderBy
if (OrderByStatements.Count > 0)
{
int count = OrderByStatements.Count;
query.Append(" ORDER BY");
foreach (OrderByClause clause in OrderByStatements)
{
query.Append($" {Context.QuoteIdentifier(clause.ColumnName)}");
// Add sorting if not default
query.AppendIf(clause.SortOrder == Sorting.Descending, " DESC");
// Append seperator if we have more orderby statements
query.AppendIf(--count > 0, ",");
}
}
// Append Limit
query.AppendIf(Limit > 0, " LIMIT " + Limit);
query.AppendIf(Offset > 0, " OFFSET " + Offset);
// Create Command
SQLiteCommand command = null;
if (buildCommand)
{
command = Context.CreateCommand(query.ToString());
command.Parameters.AddRange(parameters.ToArray());
}
// Return Result
return((buildCommand) ? command as object : query.ToString());
}
/// <summary>
/// Creates a new instance of <see cref="DeleteQueryBuilder"/> using the
/// specified <see cref="SQLiteContext"/>
/// </summary>
/// <param name="context"></param>
public DeleteQueryBuilder(SQLiteContext context)
{
this.Context = context;
this.WhereStatement = new WhereStatement(context);
}
/// <summary>
/// Creates a new instance of UpdateQueryBuilder with the provided SQLite connection.
/// </summary>
/// <param name="table">The table name we are updating data in</param>
/// <param name="context">The SQLiteContext that will be used to build and query this SQL statement</param>
public UpdateQueryBuilder(string table, SQLiteContext context) : base(context)
{
this.Table = table;
this.Context = context;
this.WhereStatement = new WhereStatement(context);
}