public bool DeleteCustomer(Customer customer)
{
if (customer.internalID.Equals(-1))
{
throw new Exception("Customer to remove had no ID!");
}
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "DELETE FROM customer WHERE customer.idcustomer = '" + customer.internalID.ToString() + "'";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
if (command.ExecuteNonQuery() <= 0)
{
return(false);
}
else
{
return(true);
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
public bool GetProductsByName(string productName, out Product[] products)
{
if (productName.Equals(""))
{
products = new Product[0];
return(false);
}
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "SELECT * FROM product WHERE product.productname LIKE '%" + productName.Trim().ToLower() + "%'";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
NumberFormatInfo numberInfo = System.Globalization.NumberFormatInfo.CurrentInfo;
List <Product> productsList = new List <Product>();
while (reader.Read())
{
Product.PriceType priceType;
if (Enum.TryParse <Product.PriceType>(reader["pricetype"].ToString(), true, out priceType))
{
Product p = new Product(
reader["productname"].ToString(),
Convert.ToInt32(reader["productcode"]),
reader["description"].ToString(),
Decimal.Parse(reader["price"].ToString(), NumberStyles.Currency, numberInfo),
priceType,
null, //TODO
(Convert.ToInt32(reader["idproduct"]))
);
productsList.Add(p);
}
else
{
throw new Exception("Couldn't cast product price type enum!");
}
}
products = productsList.ToArray();
if (products.Length > 0)
{
return(true);
}
return(false);
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
public bool AddCustomer(Customer customer)
{
if (customer.internalID.Equals(-1) == false)
{
throw new Exception("Customer to insert into database already has database id!");
}
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
using (IDbTransaction transaction = connection.BeginTransaction())
{
try
{
string query = "INSERT INTO customer (firstname, lastname, phonenumber, street, housenumber, town) VALUES "
+ "('" + customer.firstName + "', '" + customer.lastName + "', '" + customer.phoneNumber + "', '" + customer.street + "', '" + customer.houseNumber + "', '" + customer.town + "')";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
if (command.ExecuteNonQuery() <= 0)
{
transaction.Rollback();
return(false);
}
}
string queryID = "SELECT LAST_INSERT_ID();";
using (IDbCommand commandID = MySQLDAOFactory.GetDatabase().CreateCommand(queryID, connection))
{
using (IDataReader reader = commandID.ExecuteReader())
{
if (reader.Read())
{
customer.internalID = Convert.ToInt32(reader[0]);
}
}
}
if (customer.internalID.Equals(-1))
{
transaction.Rollback();
throw new Exception("Customer inserted succesfully, but could not retrieve ID afterwards. Rollback.");
}
else
{
transaction.Commit();
return(true);
}
}
catch (MySqlException ex)
{
transaction.Rollback();
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
public bool GetOrdersByCustomer(Customer customer, out Order[] orders)
{
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "SELECT * FROM " + MySQLDAOFactory.DatabaseName + ".order WHERE order.customerid = '" + customer.internalID.ToString() + "'";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
List <Order> ordersList = new List <Order>();
while (reader.Read())
{
DateTime ordered_datetime;
DateTime pickup_datetime;
if (DateTime.TryParseExact(reader["ordered_datetime"].ToString(), dateFormatReading, System.Globalization.CultureInfo.CurrentCulture, System.Globalization.DateTimeStyles.AssumeLocal, out ordered_datetime) &&
DateTime.TryParseExact(reader["pickup_datetime"].ToString(), dateFormatReading, System.Globalization.CultureInfo.CurrentCulture, System.Globalization.DateTimeStyles.AssumeLocal, out pickup_datetime))
{
Order o = new Order(
customer,
ordered_datetime,
pickup_datetime,
new Employee("", Convert.ToInt32(reader["employee_id"])),
reader["description"].ToString(),
Convert.ToInt32(reader["idorder"])
);
ordersList.Add(o);
}
else
{
throw new Exception("Couldn't cast to datetime!");
}
}
orders = ordersList.ToArray();
if (orders.Length > 0)
{
return(true);
}
return(false);
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
public bool AddCategory(ProductCategory category)
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
using (IDbTransaction transaction = connection.BeginTransaction())
{
try
{
string query = "INSERT INTO category (categoryname, categorydescription) VALUES "
+ "('" + category.name.Trim().ToLower() + "', '" + category.description + "')";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
if (command.ExecuteNonQuery() <= 0)
{
transaction.Rollback();
return(false);
}
}
string queryID = "SELECT LAST_INSERT_ID();";
using (IDbCommand commandID = MySQLDAOFactory.GetDatabase().CreateCommand(queryID, connection))
{
using (IDataReader reader = commandID.ExecuteReader())
{
if (reader.Read())
{
category.internalID = Convert.ToInt32(reader[0]);
}
}
if (category.internalID.Equals(-1) == false)
{
transaction.Commit();
return(true);
}
else
{
transaction.Rollback();
throw new Exception("category inserted succesfully, but could not retrieve ID afterwards. Rolled back.");
}
}
}
catch (MySqlException ex)
{
transaction.Rollback();
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
public bool GetCustomersByName(string customerName, out Customer[] customers)
{
string searchName = customerName.Trim().ToLower();
if (searchName.Equals(""))
{
customers = new Customer[0];
return(false);
}
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "SELECT * FROM customer where customer.firstname LIKE '%" + searchName + "%' OR customer.lastname LIKE '%" + searchName + "%'";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
List <Customer> customerList = new List <Customer>();
while (reader.Read())
{
Customer c = new Customer(
reader["firstname"].ToString(),
reader["lastname"].ToString(),
reader["phonenumber"].ToString(),
reader["street"].ToString(),
reader["housenumber"].ToString(),
reader["town"].ToString()
);
customerList.Add(c);
}
customers = customerList.ToArray();
if (customers.Length > 0)
{
return(true);
}
return(false);
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
public bool GetProductByCode(int productCode, out Product product)
{
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "SELECT * FROM product WHERE product.productcode = '" + productCode.ToString() + "'";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
NumberFormatInfo numberInfo = System.Globalization.NumberFormatInfo.CurrentInfo;
Product p = null;
if (reader.Read())
{
p = new Product(
reader["productname"].ToString(),
productCode,
reader["description"].ToString(),
Decimal.Parse(reader["price"].ToString(), NumberStyles.Currency, numberInfo),
((Product.PriceType)(Convert.ToInt32(reader["pricetype"]))),
null, //TODO
(Convert.ToInt32(reader["idproduct"]))
);
}
product = p;
if (product != null)
{
return(true);
}
return(false);
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
public bool GetAllCustomers(out Customer[] customers)
{
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "SELECT * FROM customer";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
List <Customer> customerList = new List <Customer>();
while (reader.Read())
{
Customer c = new Customer(
reader["firstname"].ToString(),
reader["lastname"].ToString(),
reader["phonenumber"].ToString(),
reader["street"].ToString(),
reader["housenumber"].ToString(),
reader["town"].ToString(),
Convert.ToInt32(reader["idcustomer"])
);
customerList.Add(c);
}
customers = customerList.ToArray();
if (customers.Length > 0)
{
return(true);
}
return(false);
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
public bool GetCustomerByID(int customerID, out Customer customer)
{
if (customerID.Equals(-1))
{
customer = null;
return(false);
}
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "SELECT * FROM customer where customer.idcustomer = '" + customerID.ToString() + "'";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
customer = new Customer(
reader["firstname"].ToString(),
reader["lastname"].ToString(),
reader["phonenumber"].ToString(),
reader["street"].ToString(),
reader["housenumber"].ToString(),
reader["town"].ToString(),
customerID
);
return(true);
}
customer = null;
return(false);
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
public bool GetAllCategories(out ProductCategory[] categories)
{
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
string query = "SELECT * FROM category";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
using (IDataReader reader = command.ExecuteReader())
{
List <ProductCategory> categoryList = new List <ProductCategory>();
while (reader.Read())
{
ProductCategory c = new ProductCategory(
reader["categoryname"].ToString(),
reader["categorydescription"].ToString(),
Convert.ToInt32(reader["idcategory"])
);
categoryList.Add(c);
}
categories = categoryList.ToArray();
if (categories.Length > 0)
{
return(true);
}
return(false);
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
public bool AddOrder(Order order)
{
if (order.internalID.Equals(-1) == false)
{
throw new Exception("Order to insert into database already has database id!");
}
if (order.customer.internalID.Equals(-1) == true)
{
throw new Exception("Order to insert into database didn't have valid customer!");
}
if (order.employee.internalID.Equals(-1) == true)
{
throw new Exception("Order to insert into database didn't have valid employee!");
}
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
using (IDbTransaction transaction = connection.BeginTransaction())
{
try
{
string query = "INSERT INTO " + MySQLDAOFactory.DatabaseName + ".order (customerid, ordered_datetime, pickup_datetime, employee_id, description) VALUES "
+ "('" + order.customer.internalID.ToString() + "', '" + order.dateTimeOrdered.ToString(dateFormatWriting) + "', '" + order.dateTimePickup.ToString(dateFormatWriting) + "', '" + order.employee.internalID.ToString() + "', '" + order.description + "')";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
if (command.ExecuteNonQuery() <= 0)
{
transaction.Rollback();
return(false);
}
}
string queryID = "SELECT LAST_INSERT_ID();";
using (IDbCommand commandID = MySQLDAOFactory.GetDatabase().CreateCommand(queryID, connection))
{
using (IDataReader reader = commandID.ExecuteReader())
{
if (reader.Read())
{
order.internalID = Convert.ToInt32(reader[0]);
}
}
}
if (order.internalID.Equals(-1))
{
transaction.Rollback();
throw new Exception("Order inserted succesfully, but could not retrieve ID afterwards. Rollback.");
}
if (order.order_entries != null && order.order_entries.Length > 0)
{
StringBuilder catQuery = new StringBuilder("INSERT INTO order_entry (orderid, productid, quantity) VALUES ", order.order_entries.Length * 2);
for (int i = 0; i < order.order_entries.Length; i++)
{
catQuery.Append("(" + order.internalID + ", " + order.order_entries[i].product.internalID + ", " + order.order_entries[i].quantity + ")");
if (i != (order.order_entries.Length - 1))
{
catQuery.Append(", ");
}
}
using (IDbCommand commandCat = MySQLDAOFactory.GetDatabase().CreateCommand(catQuery.ToString(), connection))
{
if (commandCat.ExecuteNonQuery() <= 0)
{
transaction.Rollback();
return(false);
}
else
{
transaction.Commit();
return(true);
}
}
}
else
{
transaction.Commit();
return(true);
}
}
catch (MySqlException ex)
{
transaction.Rollback();
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
public bool GetProductsByCategory(ProductCategory[] categories, out Product[] products)
{
try
{
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
StringBuilder query = new StringBuilder(
"SELECT * " +
"FROM " +
"(" +
"SELECT p.* " +
"FROM product AS p " +
"JOIN product_category AS cat " +
"ON (cat.productid = p.idproduct AND ("
);
for (int i = 0; i < categories.Length; i++)
{
if (i != 0)
{
query.Append("OR ");
}
query.Append("cat.categoryid = " + categories[i].internalID + " ");
}
query.Append(
"))" +
")" +
"AS product_with_categories " +
"GROUP BY idproduct " +
"HAVING COUNT(idproduct) >= " + categories.Length
);
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query.ToString(), connection))
{
using (IDataReader reader = command.ExecuteReader())
{
NumberFormatInfo numberInfo = System.Globalization.NumberFormatInfo.CurrentInfo;
List <Product> productsList = new List <Product>();
while (reader.Read())
{
Product.PriceType priceType;
if (Enum.TryParse <Product.PriceType>(reader["pricetype"].ToString(), true, out priceType))
{
Product p = new Product(
reader["productname"].ToString(),
Convert.ToInt32(reader["productcode"]),
reader["description"].ToString(),
Decimal.Parse(reader["price"].ToString(), NumberStyles.Currency, numberInfo),
priceType,
categories,
(Convert.ToInt32(reader["idproduct"]))
);
productsList.Add(p);
}
else
{
throw new Exception("Couldn't cast product price type enum!");
}
}
products = productsList.ToArray();
if (products.Length > 0)
{
return(true);
}
return(false);
}
}
}
}
catch (MySqlException ex)
{
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
throw ex;
}
}
public bool AddProduct(Product p)
{
if (p.internalID.Equals(-1) == false)
{
throw new Exception("Product to insert into database already has database id!");
}
using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection())
{
using (IDbTransaction transaction = connection.BeginTransaction())
{
try
{
CultureInfo culture = CultureInfo.CreateSpecificCulture("en-US");
string priceString = p.price.ToString(culture); //we store it as US-style decimal
string productName = p.productName.Trim().ToLower();
string query = "INSERT INTO product (productname, productcode, description, price, pricetype) VALUES "
+ "('" + productName + "'," + "'" + p.productCode.ToString() + "'," + "'" + p.description + "'," + "'" + priceString + "','" + p.priceType + "')";
using (IDbCommand command = MySQLDAOFactory.GetDatabase().CreateCommand(query, connection))
{
if (command.ExecuteNonQuery() <= 0)
{
transaction.Rollback();
return(false);
}
}
if (p.categories != null && p.categories.Length > 0)
{
//now get id
string queryID = "SELECT LAST_INSERT_ID();";
using (IDbCommand commandID = MySQLDAOFactory.GetDatabase().CreateCommand(queryID, connection))
{
using (IDataReader reader = commandID.ExecuteReader())
{
if (reader.Read())
{
p.internalID = Convert.ToInt32(reader[0]);
}
}
}
if (p.internalID.Equals(-1) == false)
{
//we have the id, now add categories
StringBuilder catQuery = new StringBuilder("INSERT INTO product_category (categoryid, productid) VALUES ", p.categories.Length * 2);
for (int i = 0; i < p.categories.Length; i++)
{
catQuery.Append("(" + p.categories[i].internalID + ", " + p.internalID + ")");
if (i != (p.categories.Length - 1))
{
catQuery.Append(", ");
}
}
using (IDbCommand commandCat = MySQLDAOFactory.GetDatabase().CreateCommand(catQuery.ToString(), connection))
{
if (commandCat.ExecuteNonQuery() <= 0)
{
transaction.Rollback();
return(false);
}
else
{
transaction.Commit();
return(true);
}
}
}
else
{
transaction.Rollback();
throw new Exception("product inserted succesfully, but could not retrieve ID afterwards. Rollback.");
}
}
else
{
transaction.Commit();
return(true);
}
}
catch (MySqlException ex)
{
transaction.Rollback();
throw new DatabaseException(ex.Message, ex);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
