public JsonResult GetProducts(FilterCriteria model)
{
return(Json(helper.GetProducts(model), JsonRequestBehavior.AllowGet));
}
public List <Product> GetProducts(FilterCriteria model)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
using (SqlCommand command = new SqlCommand())
{
try
{
connection.Open();
//add filter here.
string where = string.Empty;
if (model != null && (!string.IsNullOrEmpty(model.Grape) || !string.IsNullOrEmpty(model.ProductCountry)))
{
if (!string.IsNullOrEmpty(model.Grape) && model.Grape.ToLower() != "all")
{
where += "Grape = '" + model.Grape + "'";
}
if (!string.IsNullOrEmpty(model.ProductCountry) && model.ProductCountry.ToLower() != "all")
{
where += "ProductCountry = '" + model.ProductCountry + "'";
}
}
command.CommandText = "SELECT * FROM Products" + (string.IsNullOrEmpty(where) ? string.Empty : " WHERE " + where);
command.Connection = connection;
using (SqlDataReader reader = command.ExecuteReader())
{
List <Product> products = new List <Product>();
int productId = reader.GetOrdinal("ProductID");
int ProductName = reader.GetOrdinal("ProductName");
int ProductCountry = reader.GetOrdinal("ProductCountry");
int ProductType = reader.GetOrdinal("ProductType");
int ProductDescription = reader.GetOrdinal("ProductDescription");
int Grape = reader.GetOrdinal("Grape");
int ImageSrc = reader.GetOrdinal("ImageSrc");
int Price = reader.GetOrdinal("Price");
while (reader.Read())
{
products.Add(new Product()
{
ProductID = reader.IsDBNull(productId) ? 0 : reader.GetInt32(productId),
ProductName = reader.IsDBNull(ProductName) ? "" : reader.GetString(ProductName),
ProductCountry = reader.IsDBNull(ProductCountry) ? "" : reader.GetString(ProductCountry),
ProductType = reader.IsDBNull(ProductType) ? "" : reader.GetString(ProductType),
ProductDescription = reader.IsDBNull(ProductDescription) ? "" : reader.GetString(ProductDescription),
Grape = reader.IsDBNull(Grape) ? "" : reader.GetString(Grape),
ImageSrc = reader.IsDBNull(ImageSrc) ? "" : reader.GetString(ImageSrc),
Price = reader.IsDBNull(Price) ? 0 : reader.GetDecimal(Price),
});
}
return(products);
}
}
catch (Exception ex)
{
// Handle error here.
return(new List <Product>());
}
finally
{
connection.Close();
}
}
}
}