private void btnDeleteProd_Click(object sender, EventArgs e) { // get the current product Name and supplier Name int rowNum = Convert.ToInt32(dgvSupplier.CurrentCell.RowIndex); int currentSupId = Convert.ToInt32(dgvSupplier["dataGridViewTextBoxColumn1", rowNum].Value); string currentSupName = (dgvSupplier["dataGridViewTextBoxColumn2", rowNum].Value).ToString(); // obtain current product ID form comboBox int currentProdId = Convert.ToInt32(cbDeleteProd.SelectedValue); string currentProdName = (cbAddProd.SelectedItem).ToString(); // confirmation with user, return if user choose "No" if (MessageBox.Show("Are you sure " + currentSupName + " does not provide this services any more?", "Remove Confirmation", MessageBoxButtons.YesNo) == DialogResult.No) { return; } var ProdSuppID = 0; // get the current product supplier ID using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { ProdSuppID = (from prod_supp in dbContext.Products_Suppliers where prod_supp.ProductId == currentProdId && prod_supp.SupplierId == currentSupId select prod_supp.ProductSupplierId).FirstOrDefault(); } // Delete the record from database try { using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { var prodToBeDeleted = (from prod_supp in dbContext.Products_Suppliers where prod_supp.ProductSupplierId == ProdSuppID select prod_supp).FirstOrDefault(); if (prodToBeDeleted != null) { dbContext.Products_Suppliers.DeleteOnSubmit(prodToBeDeleted); dbContext.SubmitChanges(); } } } catch (Exception ex) { MessageBox.Show("Error occurred, " + "please check if the deletion is in conflict " + "with another table (Packages, Packages_Products_Supplier, etc.): " + ex.Message, ex.GetType().ToString()); } // refresh the product of supplier dataGridView and the two comboBoxes RefreshPordOfSuppAndLists(currentSupId); }
// DELETE private void btnDeleteSupplier_Click(object sender, EventArgs e) { int rowNum = Convert.ToInt32(dgvSupplier.CurrentCell.RowIndex); int supplierID = Convert.ToInt32(dgvSupplier["dataGridViewTextBoxColumn1", rowNum].Value); DialogResult delete = MessageBox.Show("Are you sure about DELETING this Supplier?", "Delete Supplier", MessageBoxButtons.YesNo); if (delete == DialogResult.No) { return; } using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { // Check if supplier still provides service products. var currentProducts = (from ps in dbContext.Products_Suppliers join p in dbContext.Products on ps.ProductId equals p.ProductId where ps.SupplierId == supplierID select p.ProdName).ToList(); // if service products exists for supplier, give a warning message and return if (currentProducts.Count > 0) { MessageBox.Show("Please remove all service Products from Supplier before deleting", "Delete Warning"); return; } } if (delete == DialogResult.Yes) { using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { try { Supplier currentSupplier = (from s in dbContext.Suppliers where s.SupplierId == supplierID select s).Single(); dbContext.Suppliers.DeleteOnSubmit(currentSupplier); dbContext.SubmitChanges(); DisplaySuppliers(); } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } } DisplaySuppliers(); }
// user can click the "Save" button to save the data to the database private void btnSave_Click(object sender, EventArgs e) { // if the product name is empty, give user a warning message and return if (prodNameTextBox.TextLength <= 0) { MessageBox.Show("The Product Name is required!", "Name is missing"); return; } // confirm with user before write data to the database String strConfirmation = bIsNewProduct ? "Do you want to add a new product: " : "Do you want to change the product to "; strConfirmation += prodNameTextBox.Text + "?"; if (MessageBox.Show(strConfirmation, "Confirmation", MessageBoxButtons.OKCancel) == DialogResult.Cancel) { return; } // write data to the database try { using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { Product objProduct = null; if (bIsNewProduct)// add a new product { objProduct = new Product { ProdName = prodNameTextBox.Text }; dbContext.Products.InsertOnSubmit(objProduct); } else // edit the existing product { objProduct = dbContext.Products.Single(prod => prod.ProductId == Convert.ToInt32(productIdTextBox.Text)); objProduct.ProdName = prodNameTextBox.Text; } dbContext.SubmitChanges(); // save the changes to database } } catch (Exception ex) { MessageBox.Show("Some error happened: " + ex.Message, ex.GetType().ToString()); } DialogResult = DialogResult.OK; }
/***************************************/ /* Product(s) of Supplier CRUD Buttons */ /***************************************/ private void btnAddProd_Click(object sender, EventArgs e) { // get the current product ID, Name and supplier ID, Name int rowNum = Convert.ToInt32(dgvSupplier.CurrentCell.RowIndex); int currentSupId = Convert.ToInt32(dgvSupplier["dataGridViewTextBoxColumn1", rowNum].Value); string currentSupName = (dgvSupplier["dataGridViewTextBoxColumn2", rowNum].Value).ToString(); //string currentSupName = GetSupplierName(currentSupId); // obtain current product ID form comboBox int currentProdId = Convert.ToInt32(cbAddProd.SelectedValue); // check if the current supplier already has current product in products_suppliers table using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { var CheckIfExists = (from ps in dbContext.Products_Suppliers where ps.ProductId == currentProdId && ps.SupplierId == currentSupId select ps).FirstOrDefault(); // if the record in the database already, refresh the 2 supplier list and return if (CheckIfExists != null) { MessageBox.Show("The Supplier has this Product already, please check it again!"); RefreshPordOfSuppAndLists(currentSupId); return; } } // insert the new record to the database try { using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { Products_Supplier newProdSupp = new Products_Supplier { ProductId = currentProdId, SupplierId = currentSupId }; dbContext.Products_Suppliers.InsertOnSubmit(newProdSupp); dbContext.SubmitChanges(); // refresh the product of supplier dataGridView and the two comboBoxes RefreshPordOfSuppAndLists(currentSupId); } } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } }
private void btnDelete_Click(object sender, EventArgs e) { // prompt user to confirm option DialogResult delete = MessageBox.Show("Are you sure you want to delete this package? " + "\n\n This will delete all attached product/supplier combos in this package.", "Delete Package", MessageBoxButtons.YesNo); if (delete == DialogResult.Yes) { try { using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { int packId = Convert.ToInt32(txtPackageID.Text); // query for package var package = (from pack in dbContext.Packages where pack.PackageId == packId select pack).Single(); // query for all prodsuppliers in that package var prodSupp = (from ps in dbContext.Packages_Products_Suppliers where ps.PackageId == packId select ps).ToList(); // delete all prod/supp combos for this package foreach (var ps in prodSupp) { dbContext.Packages_Products_Suppliers.DeleteOnSubmit(ps); } dbContext.Packages.DeleteOnSubmit(package); // delete the package and submit changes dbContext.SubmitChanges(); RefreshDisplay(); } } catch (Exception ex) { MessageBox.Show("An SQL error occured:\n\n" + ex.Message, ex.GetType().ToString()); } } }
// Delete a prod/supp combo private void btnDeletePPS_Click(object sender, EventArgs e) { // prompt user to confirm deletion DialogResult delete = MessageBox.Show("Are you sure you want to delete this Product/Supplier Combo?", "Delete Product/Supplier Combo", MessageBoxButtons.YesNo); if (delete == DialogResult.Yes) { int packageId = Convert.ToInt32(txtPackageID.Text); int psId = (int)PPSDataGridView[3, PPSDataGridView.CurrentCell.RowIndex].Value; try { using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { // get prod/supplier combo var pps = (from p in dbContext.Packages_Products_Suppliers where p.ProductSupplierId == psId && p.PackageId == packageId select p).Single(); // delete and submit dbContext.Packages_Products_Suppliers.DeleteOnSubmit(pps); dbContext.SubmitChanges(); DisplayPackageDetails(); } } catch (ChangeConflictException) { MessageBox.Show("Another user changed or deleted the current record", "Concurrency Exception"); DialogResult = DialogResult.Retry; } catch (Exception ex) { MessageBox.Show("An SQL error occured:\n\n" + ex.Message, ex.GetType().ToString()); } } }
// Delete a Product_Supplier relation in the database private void btnDelete_Click(object sender, EventArgs e) { // no supplier selected, give the user a warning message and return if (nProdSuppID == 0) { MessageBox.Show("Please select a Supplier from the List on the left-hand side please!"); return; } // get the current product Name and supplier Name for warning messages below int nRowNum = Convert.ToInt32(productDataGridView.CurrentCell.RowIndex); string strProductName = productDataGridView["Product", nRowNum].Value.ToString(); nRowNum = Convert.ToInt32(SupplierInGridView.CurrentCell.RowIndex); string strSupplierName = SupplierInGridView["Name", nRowNum].Value.ToString(); // Check if the product-supplier ID was attached to some packages. var objPackages = (from pkg in dbContext.Packages join pps in dbContext.Packages_Products_Suppliers on pkg.PackageId equals pps.PackageId where pps.ProductSupplierId == nProdSuppID select pkg.PkgName).ToList(); // if the product-supplier ID was attached to some packages, give warning message and return if (objPackages.Count > 0) { string strPackageList = String.Empty; foreach (string pkg in objPackages) { strPackageList += pkg + "\n"; } MessageBox.Show("The following packages have " + strProductName + " service provided by " + strSupplierName + ":\n" + strPackageList + "Please change the packages first."); return; } // confirmation with user, return if user choose "No" if (MessageBox.Show("Are you sure the company " + strSupplierName + " does not provide the " + strProductName + " service any more?", "Confirmation", MessageBoxButtons.YesNo) == DialogResult.No) { return; } // Delete the record from database try { var objToBeDeleted = (from prod_supp in dbContext.Products_Suppliers where prod_supp.ProductSupplierId == nProdSuppID select prod_supp).FirstOrDefault(); if (objToBeDeleted != null) { dbContext.Products_Suppliers.DeleteOnSubmit(objToBeDeleted); dbContext.SubmitChanges(); } } catch (Exception ex) { MessageBox.Show("Some error happened: " + ex.Message, ex.GetType().ToString()); } // refresh the 2 supplier lists DisplaySupplierOnProduct(nProductID); }
private void btnSave_Click(object sender, EventArgs e) { if ( Validator.IsPresent(txtSupplierId) && Validator.IsInt32(txtSupplierId) && Validator.IsMinLength(txtSupplierId, 2) && Validator.IsCorrectLength(txtSupplierId, 5) && Validator.IsPresent(txtSupplierName) && Validator.IsMaxLength(txtSupplierName, 200) ) { // confirm with user before writing data to the database String strConfirmation = isNewSupplier ? "Add a new Supplier called: " : "Update Supplier Name to: "; strConfirmation += txtSupplierName.Text.ToUpper() + "?"; if (MessageBox.Show(strConfirmation, "Confirmation", MessageBoxButtons.OKCancel) == DialogResult.Cancel) { return; } // write data to the database try { using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { Supplier supplier = null; if (isNewSupplier) // add a new supplier { string addSuppName = txtSupplierName.Text.ToUpper(); txtSupplierId.Enabled = true; int addSuppID = Convert.ToInt32(txtSupplierId.Text); // check for duplicate var checkForDuplicateName = dbContext.Suppliers.SingleOrDefault (sup => sup.SupName == addSuppName); var checkForDuplicateID = dbContext.Suppliers.SingleOrDefault (sup => sup.SupplierId == addSuppID); if (checkForDuplicateID != null) // tempSuppID already exist in DB { MessageBox.Show("SupplierID already exists in database", "Duplicated Data"); return; } if (checkForDuplicateName != null) // tempSuppName already exist in DB { MessageBox.Show(addSuppName + " already exists in database", "Duplicated Data"); return; } else { supplier = new Supplier { SupplierId = Convert.ToInt32(txtSupplierId.Text), SupName = txtSupplierName.Text.ToUpper() }; dbContext.Suppliers.InsertOnSubmit(supplier); } } else // edit supplier (isNewSupplier = false) { supplier = dbContext.Suppliers.Single(sup => sup.SupplierId == Convert.ToInt32(txtSupplierId.Text)); txtSupplierId.Enabled = false; supplier.SupName = txtSupplierName.Text.ToUpper(); } dbContext.SubmitChanges(); // save the changes to database } } catch (Exception ex) { MessageBox.Show("Some error occurred while writing data:\n" + ex.Message, ex.GetType().ToString()); } DialogResult = DialogResult.OK; } }
private void btnSave_Click(object sender, EventArgs e) { if (isAdd) { // All fields provided, ID unique. Dates younger than today and end > start if (Validator.IsPresent(txtPkgName) && Validator.IsPresent(txtPkgDesc) && Validator.IsMaxLength(txtPkgDesc, 50) && Validator.IsMinLength(txtPkgDesc, 10) && Validator.IsPresent(txtPkgBasePrice) && Validator.IsNonNegativeDecimal(txtPkgBasePrice) && Validator.IsPresent(txtPkgCom) && Validator.IsNonNegativeDecimal(txtPkgCom) && IsValidCommPrice(txtPkgCom) && IsValidEndDate(dtpEnd) ) { Package newPackage = new Package { PkgName = txtPkgName.Text, PkgDesc = txtPkgDesc.Text, PkgBasePrice = Convert.ToDecimal(txtPkgBasePrice.Text), PkgAgencyCommission = Convert.ToDecimal(txtPkgCom.Text), PkgStartDate = Convert.ToDateTime(dtpStart.Value), PkgEndDate = Convert.ToDateTime(dtpEnd.Value) }; try { using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { // insert newpackage into the DB dbContext.Packages.InsertOnSubmit(newPackage); dbContext.SubmitChanges(); } DialogResult = DialogResult.OK; } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } } else // if modify { if (Validator.IsPresent(txtPkgName) && Validator.IsPresent(txtPkgDesc) && Validator.IsMaxLength(txtPkgDesc, 50) && Validator.IsMinLength(txtPkgDesc, 10) && Validator.IsPresent(txtPkgBasePrice) && Validator.IsNonNegativeDecimal(txtPkgBasePrice) && Validator.IsPresent(txtPkgCom) && Validator.IsNonNegativeDecimal(txtPkgCom) && IsValidCommPrice(txtPkgCom) && IsValidEndDate(dtpEnd) ) { try { using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { Package pack = dbContext.Packages.Single(p => p.PackageId == GetPackageID()); // Check to see if package still exists, if it does, reassign values if (pack != null) { pack.PackageId = Convert.ToInt32(txtPackageID.Text); pack.PkgName = txtPkgName.Text; pack.PkgDesc = txtPkgDesc.Text; pack.PkgBasePrice = Convert.ToDecimal(txtPkgBasePrice.Text); pack.PkgAgencyCommission = Convert.ToDecimal(txtPkgCom.Text); pack.PkgStartDate = Convert.ToDateTime(dtpStart.Value); pack.PkgEndDate = Convert.ToDateTime(dtpEnd.Value); dbContext.SubmitChanges(); DialogResult = DialogResult.OK; } else { MessageBox.Show("Another user changed or deleted the current record", "Concurrency Exception"); DialogResult = DialogResult.Cancel; } } } catch (ChangeConflictException) { MessageBox.Show("Another user changed or deleted the current record", "Concurrency Exception"); DialogResult = DialogResult.Retry; } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } } }
private void btnSave_Click(object sender, EventArgs e) { int productID = Convert.ToInt32(cbProducts.SelectedValue); int supplierID = Convert.ToInt32(cbSuppliers.SelectedValue); int prodSuppID = GetPSID(productID, supplierID); if (isAddPPS) // if new PPS combo, then we add it to the list with a new ID. { if (Validator.IsSelectedCB(cbProducts) && Validator.IsSelectedCB(cbSuppliers) && Validator.IsValidID(prodSuppID) ) // check if both combo boxes have values { try { using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { Packages_Products_Supplier newPPS = new Packages_Products_Supplier { PackageId = packageID, ProductSupplierId = prodSuppID }; if (newPPS != null) { dbContext.Packages_Products_Suppliers.InsertOnSubmit(newPPS); dbContext.SubmitChanges(); } else { MessageBox.Show("This Product/Supplier combo already exists.", "Entry Error"); DialogResult = DialogResult.Retry; } } DialogResult = DialogResult.OK; } catch (ChangeConflictException) { MessageBox.Show("Another user changed or deleted the current record", "Concurrency Exception"); DialogResult = DialogResult.Retry; } catch (Exception ex) { MessageBox.Show("Product/Supplier combo already exists or an SQL error occured:\n\n" + ex.Message, ex.GetType().ToString()); } } } else // Modify. retrieve an already existing product supplier, and link it to a package { if (Validator.IsSelectedCB(cbProducts) && Validator.IsSelectedCB(cbSuppliers) && Validator.IsValidID(prodSuppID) ) // check if both combo boxes have values { try { using (TravelExpertDataDataContext dbContext = new TravelExpertDataDataContext()) { // get the pps row to be modified. It gets back a row that matches the package ID and old PSID. //Packages_Products_Supplier pps = dbContext.Packages_Products_Suppliers.Single(p => p.ProductSupplierId == oldPSID && p.PackageId == packageID); //Packages_Products_Supplier pps = dbContext.Packages_Products_Suppliers.Single // issue here //Packages_Products_Supplier pps = (from p in dbContext.Packages_Products_Suppliers // where p.ProductSupplierId == prodSuppID // && p.PackageId == Convert.ToInt32(txtPackageID.Text) // select p).Single(); var pps = (from p in dbContext.Packages_Products_Suppliers where p.ProductSupplierId == oldPSID && p.PackageId == packageID select p).Single(); pps.ProductSupplierId = prodSuppID; dbContext.SubmitChanges(); MessageBox.Show("Product/Supplier combo updated successfully.", "Success!"); DialogResult = DialogResult.OK; //if (pps != null) // if the product/supplier combo exist already, show error. //{ // MessageBox.Show("This record already exists.", "Concurrency Exception"); // DialogResult = DialogResult.Cancel; //} //else // change the product supplier ID //{ // pps.ProductSupplierId = prodSuppID; // reassign the prodSuppID; // dbContext.SubmitChanges(); // DialogResult = DialogResult.OK; // close form //} } } catch (ChangeConflictException) { MessageBox.Show("Another user changed or deleted the current record", "Concurrency Exception"); DialogResult = DialogResult.Retry; } catch (Exception ex) { MessageBox.Show("Product/Supplier combo already exists or an SQL error occured:\n\n" + ex.Message, ex.GetType().ToString()); } } } }