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 RemoveOrder(Order order) { if (order.internalID.Equals(-1)) { throw new Exception("Order to remove had no ID!"); } try { using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection()) { //upon deletion, make sure both the productcode and the internal database id match, that way we're 100% sure the right product is deleted string query = "DELETE FROM " + MySQLDAOFactory.DatabaseName + ".order WHERE order.idorder = '" + order.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 RemoveProduct(Product p) { if (p.internalID == -1) { return(false); } try { using (IDbConnection connection = MySQLDAOFactory.GetDatabase().CreateOpenConnection()) { //upon deletion, make sure both the productcode and the internal database id match, that way we're 100% sure the right product is deleted string query = "DELETE FROM product WHERE product.productcode = '" + p.productCode.ToString() + "' AND product.idproduct = '" + p.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; } } } }