public void GetCategories(ref ObservableCollection <CategoryData> list)
{
list = new ObservableCollection <CategoryData>();
SqlCommand cmd = new SqlCommand("")
{
CommandText = "SELECT "
+ "category_id, "
+ "category_name, "
+ "category_description "
+ "FROM categories; "
};
using (SqlConnection conn = new SqlConnection(DBCONNECTION))
{
cmd.Connection = conn;
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(
new CategoryData(
DatabaseClientCast.DBToValue <int>(reader["category_id"]),
reader["category_name"].ToString(),
reader["category_description"].ToString()
));
}
}
}
}
public void GetSupplier(ref ObservableCollection <SupplierData> list)
{
list = new ObservableCollection <SupplierData>();
SqlCommand cmd = new SqlCommand("")
{
CommandText = "SELECT "
+ " s.supplier_id, "
+ " s.supplier_name, "
+ " a.adress_street, "
+ " a.adress_number, "
+ " a.adress_city, "
+ " a.adress_zip, "
+ " a.adress_country "
+ "FROM "
+ " suppliers s "
+ " LEFT JOIN supplieradress sa ON s.supplier_id = sa.fk_supplier_id "
+ " LEFT JOIN adresses a ON sa.fk_adress_id = a.adress_id; "
};
using (SqlConnection conn = new SqlConnection(DBCONNECTION))
{
cmd.Connection = conn;
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(
new SupplierData(
DatabaseClientCast.DBToValue <int>(reader["supplier_id"]),
reader["supplier_name"].ToString(),
reader["adress_street"].ToString(),
reader["adress_number"].ToString(),
reader["adress_city"].ToString(),
reader["adress_zip"].ToString(),
reader["adress_country"].ToString()
));
}
}
}
}
public ObservableCollection <ProductModel> GetProducts()
{
ObservableCollection <ProductModel> list = new ObservableCollection <ProductModel>();
ProductModel product;
PriceModel price;
ImageModel image;
SqlCommand cmd = new SqlCommand("")
{
CommandText = "SELECT "
+ " p.product_id, p.product_name, p.product_quantity, p.product_description, "
+ " pr.price_base, pr.price_shipping, pr.price_profit, "
+ " cat.category_id, cat.category_name, cat.category_description, "
+ " sup.supplier_id, sup.supplier_name, "
+ " adr.adress_street, adr.adress_number, adr.adress_city, adr.adress_zip, "
+ " img.image_id, img.image_name "
+ "FROM "
+ " products p "
+ " LEFT JOIN prices pr ON p.product_id = pr.fk_product_id "
+ " LEFT JOIN productcategory pcat ON p.product_id = pcat.fk_product_id "
+ " LEFT JOIN categories cat ON pcat.fk_category_id = cat.category_id "
+ " LEFT JOIN productsupplier psup ON p.product_id = psup.fk_product_id "
+ " LEFT JOIN suppliers sup ON psup.fk_supplier_id = sup.supplier_id "
+ " LEFT JOIN productimage pimg ON pimg.fk_product_id = p.product_id "
+ " LEFT JOIN images img ON pimg.fk_image_id = img.image_id "
+ " LEFT JOIN productsarchived pa ON p.product_id = pa.fk_product_id "
+ " LEFT JOIN supplieradress sadr ON sup.supplier_id = sadr.fk_supplier_id "
+ " LEFT JOIN adresses adr ON sadr.fk_adress_id = adr.adress_id "
+ "WHERE "
+ " pa.fk_product_id IS NULL "
};
using (SqlConnection conn = new SqlConnection(DBCONNECTION))
{
cmd.Connection = conn;
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
price = new PriceModel(
Convert.ToDecimal(DatabaseClientCast.DBToValue <decimal>(reader["price_base"])),
Convert.ToDecimal(DatabaseClientCast.DBToValue <decimal>(reader["price_shipping"])),
Convert.ToDecimal(DatabaseClientCast.DBToValue <decimal>(reader["price_profit"]))
);
image = new ImageModel(
reader["image_name"].ToString()
);
product = new ProductModel(
reader["product_name"].ToString(),
Convert.ToInt32(reader["product_quantity"]),
reader["product_description"].ToString(),
price,
image,
DatabaseClientCast.DBToValue <int>(reader["category_id"]),
DatabaseClientCast.DBToValue <int>(reader["supplier_id"])
);
product.SetID((int)reader["product_id"]);
product.Image.SetID(DatabaseClientCast.DBToValue <int>(reader["image_id"]));
list.Add(product);
}
}
}
return(list);
}