public void TestSqlStatementBuilderCreateQueryWithWhereAndWithOrderByAndWithTopAndWithHints()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
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 = SqlTableHints.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 SqlStatementBuilder();
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 ThrowExceptionIfThereAreNoQueryableFields()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <ThrowExceptionIfThereAreNoQueryableFieldsClass>();
var queryGroup = (QueryGroup)null;
// Act/Assert
statementBuilder.CreateQuery(queryBuilder, queryGroup);
}
public void ThrowExceptionIfAllFieldsWereIgnored()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <ThrowExceptionIfAllFieldsWereIgnoredClass>();
var queryGroup = (QueryGroup)null;
// Act/Assert
statementBuilder.CreateQuery(queryBuilder, queryGroup);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryIfThereAreNoFields()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
// Act
statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryIfTheTableIsWhitespace()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = " ";
var fields = Field.From("Field1", "Field2", "Field3");
// Act
statementBuilder.CreateQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields);
}
public void ThrowExceptionOnSqlStatementBuilderCreateQueryIfTheOrderFieldIsNotCovered()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
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 TestSqlDbProviderCreateQueryWithTop()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestSqlDbProviderCreateQueryWithTopClass>();
var queryGroup = (QueryGroup)null;
// Act
var actual = statementBuilder.CreateQuery(queryBuilder, queryGroup, top: 10);
var expected = $"" +
$"SELECT TOP (10) [Field1], [Field2], [Field3] " +
$"FROM [TestSqlDbProviderCreateQueryWithTopClass] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithFieldMapping()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithFieldMappingClass>();
var queryGroup = (QueryGroup)null;
// Act
var actual = statementBuilder.CreateQuery(queryBuilder, queryGroup);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field4] " +
$"FROM [TestWithFieldMappingClass] ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlDbProviderCreateQueryWithTableHintViaSqlTableHints()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestSqlDbProviderCreateQueryWithTableHintClass>();
var queryGroup = (QueryGroup)null;
// Act
var actual = statementBuilder.CreateQuery(queryBuilder, queryGroup, null, null, SqlTableHints.ReadUncommitted);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [TestSqlDbProviderCreateQueryWithTableHintClass] {SqlTableHints.ReadUncommitted} ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlDbProviderCreateQueryWithTableHint()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestSqlDbProviderCreateQueryWithTableHintClass>();
var queryGroup = (QueryGroup)null;
// Act
var actual = statementBuilder.CreateQuery(queryBuilder, queryGroup, null, null, "WITH (INDEX(ANYINDEX), NOLOCK)");
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [TestSqlDbProviderCreateQueryWithTableHintClass] WITH (INDEX(ANYINDEX), NOLOCK) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateQueryWithUnquotedTableSchema()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
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 TestWithAnyFieldsAtExpression()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithAnyFieldsAtExpressionClass>();
var expression = new { AnyField = 1 };
var queryGroup = QueryGroup.Parse(expression);
// Act
var actual = statementBuilder.CreateQuery(queryBuilder, queryGroup);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [TestWithAnyFieldsAtExpressionClass] " +
$"WHERE ([AnyField] = @AnyField) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithDescendingOrderFields()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithDescendingOrderFieldsClass>();
var queryGroup = (QueryGroup)null;
var orderBy = OrderField.Parse(new { OrderField = Order.Descending });
// Act
var actual = statementBuilder.CreateQuery(queryBuilder, queryGroup, orderBy);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [TestWithDescendingOrderFieldsClass] " +
$"ORDER BY [OrderField] DESC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlDbProviderCreateQueryWithUnknownFieldAsPartOfWhereForDynamic()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestSqlDbProviderCreateQueryWithUnknownFieldAsPartOfWhereForDynamicClass>();
var expression = new { AnyField = 1 };
var queryGroup = QueryGroup.Parse(expression);
// Act
var actual = statementBuilder.CreateQuery(queryBuilder, queryGroup);
var expected = $"" +
$"SELECT [Field1], [Field2], [Field3] " +
$"FROM [TestSqlDbProviderCreateQueryWithUnknownFieldAsPartOfWhereForDynamicClass] " +
$"WHERE ([AnyField] = @AnyField) ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateQueryWithTop()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
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 TestSqlStatementBuilderCreateQueryWithHints()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2", "Field3");
var hints = SqlTableHints.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 TestSqlStatementBuilderCreateQueryWithWhereExpression()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
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);
}