public LinkedList<Product> GetCategoryList(string category)
        {
            var categoryList = new LinkedList<Product>();

            var conn = new SqlConnection(GetConnectionString());
            var sql = "SELECT * FROM Products WHERE Category = '" + category + "'";

            try
            {
                conn.Open();
                var cmd = new SqlCommand(sql, conn);
                var productReader = cmd.ExecuteReader();
                if (productReader.HasRows)
                {
                    while (productReader.Read())
                    {
                        var product = new Product(Convert.ToString(productReader["Category"]).Trim(),
                            Convert.ToString(productReader["Product Name"]).Trim(),
                            Convert.ToString(productReader["Description"]).Trim(),
                            Convert.ToString(productReader["Image"]),
                            Convert.ToString(productReader["Price"]).Trim(),
                            Convert.ToString(productReader["Brand"]).Trim(),
                            Convert.ToString(productReader["Weight"]).Trim(),
                            Convert.ToString(productReader["Store Name"]).Trim(),
                            Convert.ToString(productReader["Store Address"]).Trim(),
                            Convert.ToString(productReader["Store Phone"]).Trim());

                        if (categoryList.Count == 0)
                        {
                            categoryList.AddFirst(product);
                        }
                        else
                        {
                            categoryList.AddAfter(categoryList.Last, product);
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                var msg = "GetCategoryList Sql Error: ";
                msg += ex.Message;
                throw new Exception(msg);
            }
            finally
            {
                conn.Close();
            }
            return categoryList;
        }
        public void AddProduct(Product product)
        {
            var conn = new SqlConnection(GetConnectionString());
            var sql = "INSERT INTO Products VALUES('" + product.GetCategory().Trim() + "','" + product.GetProductName().Trim() + "','" + product.GetImage().Trim() + "','" + product.GetDescription().Trim() + "','" + product.GetPrice().Trim() + "','" + product.GetBrand().Trim() + "','" + product.GetWeight().Trim() + "','" + product.GetStoreName().Trim() + "','" + product.GetStoreAddress().Trim() + "','" + product.GetStorePhone().Trim() + "')";
            try
            {
                conn.Open();
                var cmd = new SqlCommand(sql, conn);

                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                var msg = "AddProduct Sql Error: ";
                msg += ex.Message;
                throw new Exception(msg);
            }
            finally
            {
                conn.Close();
            }
        }
 //We can add an existing product too
 public void AddProduct(Product product)
 {
     _productDal.AddProduct(product);
 }
        public Product GetProduct(string productName)
        {
            productName = productName.Trim();
            Product product = null;

            var conn = new SqlConnection(GetConnectionString());
            var sql = "SELECT * FROM Products WHERE [Product Name] = '" + productName + "'";

            try
            {
                conn.Open();
                var cmd = new SqlCommand(sql, conn);
                var productReader = cmd.ExecuteReader();
                if (productReader.HasRows)
                {
                    productReader.Read();
                    product = new Product(Convert.ToString(productReader["Category"]).Trim(),
                        Convert.ToString(productReader["Product Name"]).Trim(),
                        Convert.ToString(productReader["Description"]).Trim(),
                        Convert.ToString(productReader["Image"]),
                        Convert.ToString(productReader["Price"]).Trim(),
                        Convert.ToString(productReader["Brand"]).Trim(),
                        Convert.ToString(productReader["Weight"]).Trim(),
                        Convert.ToString(productReader["Store Name"]).Trim(),
                        Convert.ToString(productReader["Store Address"]).Trim(),
                        Convert.ToString(productReader["Store Phone"]).Trim());
                }
            }
            catch (SqlException ex)
            {
                var msg = "GetProduct Sql Error: ";
                msg += ex.Message;
                throw new Exception(msg);
            }
            finally
            {
                conn.Close();
            }
            return product;
        }