/// <summary>
/// Builds the query string or DbCommand
/// </summary>
/// <param name="BuildCommand"></param>
/// <returns></returns>
protected object BuildQuery(bool BuildCommand)
{
// Make sure we have a valid DB driver
if (BuildCommand && Driver == null)
{
throw new Exception("Cannot build a command when the Db Drvier hasn't been specified. Call SetDbDriver first.");
}
// Make sure we have a table name
if (selectedTables.Count == 0)
{
throw new Exception("No tables were specified for this query.");
}
// Create Command
DbCommand Command = (BuildCommand) ? Driver.CreateCommand(null) : null;
// Start Query
StringBuilder Query = new StringBuilder("SELECT ");
Query.AppendIf(Distinct, "DISTINCT ");
// Append columns
Query.Append(String.Join(", ", SelectedColumns));
// Append Tables
Query.Append(" FROM " + String.Join(", ", SelectedTables));
// Append Joins
if (Joins.Count > 0)
{
foreach (JoinClause Clause in Joins)
{
// Convert join type to string
switch (Clause.JoinType)
{
case JoinType.InnerJoin:
Query.Append(" JOIN ");
break;
case JoinType.OuterJoin:
Query.Append(" FULL OUTER JOIN ");
break;
case JoinType.LeftJoin:
Query.Append(" LEFT JOIN ");
break;
case JoinType.RightJoin:
Query.Append(" RIGHT JOIN ");
break;
}
// Append the join statement
Query.Append($"{Clause.JoiningTable} ON ");
Query.Append(
WhereStatement.CreateComparisonClause(
$"{Clause.JoiningTable}.{Clause.JoiningColumn}",
Clause.ComparisonOperator,
new SqlLiteral($"{Clause.FromTable}.{Clause.FromColumn}")
)
);
}
}
// Append Where
if (this.WhereStatement.Count != 0)
{
Query.Append(" WHERE " + WhereStatement.BuildStatement(Command));
}
// Append GroupBy
if (GroupByColumns.Count > 0)
{
Query.Append(" GROUP BY " + String.Join(", ", GroupByColumns));
}
// Append Having
if (HavingStatement.Count > 0)
{
if (GroupByColumns.Count == 0)
{
throw new Exception("Having statement was set without Group By");
}
Query.Append(" HAVING " + this.HavingStatement.BuildStatement(Command));
}
// Append OrderBy
if (OrderByStatements.Count > 0)
{
int count = OrderByStatements.Count;
Query.Append(" ORDER BY");
foreach (OrderByClause Clause in OrderByStatements)
{
Query.Append($" {Clause.FieldName}");
// 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
if (LimitRecords is Array)
{
if (LimitRecords.Length == 1)
{
Query.Append(" LIMIT " + LimitRecords[0].ToString());
}
else
{
Query.Append($" LIMIT {LimitRecords[1]}, {LimitRecords[0]}");
}
}
// Set the command text
if (BuildCommand)
{
Command.CommandText = Query.ToString();
}
// Return Result
return((BuildCommand) ? Command as object : Query.ToString());
}
/// <summary>
/// Builds the query string or DbCommand
/// </summary>
/// <param name="BuildCommand"></param>
/// <returns></returns>
protected object BuildQuery(bool BuildCommand)
{
// Make sure we have a valid DB driver
if (BuildCommand && Driver == null)
{
throw new Exception("Cannot build a command when the Db Drvier hasn't been specified. Call SetDbDriver first.");
}
// Make sure we have a table name
if (String.IsNullOrWhiteSpace(Table))
{
throw new Exception("Table to insert into was not set.");
}
// Make sure we have at least 1 field to update
if (Fields.Count == 0)
{
throw new Exception("No fields to insert");
}
// Create Command
DbCommand Command = (BuildCommand) ? Driver.CreateCommand(null) : null;
// Start Query
StringBuilder Query = new StringBuilder("INSERT INTO " + Table + " (");
StringBuilder Values = new StringBuilder();
bool First = true;
// Add fields and values
foreach (KeyValuePair <string, object> Item in Fields)
{
// Append comma
if (!First)
{
Query.Append(", ");
Values.Append(", ");
}
else
{
First = false;
}
// If using a command, Convert values to Parameters
if (BuildCommand && Item.Value != null && Item.Value != DBNull.Value && !(Item.Value is SqlLiteral))
{
// Create param for value
DbParameter Param = Command.CreateParameter();
Param.ParameterName = "@P" + Command.Parameters.Count;
Param.Value = Item.Value;
// Add Params to command
Command.Parameters.Add(Param);
// Append query's
Query.Append(Item.Key);
Values.Append(Param.ParameterName);
}
else
{
Query.Append(Item.Key);
Values.Append(WhereStatement.FormatSQLValue(Item.Value));
}
}
// Finish the query string, and return the proper object
Query.Append(") VALUES (" + Values.ToString() + ")");
// Set the command text
if (BuildCommand)
{
Command.CommandText = Query.ToString();
}
return((BuildCommand) ? Command as object : Query.ToString());
}