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);
}