public void Delete_NoExpressions_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.Delete("Table").GetSql();
// assert
result.Should().Be("delete from Table" + Environment.NewLine);
}
public void Join_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.Join().GetSql();
// assert
result.Should().Be(string.Empty);
}
public void Insert_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.Insert("Table").GetSql();
// assert
result.Should().Be("insert into Table" + Environment.NewLine);
}
public void From_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.From("TableA").GetSql();
// assert
result.Should().Be("from" + Environment.NewLine +
"\tTableA" + Environment.NewLine);
}
public void OrderBy_WithColumns_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.OrderBy("a", "b").GetSql();
// assert
result.Should().Be("order by" + Environment.NewLine +
"\ta," + Environment.NewLine +
"\tb" + Environment.NewLine);
}
public void Select_WithColumns_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.Select("Id", "Name").GetSql();
// assert
result.Should().Be("select" + Environment.NewLine +
"\tId," + Environment.NewLine +
"\tName" + Environment.NewLine);
}
public void Join_WithOneExpression_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.Join()
.Add("inner join TableB")
.GetSql();
// assert
result.Should().Be("\tinner join TableB" + Environment.NewLine);
}
public void SqlClauseBuilderSimpleWithConst()
{
// Arrange
const string expectedSql = "(Id = '5')";
Expression <Func <User, bool> > clause = u => u.Id == 5;
var sqlClauseBuilder = new SqlClauseBuilder();
// Act
string actualSql = sqlClauseBuilder.Build(clause.Body);
// Assert
Assert.Equal(expectedSql, actualSql);
}
public void CTE_WithInnerStatement_InitializesAsCorrectlyFormattedSql()
{
// arrange
var inner_statement = new SqlClause("select * from Table");
// act
var result = SqlClauseBuilder.CTE("X", inner_statement).GetSql();
// assert
result.Should().Be(";with X as (" + Environment.NewLine +
"\tselect * from Table" + Environment.NewLine +
")" + Environment.NewLine);
}
public void Predicate_WithOrLogic_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.Predicate("or")
.Add("a = 1")
.Add("b = 1")
.GetSql();
// assert
result.Should().Be("(a = 1) or (b = 1)");
}
public void SqlClauseBuilderPropertyIsNullConst()
{
// Arrange
string expectedSql = "(Name IS NULL)";
Expression <Func <User, bool> > clause = u => u.Name == null;
var sqlClauseBuilder = new SqlClauseBuilder();
// Act
string actualSql = sqlClauseBuilder.Build(clause.Body);
// Assert
Assert.Equal(expectedSql, actualSql);
}
public void SqlClauseBuilderNotProperty()
{
// Arrange
const string expectedSql = "(IsDisabled = 0)";
Expression <Func <User, bool> > clause = u => !u.IsDisabled;
var sqlClauseBuilder = new SqlClauseBuilder();
// Act
string actualSql = sqlClauseBuilder.Build(clause.Body);
// Assert
Assert.Equal(expectedSql, actualSql);
}
public void Predicate_ByDefault_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.Predicate()
.Add("a = 1")
.Add("b = 1")
.GetSql();
// assert
result.Should().Be("(a = 1) and (b = 1)");
}
public void SqlClauseBuilderBooleanClauseWithConst()
{
// Arrange
const string expectedSql = "(IsDisabled != 1)";
Expression <Func <User, bool> > clause = u => u.IsDisabled != true;
var sqlClauseBuilder = new SqlClauseBuilder();
// Act
string actualSql = sqlClauseBuilder.Build(clause.Body);
// Assert
Assert.Equal(expectedSql, actualSql);
}
public void SqlClauseBuilderOrClause()
{
// Arrange
const int id = 42;
string expectedSql = $"((Id = '{id}') OR (Name = 'some Value'))";
Expression <Func <User, bool> > clause = u => u.Id == id || u.Name == "some Value";
var sqlClauseBuilder = new SqlClauseBuilder();
// Act
string actualSql = sqlClauseBuilder.Build(clause.Body);
// Assert
Assert.Equal(expectedSql, actualSql);
}
public void SqlClauseBuilderNotExpression()
{
// Arrange
const int id = 42;
string expectedSql = $"(NOT (Id > '{id}'))";
Expression <Func <User, bool> > clause = u => !(u.Id > id);
var sqlClauseBuilder = new SqlClauseBuilder();
// Act
string actualSql = sqlClauseBuilder.Build(clause.Body);
// Assert
Assert.Equal(expectedSql, actualSql);
}
public void SqlClauseBuilderNotClause()
{
// Arrange
int id = 42;
string expectedSql = $"(Id != '{id}')";
Expression <Func <User, bool> > clause = u => u.Id != id;
var sqlClauseBuilder = new SqlClauseBuilder();
// Act
string actualSql = sqlClauseBuilder.Build(clause.Body);
// Assert
Assert.Equal(expectedSql, actualSql);
}
public void SqlClauseBuilderSimpleWithVaue()
{
// Arrange
string name = "some Name";
string expectedSql = $"(Name = '{name}')";
Expression <Func <User, bool> > clause = u => u.Name == name;
var sqlClauseBuilder = new SqlClauseBuilder();
// Act
string actualSql = sqlClauseBuilder.Build(clause.Body);
// Assert
Assert.Equal(expectedSql, actualSql);
}
public void CTE_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.CTE("X")
.Add("select * from Table")
.GetSql();
// assert
result.Should().Be(";with X as (" + Environment.NewLine +
"\tselect * from Table" + Environment.NewLine +
")" + Environment.NewLine);
}
public void SqlClauseBuilderSimpleWithConvertFromEnum()
{
// Arrange
var userType = UserType.Reader;
string expectedSql = $"(Type = {(byte)UserType.Reader})";
Expression <Func <User, bool> > clause = u => u.Type == userType;
var sqlClauseBuilder = new SqlClauseBuilder();
// Act
string actualSql = sqlClauseBuilder.Build(clause.Body);
// Assert
Assert.Equal(expectedSql, actualSql);
}
public void SqlClauseBuilderBooleanClauseWithValue()
{
// Arrange
const bool isDisabled = false;
string expectedSql = $"(IsDisabled != {(isDisabled ? "1" : "0")})";
Expression <Func <User, bool> > clause = u => u.IsDisabled != isDisabled;
var sqlClauseBuilder = new SqlClauseBuilder();
// Act
string actualSql = sqlClauseBuilder.Build(clause.Body);
// Assert
Assert.Equal(expectedSql, actualSql);
}
public void GroupBy_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.GroupBy()
.Add("a")
.Add("b")
.GetSql();
// assert
result.Should().Be("group by" + Environment.NewLine +
"\ta," + Environment.NewLine +
"\tb" + Environment.NewLine);
}
public void Insert_WithColumns_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.Insert("Table", "Id", "Name").GetSql();
// assert
result.Should().Be("insert into Table" + Environment.NewLine +
"(" + Environment.NewLine +
"\tId," + Environment.NewLine +
"\tName" + Environment.NewLine +
")" + Environment.NewLine +
Environment.NewLine);
}
public void Having_WithOrLogic_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.Having("or")
.Add("a = 1")
.Add("b = 1")
.GetSql();
// assert
result.Should().Be("having" + Environment.NewLine +
"\t(a = 1)" + Environment.NewLine +
"\tor (b = 1)" + Environment.NewLine);
}
public void SqlClauseBuilderAndOrClause()
{
// Arrange
const int id = 42;
var now = DateTime.Now;
string expectedSql = $"((Id = '{id}') OR ((Name = 'some Value') AND (Created < '{now.ToString("yyyy.MM.dd HH:mm:ss")}')))";
Expression <Func <User, bool> > clause = u => u.Id == id || (u.Name == "some Value" && u.Created < now);
var sqlClauseBuilder = new SqlClauseBuilder();
// Act
string actualSql = sqlClauseBuilder.Build(clause.Body);
// Assert
Assert.Equal(expectedSql, actualSql);
}
public void Where_ByDefault_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.Where()
.Add("a = 1")
.Add("b = 1")
.GetSql();
// assert
result.Should().Be("where" + Environment.NewLine +
"\t(a = 1)" + Environment.NewLine +
"\tand (b = 1)" + Environment.NewLine);
}
public void InsertColumns_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.InsertColumns()
.Add("Id")
.Add("Name")
.GetSql();
// assert
result.Should().Be("(" + Environment.NewLine +
"\tId," + Environment.NewLine +
"\tName" + Environment.NewLine +
")" + Environment.NewLine);
}
public void Update_InitializesAsCorrectlyFormattedSql()
{
// arrange
// act
var result = SqlClauseBuilder.Update("Table")
.Add("A = 1")
.Add("B = 2")
.GetSql();
// assert
result.Should().Be("update Table" + Environment.NewLine +
"set" + Environment.NewLine +
"\tA = 1," + Environment.NewLine +
"\tB = 2" + Environment.NewLine);
}
public void AddNotExists_WhereClause_AddsCorrectPredicate()
{
// arrange
var sut = SqlClauseBuilder.Where();
var parameter = new SqlParameter {
ParameterName = "@x"
};
var clause = new SqlClause("select * from Table2");
clause.Add(parameter);
// act
sut.AddNotExists(clause);
var sql = sut.GetSql();
var parameters = sut.GetParameters();
// assert
sql.Should().BeEquivalentToSql("where (not exists (select * from Table2))");
parameters.Should().Equal(parameter);
}