public void QueryAlwaysGeneratesTheSameScript()
{
var q = new SharpQuery();
var u = new UserTable();
var p = new PostTable();
q.Select(u.FirstName, u.Email, p.Title).From(u).LeftOuterJoin(p, p.UserId.IsEqualTo(u.Id)).EndStatement();
var script1 = q.ToString();
var script2 = q.ToString();
TSqlAssert.ScriptsAreEqual(script1, script2);
}
public void QueryAlwaysGeneratesTheSameScript()
{
var q = new SharpQuery();
var u = new UserTable();
var p = new PostTable();
q.Select(u.FirstName, u.Email, p.Title).From(u).LeftOuterJoin(p, p.UserId.IsEqualTo(u.Id)).EndStatement();
var script1 = q.ToString();
var script2 = q.ToString();
TSqlAssert.ScriptsAreEqual(script1, script2);
}
public void InnerJoin()
{
var u = new UserTable();
var p = new PostTable();
var q = new SharpQuery();
q.Select(p.AllColumns)
.From(p)
.InnerJoin(u, u.Id.IsEqualTo(p.UserId))
.Where(u.FirstName.IsEqualTo("Mike"))
.EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, @"
SELECT
[dbo].[Post].[Id]
, [dbo].[Post].[UserId]
, [dbo].[Post].[Title]
, [dbo].[Post].[Text]FROM
[dbo].[Post]
INNER JOIN [dbo].[User] ON [dbo].[User].[Id] = [dbo].[Post].[UserId]WHERE
[dbo].[User].[Name] = @p0
");
}
public void NotEndedStatementsShouldNotBeAddedToScript1()
{
var q = new SharpQuery();
q.Select(1);
TSqlAssert.ScriptsAreEqual(string.Empty, q.ToString());
}
public void InnerJoin()
{
var u = new UserTable();
var p = new PostTable();
var q = new SharpQuery();
q.Select(p.AllColumns)
.From(p)
.InnerJoin(u, u.Id.IsEqualTo(p.UserId))
.Where(u.FirstName.IsEqualTo("Mike"))
.EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, @"
SELECT
[dbo].[Post].[Id]
, [dbo].[Post].[UserId]
, [dbo].[Post].[Title]
, [dbo].[Post].[Text]FROM
[dbo].[Post]
INNER JOIN [dbo].[User] ON [dbo].[User].[Id] = [dbo].[Post].[UserId]WHERE
[dbo].[User].[Name] = @p0
");
}
public void EndedStatementsShouldBeAddedToScript()
{
var q = new SharpQuery();
q.Select(1).EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, "SELECT @p0");
}
public void Having()
{
var q = new SharpQuery();
var u = new UserTable();
var p = new PostTable();
int userId = 1;
q.Select(u.Id, Sql.Count(1))
.From(u)
.InnerJoin(p, u.Id.IsEqualTo(p.UserId))
.Where(u.Id.IsEqualTo(userId) & p.PostDate.IsGreaterThanOrEqualTo(DateTime.Now.AddDays(-7)))
.GroupBy(u.Id)
.Having(Sql.Count(1).IsGreaterThan(10))
.EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT [dbo].[User].[Id],
COUNT(@p0)
FROM [dbo].[User]
INNER JOIN
[dbo].[Post]
ON [dbo].[User].[Id] = [dbo].[Post].[UserId]
WHERE ([dbo].[User].[Id] = @p0
AND [dbo].[Post].[PostDate] >= @p1)
GROUP BY [dbo].[User].[Id]
HAVING COUNT(@p0) > @p2;
");
}
public void Having()
{
var q = new SharpQuery();
var u = new UserTable();
var p = new PostTable();
int userId = 1;
q.Select(u.Id, Sql.Count(1))
.From(u)
.InnerJoin(p, u.Id.IsEqualTo(p.UserId))
.Where(u.Id.IsEqualTo(userId) & p.PostDate.IsGreaterThanOrEqualTo(DateTime.Now.AddDays(-7)))
.GroupBy(u.Id)
.Having(Sql.Count(1).IsGreaterThan(10))
.EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT [dbo].[User].[Id],
COUNT(@p0)
FROM [dbo].[User]
INNER JOIN
[dbo].[Post]
ON [dbo].[User].[Id] = [dbo].[Post].[UserId]
WHERE ([dbo].[User].[Id] = @p0
AND [dbo].[Post].[PostDate] >= @p1)
GROUP BY [dbo].[User].[Id]
HAVING COUNT(@p0) > @p2;
");
}
public void Test1()
{
var q = new SharpQuery();
q.Select(1).EndStatement();
var query = q.ToString();
Assert.That(q.ScriptBuilder.Parameters.Count, Is.EqualTo(1));
}
public void Test1()
{
var q = new SharpQuery();
q.Select(1).EndStatement();
var query = q.ToString();
Assert.That(q.ScriptBuilder.Parameters.Count, Is.EqualTo(1));
}
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 EndedStatementsShouldBeAddedToScript()
{
var q = new SharpQuery();
q.Select(1).EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, "SELECT @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 IsNull()
{
var q = new SharpQuery();
q.Select(1).Where(ConstantExpression.NULL.IsNull()).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE NULL IS NULL
");
}
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 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 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 Multiply()
{
var q = new SharpQuery();
var a = ConstantExpression.GetConstant(1.1);
var b = ConstantExpression.GetConstant(2.2);
q.Select(a * b).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT (@p0 * @p1)
");
}
public void Minus()
{
var q = new SharpQuery();
var a = Expression.GetConstant(1);
var b = Expression.GetConstant(2);
q.Select(a - b).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT (@p0 - @p1)");
}
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 Divide()
{
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 Minus()
{
var q = new SharpQuery();
var a = Expression.GetConstant(1);
var b = Expression.GetConstant(2);
q.Select(a - b).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT (@p0 - @p1)");
}
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 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 FalsePredicate()
{
var q = new SharpQuery();
var u = new UserTable();
q.Select(1).From(u).Where(Predicate.FALSE).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
FROM [dbo].[User]
WHERE 1 <> 1;
");
}
public void InsertWithMultipleValues()
{
var u = new UserTable();
var q = new SharpQuery();
q.InsertInto(u, u.Id, u.FirstName, u.Email)
.Values(1, "a", "b")
.Values(2, "a", "b")
.Values(3, "a", "b")
.EndStatement();
var str = q.ToString();
}
public void SelectSingleColumnFromAliasedTable()
{
var u = new UserTable("u");
var q = new SharpQuery();
q.Select(u.FirstName).From(u).EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, @"
SELECT [u].[Name]
FROM [dbo].[User] AS [u]
");
}
public void InsertWithMultipleValues()
{
var u = new UserTable();
var q = new SharpQuery();
q.InsertInto(u, u.Id, u.FirstName, u.Email)
.Values(1, "a", "b")
.Values(2, "a", "b")
.Values(3, "a", "b")
.EndStatement();
var str = q.ToString();
}
public void SelectAliasedColumnFromTable()
{
var u = new UserTable();
var q = new SharpQuery();
q.Select(u.FirstName.As("Username")).From(u).EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, @"
SELECT [dbo].[User].[Name] AS [Username]
FROM [dbo].[User]
");
}
public void Exists()
{
var q = new SharpQuery();
var subQuery = q.Select(1).Statement;
q.Select(1).Where(Predicate.Exists(subQuery)).EndStatement();
var actual = q.ToString();
TSqlAssert.ScriptsAreEqual(actual, @"
SELECT
@p0
WHERE EXISTS( SELECT @p0 )
");
}
public void SingleExprSingleColumnSelect()
{
var q = new SharpQuery();
var selectStm = q.SelectOne(2).Statement;
var exp = (Expression)selectStm;
q.Select(exp).EndStatement();
var sqlString = q.ToString();
TSqlAssert.ScriptsAreEqual(sqlString, @"
SELECT
( SELECT TOP ( @p0 ) @p1 )
");
}
public void InsertIntoTable()
{
var u = new UserTable();
var q = new SharpQuery();
q.InsertInto(u, u.Email, u.FirstName).Values("*****@*****.**", "username").EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
INSERT INTO [dbo].[User]( [dbo].[User].[Email]
, [dbo].[User].[Name])
VALUES(
@p0
, @p1 )
");
}
public void InsertIntoTable()
{
var u = new UserTable();
var q = new SharpQuery();
q.InsertInto(u, u.Email, u.FirstName).Values("*****@*****.**", "username").EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
INSERT INTO [dbo].[User]( [dbo].[User].[Email]
, [dbo].[User].[Name])
VALUES(
@p0
, @p1 )
");
}
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 SelectAllColumnsFromTable()
{
var q = new SharpQuery();
var u = new UserTable();
q.Select(u.AllColumns).From(u).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT
[dbo].[User].[Id]
, [dbo].[User].[Name]
, [dbo].[User].[Email]
FROM [dbo].[User]
");
}
public void SimpleCase2()
{
var q = new SharpQuery();
q.Select(q.Case(1).When(1, 2).When(3, 4).When(5, 6).Else(7)).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT
( CASE @p0
WHEN @p0 THEN @p1
WHEN @p2 THEN @p3
WHEN @p4 THEN @p5
ELSE @p6
END )
");
}
public void SingleExprSingleColumnSelect()
{
var q = new SharpQuery();
var selectStm = q.SelectOne(2).Statement;
var exp = (Expression)selectStm;
q.Select(exp).EndStatement();
var sqlString = q.ToString();
TSqlAssert.ScriptsAreEqual(sqlString, @"
SELECT
( SELECT TOP ( @p0 ) @p1 )
");
}
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 SelectWithWhere()
{
var u = new UserTable();
var q = new SharpQuery();
q.Select(u.FirstName).From(u).Where(u.Id.IsEqualTo(1)).EndStatement();
var script = q.ToString();
Console.WriteLine(script);
TSqlAssert.ScriptsAreEqual(script, @"
SELECT
[dbo].[User].[Name]
FROM [dbo].[User]
WHERE
[dbo].[User].[Id] = @p0
");
}
public void SimpleCase()
{
var q = new SharpQuery();
SimpleCase c = new SimpleCase(1);
c.When(1, 2).When(3, 4).Else(5);
q.Select(c).EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, @"
SELECT
( CASE @p0
WHEN @p0 THEN @p1
WHEN @p2 THEN @p3
ELSE @p4
END )
");
}
public void SimpleCase()
{
var q = new SharpQuery();
SimpleCase c = new SimpleCase(1);
c.When(1, 2).When(3, 4).Else(5);
q.Select(c).EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, @"
SELECT
( CASE @p0
WHEN @p0 THEN @p1
WHEN @p2 THEN @p3
ELSE @p4
END )
");
}
public void RightOuterJoin()
{
var u = new UserTable();
var p = new PostTable();
var q = new SharpQuery();
q.Select(u.FirstName, p.Title).From(u).RightOuterJoin(p, p.UserId.IsEqualTo(u.Id)).EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, @"
SELECT
[dbo].[User].[Name]
, [dbo].[Post].[Title]FROM
[dbo].[User]RIGHT OUTER JOIN
[dbo].[Post] ON [dbo].[Post].[UserId] = [dbo].[User].[Id]
");
}
public void OrderBy()
{
var q = new SharpQuery();
var u = new UserTable();
q.Select(u.AllColumns).From(u).EndStatement();
q.OrderByAsc(u.Id, u.FirstName).OrderByDesc(u.Email).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT
[dbo].[User].[Id]
, [dbo].[User].[Name]
, [dbo].[User].[Email]
FROM [dbo].[User]
ORDER BY
[dbo].[User].[Id] ASC
, [dbo].[User].[Name] ASC
, [dbo].[User].[Email] DESC
");
}
public void SelectSingleColumnFromTable()
{
/*? <span class='title'>Example</span>
* **Description: Returns whether event.stopImmediatePropagation() was ever called on this event object.
* <span>This method does not accept any arguments.</span>
* Checks whether event.stopImmediatePropagation() was called.
*
*/
var u = new UserTable();
var q = new SharpQuery();
q.Select(u.FirstName).From(u).EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, @"
SELECT [dbo].[User].[Name]
FROM [dbo].[User]
");
}
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)
");
}
public void Test1()
{
var q = new SharpQuery();
var u = new UserTable();
var p = new PostTable();
q.Select(u.Id, Sql.Count(1))
.From(u)
.InnerJoin(p, p.UserId.IsEqualTo(u.Id))
.GroupBy(u.Id)
.EndStatement();
var query = q.ToString();
TSqlAssert.ScriptsAreEqual(query, @"
SELECT
[dbo].[User].[Id]
, COUNT( @p0 )
FROM [dbo].[User]
INNER JOIN [dbo].[Post] ON [dbo].[Post].[UserId] = [dbo].[User].[Id]
GROUP BY
[dbo].[User].[Id]");
}
public void SearchedCase()
{
var q = new SharpQuery();
var sc = new SearchedCase();
sc.CaseWhen(((Expression)1).IsEqualTo(1), 1)
.CaseWhen(((Expression)2).IsEqualTo(3), 4)
.Else(5);
q.Select(sc).EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, @"
SELECT
(CASE WHEN @p0 = @p0 THEN @p0
WHEN @p1 = @p2 THEN @p3
ELSE @p4
END)
");
}
public void EmptyQueryReturnsEmptyScript()
{
var q = new SharpQuery();
TSqlAssert.ScriptsAreEqual(string.Empty, q.ToString());
}
public void NotEqualTo()
{
var q = new SharpQuery();
q.Select(1).Where(ConstantExpression.GetConstant(1).IsNotEqualTo(2)).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE @p0 != @p1
");
}
public void NotExists()
{
var q = new SharpQuery();
var subQuery = q.Select(1).Statement;
q.Select(1).Where(Predicate.NotExists(subQuery)).EndStatement();
var actual = q.ToString();
TSqlAssert.ScriptsAreEqual(actual, @"
SELECT
@p0
WHERE NOT EXISTS( SELECT @p0 )
");
}
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 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 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 Like()
{
var q = new SharpQuery();
q.Select(1).Where(ConstantExpression.GetConstant(1).IsLike(1)).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE @p0 LIKE @p0
");
}
public void NotEndedStatementsShouldNotBeAddedToScript1()
{
var q = new SharpQuery();
q.Select(1);
TSqlAssert.ScriptsAreEqual(string.Empty, q.ToString());
}
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 IsNull()
{
var q = new SharpQuery();
q.Select(1).Where(ConstantExpression.NULL.IsNull()).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT @p0
WHERE NULL IS NULL
");
}
public void SimpleCase2()
{
var q = new SharpQuery();
q.Select(q.Case(1).When(1, 2).When(3, 4).When(5, 6).Else(7)).EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT
( CASE @p0
WHEN @p0 THEN @p1
WHEN @p2 THEN @p3
WHEN @p4 THEN @p5
ELSE @p6
END )
");
}
