Esempio n. 1
0
        /// <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());
        }