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)); }
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)); }
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)); }
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)); }
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)); }
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)); }
public void SelectStar() { var expected = @"SELECT * FROM Customers "; var q = new QueryBuilder() .From .Table("Customers") .End ; q.GetQueryString().Should(Be.EqualTo(expected)); }
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)); }
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; }
public static ExistsExpression Exists(QueryBuilder queryBuilder) { return new ExistsExpression(queryBuilder); }