public PageQuery ( SqlQuery sqlQuery, PagingOptions pagingOptions ) : SqlQuery | ||
sqlQuery | SqlQuery | |
pagingOptions | PagingOptions | |
return | SqlQuery |
public void PageWithWhereAndOrderBySecondResultsPage() { var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE CustomerStatusId = @p0 ORDER BY Name ASC", CustomerStatus.Active); var sqlDialect = new SqlServerCeDialect(); 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(25, paged.Arguments[1]); Assert.Equal(25, paged.Arguments[2]); }
public void PageNonQualifiedQuery() { var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers ORDER BY CustomerId"); var sqlDialect = new SqlServerCeDialect(); 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(0, paged.Arguments[0]); Assert.Equal(25, paged.Arguments[1]); }
public void PageWithMultiWhereAndMultiOrderByMultiLine() { var sqlQuery = new SqlQuery(@"SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE (CustomerStatusId = @p0 AND DoB > @p1) ORDER BY Name ASC, DoB ASC", new object[] { CustomerStatus.Active, new DateTime(1980, 01, 01) }); var sqlDialect = new SqlServerCeDialect(); var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25)); Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE (CustomerStatusId = @p0 AND DoB > @p1) ORDER BY Name ASC, DoB ASC OFFSET @p2 ROWS FETCH NEXT @p3 ROWS ONLY", paged.CommandText); Assert.Equal(sqlQuery.Arguments[0], paged.Arguments[0]); Assert.Equal(sqlQuery.Arguments[1], paged.Arguments[1]); Assert.Equal(0, paged.Arguments[2]); Assert.Equal(25, paged.Arguments[3]); }
public void PageThrowsMicroLiteExceptionIfNoOrderByClause() { var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers"); var sqlDialect = new SqlServerCeDialect(); var exception = Assert.Throws<MicroLiteException>( () => sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25))); Assert.Equal(ExceptionMessages.SqlServerCeDialect_PagedRequiresOrderBy, exception.Message); }
public void PageQueryThrowsArgumentNullExceptionForNullSqlCharacters() { var sqlDialect = new SqlServerCeDialect(); var exception = Assert.Throws<ArgumentNullException>( () => sqlDialect.PageQuery(null, PagingOptions.None)); }
public void PageWithNoWhereButOrderBy() { var sqlDialect = new SqlServerCeDialect(); 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 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 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); }