private List<Product> GetProductsByCriteria(int? categoryId = null, int? subCategoryId = null, int page = 1, int? productId = null)
{
if (page <= 0)
page = 1;
CategoryListItem nextCategory;
ProductsCriteria criteria = new ProductsCriteria()
{
PageSize = 20,
PageNumber = page,
CategoryId = categoryId,
SubCategoryId = subCategoryId,
ProductId = productId
};
List<Product> products = DataProvider.GetProductsByCriteria(criteria, out nextCategory);
if (nextCategory != null)
{
ViewData["NextCategory"] = nextCategory;
}
ViewData["CategoriesTags"] = DataProvider.GetCategoriesTags();
ViewData["Category"] = categoryId != null ? categoryId.Value : -1;
ViewData["SubCategory"] = subCategoryId != null ? subCategoryId.Value : -1;
ViewData["PageNumber"] = page;
return products;
}
public static List<Product> GetProductsByCriteria(ProductsCriteria criteria, out CategoryListItem nextCategory)
{
nextCategory = null;
List<Product> products = new List<Product>();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[CONNECTION_STRING].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("GetProductsByCriteria", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
if (criteria.CategoryId != null)
{
cmd.Parameters.Add(new SqlParameter("@MainCat", criteria.CategoryId.Value));
}
cmd.Parameters.Add(new SqlParameter("@PageNumber", criteria.PageNumber));
cmd.Parameters.Add(new SqlParameter("@PageSize", criteria.PageSize));
if (criteria.ProductId != null && criteria.ProductId.Value > 0)
{
cmd.Parameters.Add(new SqlParameter("@ProductId", criteria.ProductId.Value));
}
conn.Open();
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Product p = new Product()
{
Id = reader.GetInt32(0),
Name = reader.GetString(3),
ImageName = reader.GetString(1),
SmallImageName = reader.GetString(5),
CategoryId = reader.GetInt32(6),
SubCategoryId = reader.GetInt32(2),
CategoryName = reader.GetString(7),
SubCategoryName = reader.IsDBNull(4) ? string.Empty : reader.GetString(4),
PreviousProductId = reader.IsDBNull(12) ? null : (int?)reader.GetInt32(12),
NextProductId = reader.IsDBNull(13) ? null : (int?)reader.GetInt32(13),
};
products.Add(p);
}
if (criteria.CategoryId != null && reader.NextResult())
{
if (reader.Read())
{
nextCategory = new CategoryListItem()
{
Id = reader.GetInt32(0),
Name = reader.GetString(1)
};
}
}
}
conn.Close();
}
}
return products;
}