public PagingSql PageQuery <TEntity>(long skip, long take, string orderByQuery = null, string filterQuery = null) where TEntity : class { var tableInfo = GetTableInfo <TEntity>(); var tableName = tableInfo.TableName; var primaryKey = tableInfo.Key; if (string.IsNullOrWhiteSpace(orderByQuery)) { orderByQuery = $"ORDER BY {primaryKey}"; } var pagingSql = new PagingSql(); if (string.IsNullOrWhiteSpace(filterQuery)) { pagingSql.Items = $"SELECT * FROM {tableName} {orderByQuery} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY"; pagingSql.Count = $"SELECT CAST(COUNT(*) AS Int) AS total FROM {tableName}"; } else { pagingSql.Items = $"SELECT DISTINCT {tableName}.* FROM {tableName} {filterQuery} {orderByQuery} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY"; pagingSql.Count = $"SELECT CAST(COUNT(DISTINCT {tableName}.{primaryKey}) AS Int) AS total FROM {tableName} {filterQuery}"; } return(pagingSql); }
public PagingSql PageQuery <TEntity>(long skip, long take, string orderByQuery = null, string filterQuery = null) where TEntity : class { TableInfo tableInfo = GetTableInfo <TEntity>(); if (string.IsNullOrWhiteSpace(orderByQuery)) { orderByQuery = string.Format("ORDER BY {0}", tableInfo.Key); } var pagingSql = new PagingSql(); if (string.IsNullOrWhiteSpace(filterQuery)) { pagingSql.Items = string.Format("SELECT * FROM {0} {1} OFFSET {2} ROWS FETCH NEXT {3} ROWS ONLY", tableInfo.TableName, orderByQuery, skip, take); pagingSql.Count = string.Format("SELECT CAST(COUNT(*) AS Int) AS total FROM {0}", tableInfo.TableName); } else { pagingSql.Items = string.Format("SELECT DISTINCT {0}.* FROM {0} {1} {2} OFFSET {3} ROWS FETCH NEXT {4} ROWS ONLY", tableInfo.TableName, filterQuery, orderByQuery, skip, take); pagingSql.Count = string.Format("SELECT CAST(COUNT(DISTINCT TestTable.Id) AS Int) AS total FROM {0} {1}", tableInfo.TableName, filterQuery); } return(pagingSql); }
public void PageQuery_FilterBySpecified_PassedFilterQueryIsUsed() { const string expectedItemsSql = "SELECT DISTINCT TestTable.* FROM TestTable WHERE BY Name like 'J%' ORDER BY Id OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY"; const string expectedCountSql = "SELECT CAST(COUNT(DISTINCT TestTable.Id) AS Int) AS total FROM TestTable WHERE BY Name like 'J%'"; IQueryBuilder queryBuilder = GetQueryBuilder(); PagingSql pagingSql = queryBuilder.PageQuery <TestEntityWithAutoIncreamentId>(2, 5, filterQuery: "WHERE BY Name like 'J%'"); Assert.AreEqual(expectedItemsSql, ReplaceNextLineAndTab(pagingSql.Items)); Assert.AreEqual(expectedCountSql, ReplaceNextLineAndTab(pagingSql.Count)); }
public void PageQuery_OrderBySpecified_PassedOrderByIsUsed() { const string expectedItemsSql = "SELECT * FROM TestTable ORDER BY Name OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY"; const string expectedCountSql = "SELECT CAST(COUNT(*) AS Int) AS total FROM TestTable"; IQueryBuilder queryBuilder = GetQueryBuilder(); PagingSql pagingSql = queryBuilder.PageQuery <TestEntityWithAutoIncreamentId>(2, 5, orderByQuery: "ORDER BY Name"); Assert.AreEqual(expectedItemsSql, ReplaceNextLineAndTab(pagingSql.Items)); Assert.AreEqual(expectedCountSql, ReplaceNextLineAndTab(pagingSql.Count)); }