public void UpdateProductsCSV(Product product)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (UniCommand command = new UniCommand("UPDATE products SET description = :description, height = :height, width = :width, depth = :depth, colour = :colour, image = :image, category = :category WHERE code = :code", con))
{
ImageConversion images = new ImageConversion();
command.Parameters.Add("code", UniDbType.VarChar).Value = product.code;
command.Parameters.Add("description", UniDbType.VarChar).Value = product.description;
command.Parameters.Add("height", UniDbType.Int).Value = product.height;
command.Parameters.Add("width", UniDbType.Int).Value = product.width;
command.Parameters.Add("depth", UniDbType.Int).Value = product.depth;
command.Parameters.Add("colour", UniDbType.Int).Value = product.colour.ToArgb();
command.Parameters.Add("image", UniDbType.VarChar).Value = images.ImageToBase64(product.image); //passing base64 string in
command.Parameters.Add("category", UniDbType.VarChar).Value = product.category;
command.ExecuteNonQuery();
}
}
con.Close();
}
}
//UPDATE OPERATIONS - ProductCategoriesForm.cs
public void UpdateProductCategory(Category category, string old_code)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (UniCommand command = new UniCommand("UPDATE product_categories SET code = :code, description = :description WHERE code = :pcode", con))
{
command.Parameters.Add("code", UniDbType.VarChar).Value = category.code;
command.Parameters.Add("description", UniDbType.VarChar).Value = category.description;
command.Parameters.Add("pcode", UniDbType.VarChar).Value = old_code;
command.ExecuteNonQuery();
}
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
public void UpdateCustomersCSV(Customer customer)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (UniCommand command = new UniCommand("UPDATE customers SET name = :name, address_line_1 = :address_line_1, address_line_2 = :address_line_2, address_line_3 = :address_line_3, address_line_4 = :address_line_4, address_postcode = :address_postcode WHERE account = :account", con))
{
command.Parameters.Add("account", UniDbType.VarChar).Value = customer.account;
command.Parameters.Add("name", UniDbType.VarChar).Value = customer.name;
command.Parameters.Add("address_line_1", UniDbType.VarChar).Value = customer.address_line_1;
command.Parameters.Add("address_line_2", UniDbType.VarChar).Value = customer.address_line_2;
command.Parameters.Add("address_line_3", UniDbType.VarChar).Value = customer.address_line_3;
command.Parameters.Add("address_line_4", UniDbType.VarChar).Value = customer.address_line_4;
command.Parameters.Add("address_postcode", UniDbType.VarChar).Value = customer.address_postcode;
command.ExecuteNonQuery();
}
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
public void RunNonQuerySQL(string sql)
{
try
{
UniCommand sqlCommand = new UniCommand(sql, uniConnection);
sqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(string.Format("Error running SQL: [{0}]", sql), e);
}
}
public void RunStoredProcedureNonQuery(string storedProcedureName)
{
try
{
UniCommand sqlCommand = new UniCommand(storedProcedureName, uniConnection);
//sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(string.Format("Error running SP: [{0}]", storedProcedureName), e);
}
}
//INSERT OPERATIONS - ProductCategoriesForm.cs
public void InsertProductCategory(Category category)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
using (UniCommand command = new UniCommand("INSERT into product_categories(code, description) VALUES (:code, :description)", con))
{
//INSERT OPERATION BELOW..
command.Parameters.Add("code", UniDbType.VarChar).Value = category.code;
command.Parameters.Add("description", UniDbType.VarChar).Value = category.description;
command.ExecuteNonQuery();
}
con.Close();
}
}
public void DeleteCustomer(Customer customer)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
UniCommand command = new UniCommand("DELETE FROM customers WHERE id = :customerid", con);
command.Parameters.Add("customerid", UniDbType.Int).Value = customer.id;
command.ExecuteNonQuery();
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
//DELETE OPERATIONS - ProductForm.cs
public void DeleteProduct(Product product)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (UniCommand command = new UniCommand("DELETE FROM products WHERE id = :id", con))
{
command.Parameters.Add("id", UniDbType.VarChar).Value = product.id;
command.ExecuteNonQuery();
}
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
//DELETE OPERATIONS - CustomerForm.cs
public void RemoveProductSelection(int customerID, int productID)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
UniCommand command = new UniCommand("DELETE FROM customer_products WHERE customer_products.customer_id = :customerID AND customer_products.product_id = :productID", con);
command.Parameters.Add("customerID", UniDbType.Int).Value = customerID;
command.Parameters.Add("productID", UniDbType.Int).Value = productID;
command.ExecuteNonQuery();
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
//DELETE OPERATIONS - ProductCategoriesForm.cs
public void DeleteProductCategory(Category category)
{
using (UniConnection con = DatabaseConnection.MakeConnection())
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
using (UniCommand command = new UniCommand("DELETE FROM product_categories WHERE code = :code", con))
{
//INSERT OPERATION BELOW..
command.Parameters.Add("code", UniDbType.VarChar).Value = category.code;
command.ExecuteNonQuery();
}
}
else
{
MessageBox.Show("Not open...");
}
con.Close();
}
}
public void RunStoredProcedureNonQuery(string storedProcedureName)
{
try
{
UniCommand sqlCommand = new UniCommand(storedProcedureName, uniConnection);
//sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(string.Format("Error running SP: [{0}]", storedProcedureName), e);
}
}
public void RunNonQuerySQL(string sql)
{
try
{
UniCommand sqlCommand = new UniCommand(sql, uniConnection);
sqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(string.Format("Error running SQL: [{0}]", sql), e);
}
}
