public void NotIn()
{
var q = new SharpQuery();
q.Select(1).Where(ConstantExpression.GetConstant(1).IsNotIn(2, 3, 4, 5, 6, 7)).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE @p0 NOT IN (@p1, @p2, @p3, @p4, @p5,@p6)
");
}
public void LessThan()
{
var q = new SharpQuery();
q.Select(1).Where(ConstantExpression.GetConstant(1).IsLessThan(2)).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE @p0 < @p1
");
}
public void Between()
{
var q = new SharpQuery();
q.Select(1).Where(ConstantExpression.GetConstant(4).IsBetween(3, 5)).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE (@p1 BETWEEN @p2 AND @p3)
");
}
public void GreaterThanOrEqualTo()
{
var q = new SharpQuery();
q.Select(1).Where(ConstantExpression.GetConstant(1).IsGreaterThanOrEqualTo(1)).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE @p0 >= @p0
");
}
public void In()
{
var q = new SharpQuery();
q.Select(1).Where(ConstantExpression.GetConstant(1).IsIn(1, 2, 3, 4, 5)).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE @p0 IN (@p0, @p1, @p2, @p3, @p4)
");
}
public void IsNotNull()
{
var q = new SharpQuery();
q.Select(1).Where(ConstantExpression.GetConstant(1).IsNotNull()).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE @p0 IS NOT NULL
");
}
public void NotInSelect()
{
var q = new SharpQuery();
var singleExprSelectStatement = q.Select(1).Statement;
q.Select(1).Where(ConstantExpression.GetConstant(2).IsNotIn(singleExprSelectStatement)).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE @p1 NOT IN (SELECT @p0)
");
}
public void Mod()
{
var q = new SharpQuery();
var a = ConstantExpression.GetConstant(5);
var b = ConstantExpression.GetConstant(2);
q.Select(a % b).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT (@p0 % @p1)
");
}
public void EndsWith()
{
var q = new SharpQuery();
var a = ConstantExpression.GetConstant("code insiders");
q.Select(1).Where(a.EndsWith("insiders")).EndStatement();
var actual = q.ToString();
TSqlAssert.ScriptsAreEqual(actual, @"
SELECT @p0
WHERE @p1 LIKE (@p2 + @p3)
");
}
public void And()
{
var q = new SharpQuery();
var a = ConstantExpression.GetConstant(1);
var b = ConstantExpression.GetConstant(2);
q.Select(1).Where(a.IsEqualTo(1) & b.IsEqualTo(3)).EndStatement();
var actual = q.ToString();
TSqlAssert.ScriptsAreEqual(actual, @"
SELECT @p0
WHERE (@p0 = @p0
AND @p1 = @p2)
");
}
public void SearchedCase2()
{
var q = new SharpQuery();
var v = ConstantExpression.GetConstant(1);
var p = ConstantExpression.GetConstant(2);
q.Select(q
.CaseWhen(v.IsBetween(2, 3), 3)
.CaseWhen(v.IsEqualTo(5), 4)
.CaseWhen(p.IsEqualTo(v), 10)
.Else(p))
.EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT
(CASE WHEN ( @p0 BETWEEN @p1 AND @p2 ) THEN @p2
WHEN @p0 = @p3 THEN @p4
WHEN @p1 = @p0 THEN @p5
ELSE @p1
END)
");
}
