public void TestSqlStatementBuilderCreateBatchQuerySecondBatch()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2" }, Helper.DbSetting);
var orderBy = OrderField.Parse(new { Field1 = Order.Ascending }, Helper.DbSetting);
// Act
var actual = statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
page: 1,
rowsPerBatch: 10,
orderBy: orderBy,
where : null);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [Table] " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 11 AND 20) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void TestSqlStatementBuilderCreateBatchQueryWithHints()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
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,
hints: SqlServerTableHints.NoLock);
var expected = $"" +
$"WITH CTE AS " +
$"( " +
$"SELECT ROW_NUMBER() OVER ( ORDER BY [Field1] ASC ) AS [RowNumber], [Field1], [Field2] " +
$"FROM [dbo].[Table] WITH (NOLOCK) " +
$") " +
$"SELECT [Field1], [Field2] " +
$"FROM CTE " +
$"WHERE ([RowNumber] BETWEEN 1 AND 10) " +
$"ORDER BY [Field1] ASC ;";
// Assert
Assert.AreEqual(expected, actual);
}
public void ThrowExceptionOnSqlStatementBuilderCreateBatchQueryIfThereAreNoOrderFields()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
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 SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var orderBy = OrderField.Parse(new { Field1 = Order.Ascending }, Helper.DbSetting);
// Act/Assert
statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: null,
page: 0,
rowsPerBatch: 10,
orderBy: orderBy,
where : null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateBatchQueryIfTheTableIsWhitespace()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = " ";
var fields = Field.From(new[] { "Field1", "Field2" }, Helper.DbSetting);
// Act/Assert
statementBuilder.CreateBatchQuery(queryBuilder: queryBuilder,
tableName: tableName,
fields: fields,
page: 0,
rowsPerBatch: 10,
orderBy: null,
where : null);
}
public void ThrowExceptionOnSqlStatementBuilderCreateBatchQueryIfTheRowsPerBatchIsLessThanOne()
{
// Setup
var statementBuilder = new SqlServerStatementBuilder();
var queryBuilder = new QueryBuilder();
var tableName = "Table";
var fields = Field.From(new[] { "Field1", "Field2" }, Helper.DbSetting);
var orderBy = OrderField.Parse(new { Field1 = Order.Ascending }, Helper.DbSetting);
// 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 SqlServerStatementBuilder();
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);
}
