public void PageQueryWithSubQuery()
{
var sqlDialect = new MsSql2005Dialect();
SqlCharacters.Current = MsSqlCharacters.Instance;
var sqlQuery = SqlBuilder
.Select("*")
.From(typeof(Customer))
.Where("Name LIKE @p0", "Fred%")
.AndWhere("SourceId").In(new SqlQuery("SELECT SourceId FROM Source WHERE Status = @p0", 1))
.ToSqlQuery();
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 2, resultsPerPage: 10));
Assert.Equal("SELECT * FROM (SELECT [Created],[CreditLimit],[DateOfBirth],[Id],[Name],[CustomerStatusId],[Updated],[Website],ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber FROM [Customers] WHERE (Name LIKE @p0) AND ([SourceId] IN (SELECT SourceId FROM Source WHERE Status = @p1))) AS [MicroLitePagedResults] WHERE (RowNumber >= @p2 AND RowNumber <= @p3)", paged.CommandText);
Assert.Equal(DbType.String, paged.Arguments[0].DbType);
Assert.Equal("Fred%", paged.Arguments[0].Value);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(1, paged.Arguments[1].Value);
Assert.Equal(DbType.Int32, paged.Arguments[2].DbType);
Assert.Equal(11, paged.Arguments[2].Value);
Assert.Equal(DbType.Int32, paged.Arguments[3].DbType);
Assert.Equal(20, paged.Arguments[3].Value);
}
public void PageWithWhereAndOrderByMultiLine()
{
var sqlDialect = new MsSql2005Dialect();
var sqlQuery = new SqlQuery(@"SELECT
[Customers].[CustomerId],
[Customers].[Name],
[Customers].[DoB],
[Customers].[StatusId]
FROM
[Sales].[Customers]
WHERE
[Customers].[StatusId] = @p0
ORDER BY
[Customers].[Name] ASC", CustomerStatus.Active);
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT * FROM (SELECT [Customers].[CustomerId], [Customers].[Name], [Customers].[DoB], [Customers].[StatusId],ROW_NUMBER() OVER(ORDER BY [Customers].[Name] ASC) AS RowNumber FROM [Sales].[Customers] WHERE [Customers].[StatusId] = @p0) AS [MicroLitePagedResults] WHERE (RowNumber >= @p1 AND RowNumber <= @p2)", paged.CommandText);
Assert.Equal(sqlQuery.Arguments[0], paged.Arguments[0]);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(1, paged.Arguments[1].Value);
Assert.Equal(DbType.Int32, paged.Arguments[2].DbType);
Assert.Equal(25, paged.Arguments[2].Value);
}
public void PageQueryThrowsArgumentNullExceptionIfSqlQueryIsNull()
{
var sqlDialect = new MsSql2005Dialect();
var exception = Assert.Throws <ArgumentNullException>(
() => sqlDialect.PageQuery(null, PagingOptions.ForPage(1, 10)));
Assert.Equal("sqlQuery", exception.ParamName);
}
public void PageWithNoWhereOrOrderBySecondResultsPage()
{
var sqlDialect = new MsSql2005Dialect();
var sqlQuery = new SqlQuery("SELECT [Customers].[CustomerId], [Customers].[Name], [Customers].[DoB], [Customers].[StatusId] FROM [Sales].[Customers]");
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 2, resultsPerPage: 25));
Assert.Equal("SELECT * FROM (SELECT [Customers].[CustomerId], [Customers].[Name], [Customers].[DoB], [Customers].[StatusId],ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber FROM [Sales].[Customers]) AS [MicroLitePagedResults] WHERE (RowNumber >= @p0 AND RowNumber <= @p1)", paged.CommandText);
Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
Assert.Equal(26, paged.Arguments[0].Value);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(50, paged.Arguments[1].Value);
}
public void PageWithJoinWithAlias()
{
var sqlDialect = new MsSql2005Dialect();
var sqlQuery = new SqlQuery("SELECT [Customers].[CustomerId] AS CustId, [Invoices].[InvoiceId] FROM [Customers] INNER JOIN [Invoices] ON [Invoices].[CustomerId] = [Customers].[CustomerId]");
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT * FROM (SELECT [Customers].[CustomerId] AS CustId, [Invoices].[InvoiceId],ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber FROM [Customers] INNER JOIN [Invoices] ON [Invoices].[CustomerId] = [Customers].[CustomerId]) AS [MicroLitePagedResults] WHERE (RowNumber >= @p0 AND RowNumber <= @p1)", paged.CommandText);
Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
Assert.Equal(1, paged.Arguments[0].Value);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(25, paged.Arguments[1].Value);
}
public void PageNonQualifiedWildcardQuery()
{
var sqlDialect = new MsSql2005Dialect();
var sqlQuery = new SqlQuery("SELECT * FROM Customers");
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber FROM Customers) AS [MicroLitePagedResults] WHERE (RowNumber >= @p0 AND RowNumber <= @p1)", paged.CommandText);
Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
Assert.Equal(1, paged.Arguments[0].Value);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(25, paged.Arguments[1].Value);
}
public void PageWithNoWhereButOrderBy()
{
var sqlDialect = new MsSql2005Dialect();
var sqlQuery = new SqlQuery("SELECT [CustomerId], [Name], [DoB], [StatusId] FROM [dbo].[Customers] ORDER BY [CustomerId] ASC");
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT * FROM (SELECT [CustomerId], [Name], [DoB], [StatusId],ROW_NUMBER() OVER(ORDER BY [CustomerId] ASC) AS MicroLiteRowNumber FROM [dbo].[Customers]) AS [MicroLitePagedResults] WHERE (MicroLiteRowNumber >= @p0 AND MicroLiteRowNumber <= @p1)", paged.CommandText);
Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
Assert.Equal(1, paged.Arguments[0].Value);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(25, paged.Arguments[1].Value);
}