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