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 InsertCategory(CategoryData data)
{
string sql;
SqlCommand cmd;
sql = "INSERT INTO categories (category_name, category_description) "
+ " VALUES (@name, @description); ";
using (SqlConnection conn = new SqlConnection(DBCONNECTION))
{
conn.Open();
cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@name", SqlDbType.Text).Value = DatabaseClientCast.StringToDb(data.Name);
cmd.Parameters.Add("@description", SqlDbType.Text).Value = DatabaseClientCast.StringToDb(data.Description);
cmd.ExecuteNonQuery();
}
}
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 void InsertSupplier(SupplierData data)
{
string sql;
SqlCommand cmd;
sql = "INSERT INTO suppliers (supplier_name) VALUES (@name); "
+ "INSERT INTO adresses (adress_street, adress_number, adress_city, adress_zip, adress_country) VALUES (@street, @nr, @city, @zip, @country); ";
using (SqlConnection conn = new SqlConnection(DBCONNECTION))
{
conn.Open();
cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@name", SqlDbType.Text).Value = DatabaseClientCast.StringToDb(data.Name);
cmd.Parameters.Add("@street", SqlDbType.Text).Value = DatabaseClientCast.StringToDb(data.Street);
cmd.Parameters.Add("@nr", SqlDbType.Text).Value = DatabaseClientCast.StringToDb(data.Nr);
cmd.Parameters.Add("@city", SqlDbType.Text).Value = DatabaseClientCast.StringToDb(data.City);
cmd.Parameters.Add("@zip", SqlDbType.Text).Value = DatabaseClientCast.StringToDb(data.Zip);
cmd.Parameters.Add("@country", SqlDbType.Text).Value = DatabaseClientCast.StringToDb(data.Country);
cmd.ExecuteNonQuery();
}
}
private void InsertProduct(ProductModel product)
{
int id;
string sql;
SqlCommand cmd;
SqlTransaction transaction;
using (SqlConnection conn = new SqlConnection(DBCONNECTION))
{
conn.Open();
transaction = conn.BeginTransaction(IsolationLevel.ReadUncommitted);
try
{
#region Insert Produkt
sql = "INSERT INTO products (product_name, product_quantity, product_description) "
+ " VALUES (@name, @quantity, @description); ";
cmd = new SqlCommand(sql, conn, transaction);
cmd.Parameters.Add("@name", SqlDbType.Text).Value = product.ProductName.StringToDb();
cmd.Parameters.Add("@quantity", SqlDbType.Int).Value = DatabaseClientCast.ValueToDb <int>(product.Quantity);
cmd.Parameters.Add("@description", SqlDbType.Text).Value = product.Description.StringToDb();
cmd.ExecuteNonQuery();
cmd = new SqlCommand("select @@IDENTITY", conn, transaction);
id = Convert.ToInt32(cmd.ExecuteScalar());
product.SetID(id);
#endregion Insert Produkt
#region Insert Price
sql = "INSERT INTO prices (price_base, price_shipping, price_profit, fk_product_id) "
+ " VALUES (@base, @shipping, @profit, @productID); ";
cmd = new SqlCommand(sql, conn, transaction);
cmd.Parameters.Add("@base", SqlDbType.Decimal).Value = product.Price.BasePrice.ValueToDb <decimal>();
cmd.Parameters.Add("@shipping", SqlDbType.Decimal).Value = product.Price.ShippingPrice.ValueToDb <decimal>();
cmd.Parameters.Add("@profit", SqlDbType.Decimal).Value = product.Price.Profit.ValueToDb <decimal>();
cmd.Parameters.Add("@productID", SqlDbType.Int).Value = product.ID.ValueToDb <int>();
cmd.ExecuteNonQuery();
#endregion Insert Price
#region Insert Category [dbo.productcategory]
if (product.CategoryID != null)
{
sql = "INSERT INTO productcategory (fk_category_id, fk_product_id) "
+ " VALUES (@categoryID, @productID); ";
cmd = new SqlCommand(sql, conn, transaction);
cmd.Parameters.Add("@categoryID", SqlDbType.Int).Value = product.CategoryID.ValueToDb <int>();
cmd.Parameters.Add("@productID", SqlDbType.Int).Value = product.ID.ValueToDb <int>();
cmd.ExecuteNonQuery();
}
#endregion Insert Category [dbo.productcategory]
#region Insert Supplier [dbo.productsupplier]
if (product.SupplierID != null)
{
sql = "INSERT INTO productsupplier (fk_supplier_id, fk_product_id) "
+ " VALUES (@supplierID, @productID); ";
cmd = new SqlCommand(sql, conn, transaction);
cmd.Parameters.Add("@supplierID", SqlDbType.Int).Value = product.SupplierID.ValueToDb <int>();
cmd.Parameters.Add("@productID", SqlDbType.Int).Value = product.ID.ValueToDb <int>();
cmd.ExecuteNonQuery();
}
#endregion Insert Supplier [dbo.productsupplier]
#region Insert Image
if (product.Image.FileName != null)
{
sql = " BEGIN "
+ " INSERT INTO images (image_name) VALUES (@imageName); "
+ " INSERT INTO productimage (fk_product_id, fk_image_id) VALUES (@productID, @@IDENTITY); "
+ " END; ";
cmd = new SqlCommand(sql, conn, transaction);
cmd.Parameters.Add("@productID", SqlDbType.Int).Value = product.ID.ValueToDb <int>();
cmd.Parameters.Add("@imageID", SqlDbType.Int).Value = product.Image.ID.ValueToDb <int>();
cmd.Parameters.Add("@imageName", SqlDbType.Text).Value = product.Image.FileName.StringToDb();
cmd.ExecuteNonQuery();
cmd = new SqlCommand("SELECT IDENT_CURRENT ('images'); ", conn, transaction);
id = Convert.ToInt32(cmd.ExecuteScalar());
if (product.Image.ID != id)
{
product.Image.SetID(id);
}
}
#endregion Insert Image
transaction.Commit();
}
#region Exception-Block
catch (Exception exceptionCommit)
{
try
{
transaction.Rollback();
}
catch (Exception exceptionRollback)
{
throw new Exception(
$"Fehler beim Senden der Daten zur Datenbank: {exceptionCommit.Message}\n" +
$"Fehler beim zurücksetzten der Daten in der Datenbank: {exceptionRollback.Message}");
}
throw new Exception($"Fehler beim Senden der Daten zur Datenbank: { exceptionCommit.Message }");
}
#endregion Exception-Block
finally
{
conn.Close();
}
}
}
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);
}
