public void ShouldCreateSelectWithWhereAndInnerJoinOrderByDescStatement()
{
var queryResult = FlepperQueryBuilder
.Select <UserDto>(
AsFrom <UserDto>("t1", column => new { column.Id }),
AsFrom <UserDto>("t1", column => new { column.Name }),
AsFrom <UserDto>("t1", column => new { column.Birthday })
)
.From("user").As("t1")
.InnerJoin("address").As("t2").On("t2", "column1").EqualTo("t1", "column2")
.Where("Name").EqualTo("Fabio")
.OrderByDescending("t1", "Name")
.ThenByDescending("t1", "Birthday")
.BuildWithParameters();
queryResult
.Query
.Trim()
.Should()
.Be("SELECT [t1].[Id],[t1].[Name],[t1].[Birthday] FROM [user] AS t1 INNER JOIN [address] AS t2 ON [t2].[column1] = [t1].[column2] WHERE [Name] = @p0 ORDER BY [t1].[Name] DESC, [t1].[Birthday] DESC");
dynamic parameters = queryResult.Parameters;
Assert.Equal("Fabio", parameters.@p0);
}
public void ShoulCreateUpdateCommandWithTableSchema()
{
FlepperQueryBuilder.Update("dbo", "table")
.Build()
.Trim()
.Should()
.Be("UPDATE [dbo].[table]");
}
public void ShouldCreateDeleteStatement()
{
FlepperQueryBuilder.Delete().From("Test")
.Build()
.Trim()
.Should()
.Be("DELETE FROM [Test]");
}
public void ShouldCreateInsertStatementWithTable()
{
FlepperQueryBuilder.Insert().Into("Test")
.Build()
.Trim()
.Should()
.Be("INSERT INTO [Test]");
}
public void ShouldCreateSelectTop1StatementForAllColumns()
{
FlepperQueryBuilder.Select().Top().From("user")
.Build()
.Trim()
.Should()
.Be("SELECT TOP 1 * FROM [user]");
}
public void ShoulCreateUpdateCommand()
{
FlepperQueryBuilder.Update("table")
.Build()
.Trim()
.Should()
.Be("UPDATE [table]");
}
public void ShouldContainsWhereInStatement()
{
FlepperQueryBuilder.Select()
.From("user").Where("name")
.Build()
.Trim()
.Should()
.Contain("WHERE");
}
public void ShouldCreateSelectStatementWithSchema()
{
FlepperQueryBuilder.Select()
.From("dbo", "user")
.Build()
.Trim()
.Should()
.Be("SELECT * FROM [dbo].[user]");
}
public void ShouldCreateSelectStatementWithSpecificColumns()
{
FlepperQueryBuilder.Select("Id", "Name", "Birthday")
.From("user")
.Build()
.Trim()
.Should()
.Be("SELECT [Id],[Name],[Birthday] FROM [user]");
}
public void ShoulContainWhereWithNotEqualToNull()
{
FlepperQueryBuilder.Select()
.From("table")
.Where("field").NotEqualTo <object>(null)
.Build()
.Trim()
.Should()
.Contain("WHERE [field] IS NOT NULL");
}
public void ShouldCreateSelectStatementWithOrderBy()
{
FlepperQueryBuilder
.Select <UserDto>(user => new { user.Id, user.Name })
.From("user")
.Build()
.Trim()
.Should()
.Be("SELECT [Id],[Name] FROM [user]");
}
public void ShouldCreateInsertStatementWithColumns()
{
FlepperQueryBuilder
.Insert().Into("Test")
.Columns("column1", "column2")
.Build()
.Trim()
.Should()
.Be("INSERT INTO [Test] ([column1],[column2] )");
}
public void ShoudCreateSelectWithColumnsOfType()
{
FlepperQueryBuilder
.Select <UserDto>()
.From("user")
.Build()
.Trim()
.Should()
.Be("SELECT [Id],[Name],[Birthday] FROM [user]");
}
public void ShouldReturnInnerJoinStatement()
{
FlepperQueryBuilder.Select()
.From("Table1").As("t1")
.InnerJoin("Table2").As("t2")
.Build()
.Trim()
.Should()
.Contain("INNER JOIN [Table2] t2");
}
public void ShouldCreateSelectStatementWithMultipleOrderByButItShouldOnlyBeDescending()
{
FlepperQueryBuilder.Select <UserDto>(user => new { user.Id, user.Name, user.Birthday })
.From("user")
.OrderBy("Name")
.ThenByDescending("Birthday")
.Build()
.Trim()
.Should()
.Be("SELECT [Id],[Name],[Birthday] FROM [user] ORDER BY [Name], [Birthday] DESC");
}
public void ShouldCreateSelectStatementWithOrderByDesc()
{
FlepperQueryBuilder
.Select()
.Top(1)
.From("dbo", "user")
.OrderByDescending("Birthday")
.Build()
.Trim()
.Should()
.Be("SELECT TOP 1 * FROM [dbo].[user] ORDER BY [Birthday] DESC");
}
public void ShouldReturnInnerJoinWithOnNotEqualStatement()
{
FlepperQueryBuilder.Select()
.From("Table1").As("t1")
.InnerJoin("Table2").As("t2")
.On("t2", "column1")
.NotEqualTo("t1", "column2")
.Build()
.Trim()
.Should()
.Contain("INNER JOIN [Table2] t2 ON t2.[column1] <> t1.[column2]");
}
public void ShouldReturnInnerJoinStatementWishTableAlias()
{
FlepperQueryBuilder
.Select(
AsFrom("t1", "c1"),
AsFrom("t2", "c1"))
.From("Table1").As("t1")
.InnerJoin("Table2").As("t2")
.On("t1", "c1").EqualTo("t2", "c1")
.Build()
.Trim()
.Should()
.Be("SELECT [t1].[c1],[t2].[c1] FROM [Table1] AS t1 INNER JOIN [Table2] AS t2 ON [t1].[c1] = [t2].[c1]");
}
public void ShouldCreateUpdateCommandWithColumnAndValue()
{
var queryResult = FlepperQueryBuilder.Update("dbo", "table")
.Set("column", "value")
.BuildWithParameters();
queryResult
.Query
.Trim()
.Should()
.Be("UPDATE [dbo].[table] SET [column] = @p0");
dynamic parameters = queryResult.Parameters;
Assert.Equal("value", parameters.@p0);
}
public void ShoulContainLikeEndsWith()
{
QueryResult result = FlepperQueryBuilder.Select()
.From("table")
.Where("field").EndsWith("abc")
.BuildWithParameters();
result.Query
.Trim()
.Should()
.Be("SELECT * FROM [table] WHERE [field] LIKE @p0");
dynamic parameters = result.Parameters;
Assert.Equal("abc%", parameters.@p0);
}
public void ShouldCreateDeleteStatementWithWhere()
{
var build = FlepperQueryBuilder.Delete()
.From("Test")
.Where("Id")
.EqualTo(2)
.BuildWithParameters();
build.Query
.Trim()
.Should()
.Be("DELETE FROM [Test] WHERE [Id] = @p0");
dynamic parameters = build.Parameters;
Assert.Equal(2, parameters.@p0);
}
public void ShouldCreteSelectWithWhereCondition()
{
var queryResult = FlepperQueryBuilder.Select("Id", "Name", "Birthday")
.From("user")
.Where("Name").EqualTo("Nicolas")
.BuildWithParameters();
queryResult
.Query
.Trim()
.Should()
.Be("SELECT [Id],[Name],[Birthday] FROM [user] WHERE [Name] = @p0");
dynamic parameters = queryResult.Parameters;
Assert.Equal("Nicolas", parameters.@p0);
}
[Fact] //ShoulContainWhereWithNotEqualTo()
public void ShoulContainWhereWithBetween()
{
QueryResult result = FlepperQueryBuilder.Select()
.From("table")
.Where("field").Between(10, 20)
.BuildWithParameters();
result.Query
.Trim()
.Should()
.Be("SELECT * FROM [table] WHERE [field] BETWEEN @p0 AND @p1");
dynamic parameters = result.Parameters;
Assert.Equal(10, parameters.@p0);
Assert.Equal(20, parameters.@p1);
}
public void ShouldCreateInsertStatementWithValues()
{
var queryResult = FlepperQueryBuilder
.Insert().Into("Test")
.Values("value1", 2)
.BuildWithParameters();
queryResult.Query
.Trim()
.Should()
.Be("INSERT INTO [Test] VALUES (@p0, @p1)");
dynamic parameters = queryResult.Parameters;
Assert.Equal("value1", parameters.@p0);
Assert.Equal(2, parameters.@p1);
}
public void ShoulContainWhereWithNotEqualTo()
{
var queryResult = FlepperQueryBuilder.Select()
.From("table")
.Where("field").NotEqualTo <int>(1)
.BuildWithParameters();
queryResult
.Query
.Trim()
.Should()
.Contain("WHERE [field] <> @p0");
dynamic parameters = queryResult.Parameters;
Assert.Equal(1, parameters.@p0);
}
public void ShouldCreateSelectBirthdayWithWhereAndOrderBy()
{
var queryResult = FlepperQueryBuilder.Select <UserDto>(user => new { user.Id, user.Name, user.Birthday })
.From("user")
.Where("Name").EqualTo("Fabio")
.OrderBy("Birthday")
.BuildWithParameters();
queryResult
.Query
.Trim()
.Should()
.Be("SELECT [Id],[Name],[Birthday] FROM [user] WHERE [Name] = @p0 ORDER BY [Birthday]");
dynamic parameters = queryResult.Parameters;
Assert.Equal("Fabio", parameters.@p0);
}
public void ShouldContainsWhereWithComparisonOperatos()
{
var queryResult = FlepperQueryBuilder.Select()
.From("user")
.Where("name")
.EqualTo("gustavo")
.BuildWithParameters();
queryResult
.Query
.Trim()
.Should()
.Contain("WHERE [name] = @p0");
dynamic parameters = queryResult.Parameters;
Assert.Equal("gustavo", parameters.@p0);
}
public void ShouldReturnInnerJoinWithWhereStatement()
{
var queryResult = FlepperQueryBuilder.Select()
.From("Table1").As("t1")
.InnerJoin("Table2").As("t2")
.On("t2", "column1")
.NotEqualTo("t1", "column2")
.Where("t1", "name").EqualTo("table")
.BuildWithParameters();
queryResult.Query
.Trim()
.Should()
.Contain("INNER JOIN [Table2] t2 ON t2.[column1] <> t1.[column2] WHERE t1.[name] = @p0");
dynamic parameters = queryResult.Parameters;
Assert.Equal("table", parameters.@p0);
}
public void ShoulContainLikeContainsAndStartsWithAndEndsWith()
{
QueryResult result = FlepperQueryBuilder.Select()
.From("table")
.Where("field1").Contains("abc1")
.And("field2").StartsWith("abc2")
.And("field3").EndsWith("abc3")
.BuildWithParameters();
result.Query
.Trim()
.Should()
.Be("SELECT * FROM [table] WHERE [field1] LIKE @p0 AND [field2] LIKE @p1 AND [field3] LIKE @p2");
dynamic parameters = result.Parameters;
Assert.Equal("%abc1%", parameters.@p0);
Assert.Equal("%abc2", parameters.@p1);
Assert.Equal("abc3%", parameters.@p2);
}
public void ShouldContainsWhereWithLogicalOperatorWithComparisonOperators()
{
var queryResult = FlepperQueryBuilder.Select()
.From("project")
.Where("name").EqualTo("flepper")
.And("age").GreaterThan(1)
.Or("age").EqualTo(5)
.And("csharpverson").GreaterThanOrEqualTo(7)
.And("highcomplexmethods").LessThan(10)
.And("highlocmethods").LessThanOrEqualTo(30)
.And("repositoryhostedon").EqualTo("github")
.And("active").NotEqualTo <bool>(false)
.BuildWithParameters();
queryResult
.Query
.Trim()
.Should()
.Be("SELECT * FROM [project] WHERE [name] = @p0 " +
"AND [age] > @p1 " +
"OR [age] = @p2 " +
"AND [csharpverson] >= @p3 " +
"AND [highcomplexmethods] < @p4 " +
"AND [highlocmethods] <= @p5 " +
"AND [repositoryhostedon] = @p6 " +
"AND [active] <> @p7");
dynamic parameters = queryResult.Parameters;
Assert.Equal("flepper", parameters.@p0);
Assert.Equal(1, parameters.@p1);
Assert.Equal(5, parameters.@p2);
Assert.Equal(7, parameters.@p3);
Assert.Equal(10, parameters.@p4);
Assert.Equal(30, parameters.@p5);
Assert.Equal("github", parameters.@p6);
Assert.Equal(false, parameters.@p7);
}
