public void PageWithNoWhereButOrderBy() { var sqlDialect = new MySqlDialect(); var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers ORDER BY CustomerId ASC"); var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25)); Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers ORDER BY CustomerId ASC LIMIT @p0,@p1", paged.CommandText); Assert.Equal(DbType.Int32, paged.Arguments[0].DbType); Assert.Equal(0, paged.Arguments[0].Value); Assert.Equal(DbType.Int32, paged.Arguments[1].DbType); Assert.Equal(25, paged.Arguments[1].Value); }
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 MicroLiteRowNumber FROM [Sales].[Customers]) AS [MicroLitePagedResults] WHERE (MicroLiteRowNumber >= @p0 AND MicroLiteRowNumber <= @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 PageNonQualifiedWildcardQuery() { var sqlDialect = new FirebirdSqlDialect(); var sqlQuery = new SqlQuery("SELECT * FROM Customers"); var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25)); Assert.Equal("SELECT * FROM Customers ROWS @p0 TO @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 PageWithJoin() { var sqlDialect = new MsSql2005Dialect(); var sqlQuery = new SqlQuery("SELECT [Customers].[CustomerId], [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], [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 PageWithNoWhereOrOrderBySecondResultsPage() { var sqlDialect = new FirebirdSqlDialect(); var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers"); var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 2, resultsPerPage: 25)); Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers ROWS @p0 TO @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 PageNonQualifiedWildcardQuery() { var sqlDialect = new SqlServerCeDialect(); var sqlQuery = new SqlQuery("SELECT * FROM Customers ORDER BY CustomerId"); var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25)); Assert.Equal("SELECT * FROM Customers ORDER BY CustomerId OFFSET @p0 ROWS FETCH NEXT @p1 ROWS ONLY", paged.CommandText); Assert.Equal(DbType.Int32, paged.Arguments[0].DbType); Assert.Equal(0, paged.Arguments[0].Value); Assert.Equal(DbType.Int32, paged.Arguments[1].DbType); Assert.Equal(25, paged.Arguments[1].Value); }
public void PageNonQualifiedQuery() { var sqlDialect = new MsSql2012Dialect(); var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers ORDER BY CustomerId"); var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25)); Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers ORDER BY CustomerId OFFSET @p0 ROWS FETCH NEXT @p1 ROWS ONLY", paged.CommandText); Assert.Equal(DbType.Int32, paged.Arguments[0].DbType); Assert.Equal(0, paged.Arguments[0].Value); Assert.Equal(DbType.Int32, paged.Arguments[1].DbType); Assert.Equal(25, paged.Arguments[1].Value); }
public void PageAppendsOrderByGetDateIfNoOrderByClause() { var sqlDialect = new SqlServerCeDialect(); var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers"); var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25)); Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers ORDER BY GETDATE() OFFSET @p0 ROWS FETCH NEXT @p1 ROWS ONLY", paged.CommandText); Assert.Equal(DbType.Int32, paged.Arguments[0].DbType); Assert.Equal(0, paged.Arguments[0].Value); Assert.Equal(DbType.Int32, paged.Arguments[1].DbType); Assert.Equal(25, paged.Arguments[1].Value); }
public void PageNonQualifiedQuery() { var sqlDialect = new SQLiteDialect(); var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers"); var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25)); Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers LIMIT @p0,@p1", paged.CommandText); Assert.Equal(DbType.Int32, paged.Arguments[0].DbType); Assert.Equal(0, paged.Arguments[0].Value); Assert.Equal(DbType.Int32, paged.Arguments[1].DbType); Assert.Equal(25, paged.Arguments[1].Value); }
public void PagedThrowsObjectDisposedExceptionIfDisposed() { var session = new AsyncReadOnlySession( ConnectionScope.PerTransaction, new Mock <ISqlDialect>().Object, new Mock <IDbDriver>().Object); using (session) { } var exception = Assert.Throws <AggregateException>( () => session.PagedAsync <Customer>(null, PagingOptions.ForPage(1, 25)).Result); Assert.IsType <ObjectDisposedException>(exception.InnerException); }
public void PageWithWhereButNoOrderBy() { var sqlDialect = new PostgreSqlDialect(); var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE CustomerStatusId = @p0", CustomerStatus.Active); var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25)); Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE CustomerStatusId = @p0 LIMIT @p1 OFFSET @p2", paged.CommandText); Assert.Equal(sqlQuery.Arguments[0], paged.Arguments[0]); Assert.Equal(DbType.Int32, paged.Arguments[1].DbType); Assert.Equal(25, paged.Arguments[1].Value); Assert.Equal(DbType.Int32, paged.Arguments[2].DbType); Assert.Equal(0, paged.Arguments[2].Value); }
public void PageWithWhereButNoOrderBy() { var sqlDialect = new MsSql2005Dialect(); var sqlQuery = new SqlQuery("SELECT [Customers].[CustomerId], [Customers].[Name], [Customers].[DoB], [Customers].[StatusId] FROM [Sales].[Customers] WHERE [Customers].[StatusId] = @p0", 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 (SELECT NULL)) 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 PageWithWhereAndOrderBy() { var sqlDialect = new FirebirdSqlDialect(); var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE CustomerStatusId = @p0 ORDER BY Name ASC", CustomerStatus.Active); var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25)); Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE CustomerStatusId = @p0 ORDER BY Name ASC ROWS @p1 TO @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 PageWithWhereAndOrderBySecondResultsPage() { var sqlDialect = new SqlServerCeDialect(); var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE CustomerStatusId = @p0 ORDER BY Name ASC", CustomerStatus.Active); var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 2, resultsPerPage: 25)); Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE CustomerStatusId = @p0 ORDER BY Name ASC OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY", paged.CommandText); Assert.Equal(sqlQuery.Arguments[0], paged.Arguments[0]); Assert.Equal(DbType.Int32, paged.Arguments[1].DbType); Assert.Equal(25, paged.Arguments[1].Value); Assert.Equal(DbType.Int32, paged.Arguments[2].DbType); Assert.Equal(25, paged.Arguments[2].Value); }
public void PageQueryWithoutSubQuery() { var sqlDialect = new MsSql2005Dialect(); SqlCharacters.Current = MsSqlCharacters.Instance; var sqlQuery = SqlBuilder .Select("*").From(typeof(Customer)) .Where("Name LIKE @p0", "Fred%") .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)) AS [MicroLitePagedResults] WHERE (RowNumber >= @p1 AND RowNumber <= @p2)", 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(11, paged.Arguments[1].Value); Assert.Equal(DbType.Int32, paged.Arguments[2].DbType); Assert.Equal(20, paged.Arguments[2].Value); }
public WhenCallingForPageForTheSecondPage() { pagingOptions = PagingOptions.ForPage(this.page, this.resultsPerPage); }