public void TestWithExpressions()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithExpressionsClass>();
var where = QueryGroup.Parse(new
{
Field1 = "Test"
});
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending
});
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder, where, 0, 10, orderBy);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [TestWithExpressionsClass] " +
$"WHERE ([Field1] = @Field1) " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 1 AND 10) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateBatchQueryWithQuotedTableSchema()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "[dbo].[Table]";
var fields = Field.From("Field1", "Field2");
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending
});
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
page: 0,
rowsPerBatch: 10,
orderBy: orderBy,
where : null);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [dbo].[Table] " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 1 AND 10) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithFieldMappingsAndWithIgnoredBathQueryAndQueryCommand()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithFieldMappingsAndWithIgnoredBathQueryAndQueryCommandClass>();
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending
});
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder, null, 0, 10, orderBy);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field3] " +
$"FROM [ClassName] " +
$") " +
$"SELECT [Field1], [Field3] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 1 AND 10) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestWithMultipleOrderedColumnsAndWithoutAttributes()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestWithMultipleOrderedColumnsAndWithoutAttributesClass>();
var orderBy = OrderField.Parse(new
{
Field1 = Order.Descending,
Field2 = Order.Ascending
});
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder, null, 0, 10, orderBy);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] DESC, [Field2] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [TestWithMultipleOrderedColumnsAndWithoutAttributesClass] " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 1 AND 10) " +
$"ORDER BY [Field1] DESC, [Field2] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlDbProviderCreateBatchQueryWithTableHints()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <TestSqlDbProviderCreateBatchQueryWithTableHintsClass>();
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending
});
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder, null, 0, 10, orderBy, SqlTableHints.NoLock);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [ClassName] WITH (NOLOCK) " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 1 AND 10) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void ThrowExceptionIfAllFieldsWereIgnored()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <ThrowExceptionIfAllFieldsWereIgnoredClass>();
var queryGroup = (QueryGroup)null;
// Act/Assert
statementBuilder.CreateBatchQuery(queryBuilder, queryGroup, 0, 10, null);
}
public void ThrowExceptionIfThereAreNoQueryableFields()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder <ThrowExceptionIfThereAreNoQueryableFieldsClass>();
var queryGroup = (QueryGroup)null;
// Act/Assert
statementBuilder.CreateBatchQuery(queryBuilder, queryGroup, 0, 10, null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateBatchQueryIfThereAreNoOrderFields()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2");
// Act/Assert
statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
page: 0,
rowsPerBatch: 10,
orderBy: null,
where : null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateBatchQueryIfTheFieldsAreNull()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending
});
// Act/Assert
statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: null,
page: 0,
rowsPerBatch: 10,
orderBy: orderBy,
where : null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateBatchQueryIfTheRowsPerBatchIsLessThanOne()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2");
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending
});
// Act/Assert
statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
page: 0,
rowsPerBatch: 0,
orderBy: orderBy,
where : null);
}
public void TestSqlStatementBuilderCreateBatchQueryWithWhereExpressionUniqueField()
{
// Setup
var statementBuilder = new SqlStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From("Field1", "Field2");
var where = new QueryGroup(new QueryField("Id", Operation.NotEqual, 1));
var orderBy = OrderField.Parse(new
{
Field1 = Order.Ascending
});
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
page: 1,
rowsPerBatch: 10,
orderBy: orderBy,
where : where);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [Table] " +
$"WHERE ([Id] <> @Id) " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 11 AND 20) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}