/// <inheritdoc />
public override string Build()
{
var sb = new StringBuilder("SELECT ");
// Output Distinct.
if (IsDistinct)
{
sb.Append("DISTINCT ");
}
// Output column names.
sb.Append(SelectedColumns.Any() ? string.Join(", ", SelectedColumns.Select(WrapVariable)) : "*");
// Output table names.
if (SelectedTables.Any())
{
sb.Append($" FROM {string.Join(", ", SelectedTables.Select(WrapVariable))}");
}
// Output joins.
if (JoinStatement.Any())
{
foreach (var clause in JoinStatement)
{
sb.AppendLine();
switch (clause.JoinType)
{
case JoinType.InnerJoin:
sb.Append("INNER JOIN ");
break;
case JoinType.LeftJoin:
sb.Append("LEFT JOIN ");
break;
case JoinType.RightJoin:
sb.Append("RIGHT JOIN ");
break;
default:
throw new ArgumentOutOfRangeException(nameof(clause.JoinType), $"MySql doesn't support {clause.JoinType} join type.");
}
sb.Append($"`{clause.ToTable}` ON ");
sb.Append(CreateComparisonClause(
$"{clause.ToTable}.{clause.ToColumn}",
clause.ComparisonOperator,
new SqlLiteral($"{clause.FromTable}.{clause.FromColumn}")));
}
}
// Output where statement.
if (WhereStatement.Any())
{
sb.AppendLine();
sb.Append($"WHERE {string.Join(" AND ", WhereStatement.Select(BuildWhereClauseString))}");
}
// Output GroupBy statement.
if (GroupByColumns.Count > 0)
{
sb.AppendLine();
sb.Append($"GROUP BY {string.Join(", ", GroupByColumns.Select(WrapVariable))}");
}
// Output OrderBy statement.
if (OrderByStatement.Any())
{
sb.AppendLine();
sb.Append($"ORDER BY {string.Join(", ", OrderByStatement.Select(BuildOrderByClauseString))}");
}
if (TakeRows.HasValue)
{
sb.AppendLine();
if (SkipRows.HasValue)
{
sb.Append($"LIMIT {SkipRows}, {TakeRows}");
}
else
{
sb.Append($"LIMIT {TakeRows}");
}
}
// Return the built query.
return(sb.ToString());
}
/// <inheritdoc />
public override string Build()
{
var sb = new StringBuilder("SELECT ");
// Output Distinct.
if (IsDistinct)
{
sb.Append("DISTINCT ");
}
if (!SkipRows.HasValue && TakeRows.HasValue)
{
sb.Append($"TOP {TakeRows} ");
if (TopIsPercent)
{
sb.Append("PERCENT ");
}
}
// Output column names.
sb.Append(SelectedColumns.Any() ? string.Join(", ", SelectedColumns.Select(WrapVariable)) : "*");
// Output table names.
if (SelectedTables.Any())
{
sb.Append($" FROM {string.Join(", ", SelectedTables.Select(WrapVariable))}");
}
// Output joins.
if (JoinStatement.Any())
{
foreach (var clause in JoinStatement)
{
sb.AppendLine();
switch (clause.JoinType)
{
case JoinType.InnerJoin:
sb.Append("INNER JOIN ");
break;
case JoinType.OuterJoin:
sb.Append("OUTER JOIN ");
break;
case JoinType.LeftJoin:
sb.Append("LEFT JOIN ");
break;
case JoinType.RightJoin:
sb.Append("RIGHT JOIN ");
break;
}
sb.Append($"[{clause.ToTable}] ON ");
sb.Append(CreateComparisonClause(
$"{clause.ToTable}.{clause.ToColumn}",
clause.ComparisonOperator,
new SqlLiteral($"{clause.FromTable}.{clause.FromColumn}")));
}
}
// Output where statement.
if (WhereStatement.Any())
{
sb.AppendLine();
sb.Append($"WHERE {string.Join(" AND ", WhereStatement.Select(BuildWhereClauseString))}");
}
// Output GroupBy statement.
if (GroupByColumns.Count > 0)
{
sb.AppendLine();
sb.Append($"GROUP BY {string.Join(", ", GroupByColumns.Select(WrapVariable))}");
}
// TODO: Output having statement.
/*
* if (Having.ClauseLevels > 0)
* {
* // Check if a Group By Clause was set
* if (groupByColumns.Count == 0)
* {
* throw new Exception("Having statement was set without Group By");
* }
* if (buildCommand)
* {
* sb.Append(" HAVING " + Having.BuildWhereStatement(() => command));
* }
* else
* {
* sb.Append(" HAVING " + Having.BuildWhereStatement());
* }
* }
*/
// Output OrderBy statement.
if (OrderByStatement.Any())
{
sb.AppendLine();
sb.Append($"ORDER BY {string.Join(", ", OrderByStatement.Select(BuildOrderByClauseString))}");
// Works only in SQL Server 2012 and upper.
// TODO use BETWEEN if it require
if (SkipRows.HasValue)
{
sb.AppendLine();
sb.Append($"OFFSET {SkipRows} ROWS");
if (TakeRows.HasValue)
{
sb.AppendLine();
sb.Append($"FETCH NEXT {TakeRows} ROWS ONLY");
}
}
}
// Return the built query.
return(sb.ToString());
}