public void TestSqlStatementBuilderCreateQueryWithWhereAndWithOrderByAndWithTopAndWithHints()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var where = new QueryGroup(new QueryField("Id", 1));
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending,
Field2 = Order.Descending
});
var top = 100;
var hints = SqlServerTableHints.NoLock;
// Act
var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
where : where,
orderBy: orderBy,
top: top,
hints: hints);
var expected = $"" +
$"SELECT TOP (100) [Field1], [Field2], [Field3] " +
$"FROM [Table] WITH (NOLOCK) " +
$"WHERE ([Id] = @Id) " +
$"ORDER BY [Field1] ASC, [Field2] DESC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateQueryWithOrderBy()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending,
Field2 = Order.Descending
});
// Act
var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
orderBy: orderBy);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [Table] " +
$"ORDER BY [Field1] ASC, [Field2] DESC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryIfThereAreNoFields()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
// Act
statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryIfTheTableIsWhitespace()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = " ";
var fields = Field.From("Field1", "Field2", "Field3");
// Act
statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryIfTheTableIsEmpty()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" }, Helper.DbSetting);
// Act
statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryIfTheOrderFieldIsNotCovered()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var orderBy = OrderField.Parse(new { Id = Order.Ascending, Field1 = Order.Ascending });
// Act
statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
orderBy: orderBy);
}
public void TestSqlStatementBuilderCreateQueryWithUnquotedTableSchema()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "dbo.Table";
var fields = Field.From("Field1", "Field2", "Field3");
// Act
var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields);
var expected = "SELECT [Field1], [Field2], [Field3] FROM [dbo].[Table] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateQuery()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2", "Field3" }, Helper.DbSetting);
// Act
var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields);
var expected = "SELECT [Field1], [Field2], [Field3] FROM [Table] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateQueryWithHints()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var hints = SqlServerTableHints.NoLock;
// Act
var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
hints: hints);
var expected = "SELECT [Field1], [Field2], [Field3] FROM [Table] WITH (NOLOCK) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateQueryWithTop()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var top = 100;
// Act
var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
top: top);
var expected = "SELECT TOP (100) [Field1], [Field2], [Field3] FROM [Table] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateQueryWithWhereExpression()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var where = new QueryGroup(new QueryField("Id", 1));
// Act
var actual = statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
where : where);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [Table] " +
$"WHERE ([Id] = @Id) ;";
// Assert
Assert.AreEqual(expected, actual);
}
