public PagedResult<Product> GetProductsByType(ProductType type, int page, int itemsPerPage)
{
PagedResult<Product> result = new PagedResult<Product>();
result.PageNumber = page;
result.ItemsPerPage = itemsPerPage;
using (SqlConnection connection = new SqlConnection(this._connectionString))
{
string sql = @"Select * From VintageRabbit.Products Where [Type] = @Type Order By DateCreated Desc OFFSET @Offset ROWS FETCH NEXT @ResultsPerPage ROWS ONLY;
Select Count(*) From VintageRabbit.Products Where [Type] = @Type;";
int offset = (page - 1) * itemsPerPage;
using (var multi = connection.QueryMultiple(sql, new { Type = type.ToString(), Offset = offset, ResultsPerPage = itemsPerPage }))
{
var productResults = multi.Read<ProductDb>();
foreach (var product in productResults)
{
result.Add(this.ConvertToProduct(product));
}
result.TotalResults = multi.Read<int>().First();
}
}
return result;
}
public override PagedResult <TEntity, TKey> Select(int pageNumber, int pageSize,
IDbConnection connection,
Sort <TEntity, TKey> sorting,
Expression <Func <TEntity, bool> > expression = null,
IDbTransaction transaction = null)
{
WhereClauseBuildResult whereClauseBuildResult = expression != null?this.BuildWhereClause(expression) : null;
var sqlBuilder = new StringBuilder();
sqlBuilder.Append($"SELECT (SELECT COUNT(*) FROM {this.mapping.GetEscapedTableName<TEntity, TKey>(this.dialectSettings)}");
if (whereClauseBuildResult != null)
{
sqlBuilder.Append($" WHERE {whereClauseBuildResult.WhereClause} ");
}
sqlBuilder.AppendLine(") AS _TotalNumberOfRecords,");
sqlBuilder.AppendLine($" * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {this.BuildOrderByClause(sorting)}) AS _RowNumber, * FROM {this.mapping.GetEscapedTableName<TEntity, TKey>(this.dialectSettings)}");
if (whereClauseBuildResult != null)
{
sqlBuilder.AppendLine($" WHERE {whereClauseBuildResult.WhereClause} ");
}
sqlBuilder.AppendLine($") AS PagedResult WHERE _RowNumber >= {(pageNumber - 1) * pageSize + 1} AND _RowNumber < {pageNumber * pageSize + 1} ORDER BY _RowNumber");
var sql = sqlBuilder.ToString();
var pagedResult = new PagedResult <TEntity, TKey>
{
PageNumber = pageNumber,
PageSize = pageSize
};
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
if (transaction != null)
{
command.Transaction = transaction;
}
if (whereClauseBuildResult != null)
{
command.Parameters.Clear();
foreach (var kvp in whereClauseBuildResult.ParameterValues)
{
var param = command.CreateParameter();
param.ParameterName = kvp.Key;
param.Value = kvp.Value;
command.Parameters.Add(param);
}
}
using (var reader = command.ExecuteReader())
{
var totalNumOfRecordsRead = false;
while (reader.Read())
{
if (!totalNumOfRecordsRead)
{
pagedResult.TotalRecords = Convert.ToInt32(reader["_TotalNumberOfRecords"]);
pagedResult.TotalPages = (pagedResult.TotalRecords - 1) / pageSize + 1;
totalNumOfRecordsRead = true;
}
var entity = new TEntity();
typeof(TEntity)
.GetTypeInfo()
.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(p => p.CanWrite && p.PropertyType.IsSimpleType())
.ToList()
.ForEach(x =>
{
var value = reader[mapping.GetColumnName <TEntity, TKey>(x)];
x.SetValue(entity, EvaluatePropertyValue(x, value));
});
pagedResult.Add(entity);
}
reader.Close();
}
}
return(pagedResult);
}
public PagedResult<Product> GetProductsByCategory(ProductType type, Category category, int page, int itemsPerPage)
{
PagedResult<Product> result = new PagedResult<Product>();
result.PageNumber = page;
result.ItemsPerPage = itemsPerPage;
IList<int> categoryIds = new List<int>() { category.Id };
foreach(var child in category.Children)
{
categoryIds.Add(child.Id);
}
string sql = @"Select * From VintageRabbit.Products
Where [Type] = @Type And VintageRabbit.Products.Id In (Select Distinct ProductId From VintageRabbit.ProductCategories Where CategoryId In @CategoryIds)
Order By DateCreated Desc
OFFSET @Offset ROWS FETCH NEXT @ResultsPerPage ROWS ONLY;
Select Count(*) From VintageRabbit.Products Where [Type] = @Type And VintageRabbit.Products.Id In (Select Distinct ProductId From VintageRabbit.ProductCategories Where CategoryId In @CategoryIds);";
int offset = (page - 1) * itemsPerPage;
using (SqlConnection connection = new SqlConnection(this._connectionString))
{
using (var multi = connection.QueryMultiple(sql, new { CategoryIds = categoryIds, Type = type.ToString(), Offset = offset, ResultsPerPage = itemsPerPage }))
{
var productResults = multi.Read<ProductDb>();
foreach (var product in productResults)
{
result.Add(this.ConvertToProduct(product));
}
result.TotalResults = multi.Read<int>().First();
}
}
return result;
}
