//GET PRODUCT FROM DB BY PRODUCT CODE private void GetProduct(string productID) { try { product = ProductDB.GetProduct(productID); if (product == null) MessageBox.Show("No product found with this product code.", "Product Not Found"); else this.ShowProduct(); } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } }
//RETURN ONE PRODUCT BASED ON PRODUCT CODE public static Product GetProduct(string productCode) { Product p = new Product(); SqlConnection connection = AppRepairsDB.GetConnection(); string selectStatement = "SELECT ProductCode, Name, YearsWarranty, ReleaseDate " + "FROM Products " + "WHERE ProductCode = @ProductCode " + "ORDER BY ProductCode"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ProductCode", productCode); //assign value to @ProductCode try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (reader.Read()) //if there was a matching product code { p.ProductCode = reader["ProductCode"].ToString(); p.ProductName = reader["Name"].ToString(); p.YearsWarranty = Convert.ToDecimal(reader["YearsWarranty"]); p.ReleaseDate = (DateTime)reader["ReleaseDate"]; } else p = null; //product does not exist reader.Close(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return p; }
//SAVE CHANGES private void btnAccept_Click(object sender, EventArgs e) { if (IsValidData()) { if (addProduct) { product = new Product(); //product being added this.SaveToProduct(product); try { ProductDB.AddProduct(product); //save product to DB this.DialogResult = DialogResult.OK; } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } else //edit product { Product newProduct = new Product(); //updated product this.SaveToProduct(newProduct); try { if (!ProductDB.UpdateProduct(product, newProduct)) //if bool is false (concurrency error) { MessageBox.Show("Another user has updated or deleted that product.", "Database Error"); this.DialogResult = DialogResult.Retry; } else //bool is true, no concurrency issue { product = newProduct; this.DialogResult = DialogResult.OK; } } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } } }
//UPDATE PRODUCT DATA public static bool UpdateProduct(Product oldProduct, Product newProduct) { SqlConnection connection = AppRepairsDB.GetConnection(); string updateStatement = "UPDATE Products SET " + "ProductCode = @NewProductCode, " + "Name = @NewName, " + "YearsWarranty = @NewYearsWarranty, " + "ReleaseDate = @NewReleaseDate " + "WHERE ProductCode = @OldProductCode " + "AND Name = @OldName " + "AND YearsWarranty = @OldYearsWarranty " + "AND ReleaseDate = @OldReleaseDate"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@NewProductCode", newProduct.ProductCode); updateCommand.Parameters.AddWithValue("@NewName", newProduct.ProductName); updateCommand.Parameters.AddWithValue("@NewYearsWarranty", newProduct.YearsWarranty); updateCommand.Parameters.AddWithValue("@NewReleaseDate", newProduct.ReleaseDate); updateCommand.Parameters.AddWithValue("@OldProductCode", oldProduct.ProductCode); updateCommand.Parameters.AddWithValue("@OldName", oldProduct.ProductName); updateCommand.Parameters.AddWithValue("@OldYearsWarranty", oldProduct.YearsWarranty); updateCommand.Parameters.AddWithValue("@OldReleaseDate", oldProduct.ReleaseDate); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) //a row was affected return true; else return false; } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
//DELETE PRODUCT public static int DeleteProduct(Product product) { int deleteCount = 0; SqlConnection connection = AppRepairsDB.GetConnection(); string deleteStatement = "DELETE FROM Products " + "WHERE ProductCode = @ProductCode " + "AND Name = @Name " + "AND YearsWarranty = @YearsWarranty " + "AND ReleaseDate = @ReleaseDate"; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue("@ProductCode", product.ProductCode); deleteCommand.Parameters.AddWithValue("@Name", product.ProductName); deleteCommand.Parameters.AddWithValue("@YearsWarranty", product.YearsWarranty); deleteCommand.Parameters.AddWithValue("@ReleaseDate", product.ReleaseDate); try { connection.Open(); deleteCommand.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return deleteCount; }
//ADD NEW PRODUCT public static void AddProduct(Product product) { SqlConnection connection = AppRepairsDB.GetConnection(); string insertStatement = "INSERT Products (ProductCode, Name, YearsWarranty, ReleaseDate) " + "VALUES (@ProductCode, @Name, @YearsWarranty, @ReleaseDate)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@ProductCode", product.ProductCode); insertCommand.Parameters.AddWithValue("@Name", product.ProductName); insertCommand.Parameters.AddWithValue("@YearsWarranty", product.YearsWarranty); insertCommand.Parameters.AddWithValue("@ReleaseDate", product.ReleaseDate); try { connection.Open(); insertCommand.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
//SAVE TEXTBOX TEXT TO A PRODUCT private void SaveToProduct(Product product) { product.ProductCode = txtProductCode.Text.ToUpper(); product.ProductName = txtName.Text; product.YearsWarranty = Convert.ToDecimal(txtYearsWarranty.Text); product.ReleaseDate = Convert.ToDateTime(txtReleaseDate.Text); }
//ADD NEW PRODUCT private void btnAdd_Click(object sender, EventArgs e) { frmAddEditProduct addForm = new frmAddEditProduct(); addForm.addProduct = true; DialogResult result = addForm.ShowDialog(); if (result == DialogResult.OK) { product = addForm.product; this.FillComboBox(); cboProductCodes.Text = product.ProductCode; this.ShowProduct(); } }
//EDIT CURRENT PRODUCT private void btnEdit_Click(object sender, EventArgs e) { frmAddEditProduct editForm = new frmAddEditProduct(); editForm.addProduct = false; //editing editForm.product = product; //use current product to pass data to product in editing form DialogResult result = editForm.ShowDialog(); if (result == DialogResult.OK) { product = editForm.product; //new current product equal to edits made in editForm this.FillComboBox(); cboProductCodes.Text = product.ProductCode; this.ShowProduct(); } else if (result == DialogResult.Retry) //retry due to concurrency error { this.ClearControls(); this.GetProduct(product.ProductCode); //re-get product based on product code } }