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;
}