Esempio n. 1
0
 public JsonResult GetProducts(FilterCriteria model)
 {
     return(Json(helper.GetProducts(model), JsonRequestBehavior.AllowGet));
 }
Esempio n. 2
0
        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();
                    }
                }
            }
        }