// public method to delete product public static bool DeleteProduct(Product product) { SqlConnection connection = TravelExpertsDB.GetConnection(); string deleteStatement = "DELETE FROM Products " + "WHERE ProdName = @ProdName"; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue( "@ProdName", product.ProdName); try { connection.Open(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0) return true; else return false; } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
private void btnsave_Click(object sender, EventArgs e) { if (!IsValidData()) return; // ask for confirmation before saving DialogResult result = MessageBox.Show("Save the entered data?", "Confirm Save", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (result == DialogResult.No) return; if (addMode) // add mode { // call method to accept the data from the message boxes product = new Product(); this.AcceptProductData(product); try { // call add product method to save the entered data and return the id of the new package product.ProductId = ProductDB.AddProduct(product); if (product.ProductId > 0) { this.DialogResult = DialogResult.OK; } } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } else // modify mode { Product newProduct = new Product(); newProduct.ProductId = product.ProductId; this.AcceptProductData(newProduct); try { // call the edit product methode and check if succeeded if (ProductDB.EditProduct(product, newProduct)) { product = newProduct; this.DialogResult = DialogResult.OK; } else { MessageBox.Show("Another user has already updated or deleted that product.", "Database Error"); this.DialogResult = DialogResult.Retry; } } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } }
private void btnAdd_Click(object sender, EventArgs e) { // open the add/modify form in the add mode frmProductDataChange addProductForm = new frmProductDataChange(); addProductForm.addMode = true; DialogResult result = addProductForm.ShowDialog(); if (result == DialogResult.OK) { // copy the newly added product from the add/modify form to this form and display the product data dgvSuppliers.DataSource = null; product = addProductForm.product; this.DisplayProduct(); } }
// public method AddProduct to insert into database public static int AddProduct(Product product) { // establish a connection with the database SqlConnection connection = TravelExpertsDB.GetConnection(); // insert statement string insertStatement = "INSERT INTO Products " + "(ProdName) " + "VALUES (@ProdName)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue( "@ProdName", product.ProdName); // try to catch exceptions try { // open the connection connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Products') FROM Products"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); int productId = Convert.ToInt32(selectCommand.ExecuteScalar()); return productId; } // catch exceptions and throw it the form to handle catch (SqlException ex) { throw ex; // throw the exception; let the form handle it cause we are in a data access class } finally { connection.Close(); // close the connection } }
// public method EditProduct public static bool EditProduct(Product oldProduct, Product newProduct) { // establish a connection with the database SqlConnection connection = TravelExpertsDB.GetConnection(); string updateStatement = "UPDATE Products SET " + "ProdName = @NewProdName " + "WHERE ProdName = @OldProdName"; // update command for new and old product SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue( "@NewProdName", newProduct.ProdName); updateCommand.Parameters.AddWithValue( "@OldProdName", oldProduct.ProdName); // try for exceptions try { // open connection connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) return true; else return false; } // throw an exception to the form to handle catch (SqlException ex) { throw ex; } finally { connection.Close(); // close the connection string } }
// make method to get product by ProductId public static Product GetProductId(int productId) { // establish a connection with the database SqlConnection connection = TravelExpertsDB.GetConnection(); // create select statement to select from the database string selectStatement = "SELECT ProductId, ProdName " + "FROM Products " + "WHERE ProductId = @ProductId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ProductId", productId); // try to catch exceptions try { // open the connection connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); // only one row; resolve CommandBehavior if (reader.Read()) // there is a row { // process the row Product product = new Product(); product.ProductId = Convert.ToInt32(reader["ProductId"]); product.ProdName = reader["ProdName"].ToString(); return product; } else // no product { return null; } } catch (SqlException ex) { throw ex; // throw the exception for the form to handle } finally { connection.Close(); } }
// method to get all products public static List<Product> GetAllProducts() { List<Product> products = new List<Product>(); // establish a connection with the database SqlConnection connection = TravelExpertsDB.GetConnection(); // create select statement to select from the database string selectStatement = "SELECT ProductId, ProdName " + "FROM Products Order By ProdName"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); // try to catch exceptions try { // open the connection connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) // there is row(s) { // process the row(s) Product product = new Product(); product.ProductId = Convert.ToInt32(reader["ProductId"]); product.ProdName = reader["ProdName"].ToString(); products.Add(product); } return products; } catch (SqlException ex) { throw ex; // throw the exception for the form to handle } finally { connection.Close(); } }
private void AcceptProductData(Product product) { product.ProdName = txtProductName.Text; }
private void btnModify_Click(object sender, EventArgs e) { // open the add/modify form in the modify mode frmProductDataChange modifyProductForm = new frmProductDataChange(); modifyProductForm.addMode = false; modifyProductForm.product = product; DialogResult result = modifyProductForm.ShowDialog(); if (result == DialogResult.OK) { // copy the modified product from the add/modify form to this form and display the product data after modification product = modifyProductForm.product; this.DisplayProduct(); } else if (result == DialogResult.Retry) { btnSearch_Click(sender, e); if (product != null) this.DisplayProduct(); else this.ClearControls(); } }
private void btnSearch_Click(object sender, EventArgs e) { if (Validator.IsNotEmpty(txtProductId)) { try { // call the GetProduct method which will search for the product by its id and retrieve it to product variable product = ProductDB.GetProductId(Convert.ToInt32(txtProductId.Text)); if (product == null) { MessageBox.Show("No product found with this code, please try again.", "Product Not Found"); this.ClearControls(); } else { // if a product found, displays the data and fill the grid with the suppliers of that product this.DisplayProduct(); List<Supplier> suppliers = ProductDB.GetSuppliersOfProduct(Convert.ToInt32(txtProductId.Text)); dgvSuppliers.DataSource = suppliers; DataGridViewColumn column = dgvSuppliers.Columns[0]; column.Width = 80; // adjust the ID column width column = dgvSuppliers.Columns[1]; column.Width = 350; // adjust the Name column width } } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } }
public static List<string> GetProductsAndSuppliers(int packageId) { List<string> listOfProdSup = new List<string>(); // establish a connection with the database SqlConnection connection = TravelExpertsDB.GetConnection(); // create select statement to select from the database string selectStatement = "select packages_products_suppliers.packageId, products.prodname, " + "suppliers.Supname " + "from packages_products_suppliers inner join " + "(products inner join " + "(products_suppliers inner join suppliers on products_suppliers.supplierid = suppliers.supplierid) " + "on products.productid = products_suppliers.productid) " + "on packages_products_suppliers.productsupplierid = products_suppliers.productsupplierid " + "where PackageId = @PackageId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@PackageId", packageId); // try to catch exceptions try { // open the connection connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); // retrieve multiple rows while (reader.Read()) // there is a row { // process the row Product product = new Product(); product.ProdName = reader["ProdName"].ToString(); Supplier supplier = new Supplier(); supplier.SupName = reader["SupName"].ToString(); listOfProdSup.Add(product.ProdName + " from " + supplier.SupName); } return listOfProdSup; } catch (SqlException ex) { throw ex; // throw the exception for the form to handle } finally { connection.Close(); } }
// make method to get all the suppliers of a specific product public static List<Product> GetProductsOfSupplier(int supId) { List<Product> products = new List<Product>(); // establish a connection with the database SqlConnection connection = TravelExpertsDB.GetConnection(); // create select statement to select from the database string selectStatement = "SELECT * " + "FROM Products " + "WHERE ProductId in (Select ProductId from Products_Suppliers where SupplierId = @SupplierId)"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@SupplierId", supId); // try to catch exceptions try { // open the connection connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); // retrieve multiple rows while (reader.Read()) // there is a row { // process the row Product product = new Product(); product.ProductId = Convert.ToInt32(reader["ProductId"]); product.ProdName = reader["ProdName"].ToString(); products.Add(product); } return products; } catch (SqlException ex) { throw ex; // throw the exception for the form to handle } finally { connection.Close(); } }