Esempio n. 1
0
        public void SelectFromQuery()
        {
            var expected =
            @"SELECT
            i.Id
            FROM
            (SELECT
            *
            FROM
            Customers
            ) AS i
            ";
            var inner = new QueryBuilder()
                .From
                    .Table("Customers")
                    .End
                ;
            var q = new QueryBuilder()
                .Select
                    .Add("i.Id")
                    .End
                .From
                    .Query(inner, "i")
                    .End
                ;

            q.GetQueryString().Should(Be.EqualTo(expected));
        }
Esempio n. 2
0
        public void SelectSelectFields()
        {
            var expected =
            @"SELECT
            (
            SELECT
            Id
            FROM
            TBL
            ) AS TblId
            FROM
            Customers
            ";
            var inner = new QueryBuilder()
                .Select
                    .Add("Id")
                    .End
                .From
                    .Table("TBL")
                    .End;

            var q = new QueryBuilder()
                .Select
                    .Add(inner, "TblId")
                    .End
                .From
                    .Table("Customers")
                    .End;

            q.GetQueryString().Should(Be.EqualTo(expected));
        }
Esempio n. 3
0
        public void SubQueries_DoesNotRenderOrderByClauseWithoutTop()
        {
            var expected =
            @"SELECT
            COUNT (*)
            FROM
            (SELECT
            *
            FROM
            Customers
            ) AS inner
            ";
            var inner = new QueryBuilder()
                .From
                    .Table("Customers")
                    .End
                .OrderBy
                    .Add("SHOULD NOT BE RENDERED")
                    .End
                ;

            var q = new QueryBuilder()
                .Select
                    .Add("COUNT (*)")
                    .End
                .From
                    .Query(inner, "inner")
                    .End
                ;

            q.GetQueryString().Should(Be.EqualTo(expected));
        }
Esempio n. 4
0
        public void SelectStarWhere()
        {
            var expected =
            @"SELECT
            *
            FROM
            Customers
            WHERE
            Name LIKE @Name
            ";
            var q = new QueryBuilder()
                .From
                    .Table("Customers")
                    .End
                .Where
                    .Add("Name LIKE @Name")
                    .End
                ;

            q.GetQueryString().Should(Be.EqualTo(expected));
        }
Esempio n. 5
0
        public void SimpleJoins()
        {
            var expected =
            @"SELECT
            *
            FROM
                Customers AS c
            INNER JOIN	Orders AS o ON (o.CustomerId = c.Id)
            ";
            var q = new QueryBuilder()
                .From
                    .Table("Customers", "c")
                    .Add(Joins.Table("Orders").As("o").On("o.CustomerId = c.Id"))
                    .End
                ;

            q.GetQueryString().Should(Be.EqualTo(expected));
        }
Esempio n. 6
0
        public void SelectStarOrder()
        {
            var expected =
            @"SELECT
            *
            FROM
            Customers
            ORDER BY
            Id,
            Name
            ";
            var q = new QueryBuilder()
                .From
                    .Table("Customers")
                    .End
                .OrderBy
                    .Add("Id")
                    .Add("Name")
                    .End
                ;

            q.GetQueryString().Should(Be.EqualTo(expected));
        }
Esempio n. 7
0
        public void SelectStar()
        {
            var expected =
            @"SELECT
            *
            FROM
            Customers
            ";
            var q = new QueryBuilder()
                .From
                    .Table("Customers")
                    .End
                ;

            q.GetQueryString().Should(Be.EqualTo(expected));
        }
Esempio n. 8
0
        public void SelectSimpleFields()
        {
            var expected =
            @"SELECT
            Id,
            Name
            FROM
            Customers
            ";
            var q = new QueryBuilder();
            var s = q.Select;
            s.Add("Id");
            s.Add("Name");
            var f = q.From;
            f.Table("Customers");

            q.GetQueryString().Should(Be.EqualTo(expected));
        }
Esempio n. 9
0
        QueryBuilder WrapWithLimit()
        {
            var orderBy = OrderBy.GetQueryString();
            OrderBy.Clear();

            var wrapper = new QueryBuilder();

            foreach (var expression in Select.Expressions)
            {
                var exp = (Expression)expression;
                wrapper.Select.Add(INTERNAL_QUERY + "." + exp.Alias);
            }
            wrapper.From.Query(this, INTERNAL_QUERY);
            Select.Add("ROW_NUMBER() OVER(" + orderBy + " )", ROWNUM_FIELD);

            string where;
            if (Limit.First.HasValue && Limit.Max.HasValue)
                where = " BETWEEN " + (Limit.First.Value + 1) + " AND " + (Limit.First.Value + Limit.Max.Value);
            else if (Limit.First.HasValue && Limit.Max.HasValue == false)
                where = " >= " + (Limit.First.Value + 1);
            else
                where = " <= " + Limit.Max.Value;
            wrapper.Where.Add(ROWNUM_FIELD + where);

            isWrapped = true;
            return wrapper;
        }
Esempio n. 10
0
 public static ExistsExpression Exists(QueryBuilder queryBuilder)
 {
     return new ExistsExpression(queryBuilder);
 }