// Takes current selected Product_Supplier and adds it to the package [Ronnie] private void btnAdd_Click(object sender, EventArgs e) { // Get ProductSupplierID from combobox int prodSupID = Convert.ToInt32(productSupplierIdTextBox.Text); int packageID = currentPackage.PackageId; // if the current product and supplier combination does not exist for the package, add it to the database if (!TravelExpertsQueryManager.ExistPackagesProductsSupplier(packageID, prodSupID)) { // Create a PackagesProductSupplier with that ID, and the Package ID from the current package Packages_Products_Supplier newPackProdSup = new Packages_Products_Supplier { ProductSupplierId = prodSupID, PackageId = packageID }; // Add that PackagesProductsSupplier to the db using (travelexpertsDataContext dbContext = new travelexpertsDataContext()) { // insert through data context object from the main form dbContext.Packages_Products_Suppliers.InsertOnSubmit(newPackProdSup); dbContext.SubmitChanges(); // submit to the database } // Re-load the datagrid view refreshDataGrid(); } else { MessageBox.Show("The selected product and supplier portfolio has already been added to the package. Please try again.", "Input Error"); } }
public static List <Packages_Products_Supplier> GetPackages_Products_Suppliers() { SqlConnection connection = TravelExpertsDB.GetConnection(); List <Packages_Products_Supplier> results = new List <Packages_Products_Supplier>(); try { string sql = "SELECT * FROM Packages_Products_Suppliers"; SqlCommand command = new SqlCommand(sql, connection); SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection); while (reader.Read()) { Packages_Products_Supplier s = new Packages_Products_Supplier(); s.PackageId = Convert.ToInt32(reader["PackageId"]); s.ProductSupplierId = Convert.ToInt32(reader["ProductId"]); results.Add(s); } } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(results); }
//Delete from GridView so it won't add to database [Ronnie] private void btnDelete_Click(object sender, EventArgs e) { // Grab data from selected cell on gridview int rowNum = grdProdSup.CurrentCell.RowIndex; // index of the current row int prodSuppID = Convert.ToInt32(grdProdSup[0, rowNum].Value); // Column for ProductSupplierID using (travelexpertsDataContext dbContext = new travelexpertsDataContext()) { // Packages_Products_Supplier packProdSupForRemoval = (from match in dbContext.Packages_Products_Suppliers where (match.ProductSupplierId == prodSuppID && match.PackageId == currentPackage.PackageId) select match).Single(); dbContext.Packages_Products_Suppliers.DeleteOnSubmit(packProdSupForRemoval); dbContext.SubmitChanges(); // submit to the database } refreshDataGrid(); }
/// <summary> /// On clicking drop products from packages /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btnDropPro_Click(object sender, EventArgs e) { //only activate button if that datagrid view has some elements if (dgViewSubPack.Rows.Count > 0) { int rowIndex = dgPackageProductSuppliers.CurrentRow.Index; //needed to preserve state int rowIndexSec = dgProd.CurrentRow.Index; //Just to preserve state Package selectedPackage = packages[rowIndex]; //Package to drop from Packages_Products_Supplier package_product_supplier = packages[rowIndex].packProd[dgViewSubPack.CurrentRow.Index]; //product supplier to drop DialogResult resultPackageProductSupplier = MessageBox.Show("You are about to remove the product " + package_product_supplier.ProdName + " by \n\n" + package_product_supplier.SupName + " from " + selectedPackage.Name + " ? ", "Confirm Remove", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (resultPackageProductSupplier == DialogResult.Yes) { try { //if not concurrent if (!Packages_Products_SuppliersDB.DeletePackages_Products_Supplier(package_product_supplier)) { MessageBox.Show("Another user has updated or deleted " + "that product.", "Database Error"); } //else refresh page else { this.displayAll(rowIndex, rowIndexSec, true); } } //Catch any other error catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } } }
// Go back to last page without saving changes. This may involve some cleaning up, depending on the mode and if products were added [Eric] private void btnCancel_Click(object sender, EventArgs e) { using (travelexpertsDataContext dbContext = new travelexpertsDataContext()) { // First, we have to check to see if any products were added to the package before cancelling if (didAddProducts) // this will be true if so - no need to spend time querying the db { // Get the current PPS entries in the database corresponsind to this package List <Packages_Products_Supplier> ppsCurrent = TravelExpertsQueryManager.GetPackagesProductsSuppliersByPackageID(currentPackage.PackageId); // Next, we have to get the PPS entries to re-add (if they were deleted) and/or remove (if new ones were added) // This is not a super efficient process, but packages shouldn't have enough products for it to make much difference // First, get the ones to re-add List <Packages_Products_Supplier> ppsToAdd = ppsSnapshot // Creating a list of Package_Product_Suppliers where... .Where(ppsSnap => !ppsCurrent // ...for each snapshot entry, it is NOT the case that... .Any(ppsCurr => ppsCurr.ProductSupplierId == ppsSnap.ProductSupplierId)) // ...any current entry has that snapshot entry's ProductSupplierID .ToList(); // Next, the ones to remove List <Packages_Products_Supplier> ppsToDelete = ppsCurrent // Creating a list of Package_Product_Suppliers where... .Where(ppsCurr => !ppsSnapshot // ...for each current entry, it is NOT the case that... .Any(ppsSnap => ppsCurr.ProductSupplierId == ppsSnap.ProductSupplierId)) // ...any snapshot entry has that current entry's ProductSupplierID .ToList(); // Add the needed entries back foreach (Packages_Products_Supplier ppsA in ppsToAdd) { // LINQ to SQL doesn't let you re-add old entity objects, so we need to create copies to add back in place Packages_Products_Supplier clone = new Packages_Products_Supplier { PackageId = ppsA.PackageId, ProductSupplierId = ppsA.ProductSupplierId }; dbContext.Packages_Products_Suppliers.InsertOnSubmit(clone); } // Delete the entries to undo foreach (Packages_Products_Supplier ppsD in ppsToDelete) { // Deleting only works on entities from the current context, so need to grab them // I'm sure this could be done in the ones-to-remove LINQ query above, but I couldn't manage it Packages_Products_Supplier deleteTarget = dbContext.Packages_Products_Suppliers // Search in the table... .Single(pps => // ...for the one entry, with... pps.ProductSupplierId == ppsD.ProductSupplierId && //...the matching ProductSupplierID... pps.PackageId == ppsD.PackageId); //... and the matching PackageID dbContext.Packages_Products_Suppliers.DeleteOnSubmit(deleteTarget); } // Save changes. Phew! dbContext.SubmitChanges(); } // One more step if in Add mode. // A package was inserted into the database initially (to get the add products half to work). // So, if cancelling, we want to delete it this created package. if (isAdd) { // Delete package Package packToDelete = dbContext.Packages .Single(p => p.PackageId == currentPackage.PackageId); dbContext.Packages.DeleteOnSubmit(packToDelete); dbContext.SubmitChanges(); // submit to the database } } // Exit the form DialogResult = DialogResult.Cancel; this.Close(); }
// Modifies the ProductSupplier associated with the selected row of the datagrid using the user inputs [Eric] private void btnModifyProdSupp_Click(object sender, EventArgs e) { // Get user inputs int newProdId = Convert.ToInt32(productIdComboBox.SelectedValue); int newSupID = Convert.ToInt32(supplierIdComboBox.SelectedValue); // The user may not have selected a value for the supplierIDComboBox yet, if so, they want the visible (top) one if (newSupID == 0) { supplierIdComboBox.SelectedIndex = 0; newSupID = Convert.ToInt32(supplierIdComboBox.SelectedValue); } // grab data from dropdown selectedvalues & hidden id field (productSupplierIdTextBox) using (travelexpertsDataContext db = new travelexpertsDataContext()) { if (addMode == false) // if modifying { // Get current product supplier ID int prodSupID = Convert.ToInt32(productSupplierIdTextBox.Text); // Grab the current entry from the database Products_Supplier prodSup = db.Products_Suppliers .Single(ps => ps.ProductSupplierId == prodSupID); // Validate by ensuring this unique combination isn't in the database Products_Supplier matchingProps = Validator.prodSupComboAlreadyExists(db, newProdId, newSupID, prodSupID); if (matchingProps == null) { // Update with inputted values prodSup.ProductId = newProdId; prodSup.SupplierId = newSupID; db.SubmitChanges(); } else // there is a match for the product/supplier combo { //Give the user the option to change to this combination anyway (this will move all associated packages to the matching Product_Supplier) DialogResult result = MessageBox.Show($"That product/supplier combination already exists (ID #{matchingProps.ProductSupplierId} - {matchingProps.Product.ProdName} - {matchingProps.Supplier.SupName}). Would you like to change any associated packages to have the selected product?", "Existing Product/Supplier", MessageBoxButtons.YesNo); if (result == DialogResult.Yes) { // Get all Package_Product_Suppliers entries that refer to the current ProdSup ID (these will need to be modified) List <Packages_Products_Supplier> ppsWithCurrentProdSupID = db.Packages_Products_Suppliers.Where(pps => pps.ProductSupplierId == prodSupID).ToList(); // Go through each, updating the ProdSupID to the existing combination foreach (Packages_Products_Supplier pps in ppsWithCurrentProdSupID) { // We can't directly update ProdSupID in an existing entry as it is part of the entry's Primary key. //Instead, we have to delete it and create a new one. int currentPackageId = pps.PackageId; // to keep track of the package ID db.Packages_Products_Suppliers.DeleteOnSubmit(pps); db.SubmitChanges(); // have to submit changes here otherwise we can't create a new one try { using (travelexpertsDataContext db2 = new travelexpertsDataContext()) { // Now, create a new PPS using the PPS and the new ProdSupID Packages_Products_Supplier newPps = new Packages_Products_Supplier { PackageId = currentPackageId, // the same package id ProductSupplierId = matchingProps.ProductSupplierId // the prod_sup id that matches what the user wants to change it to }; db2.Packages_Products_Suppliers.InsertOnSubmit(newPps); db2.SubmitChanges(); } } catch (Exception ex) { MessageBox.Show(ex.Message, ex.GetType().ToString()); } } MessageBox.Show($"Any associated packages successfully transffered to ID# {matchingProps.ProductSupplierId} - {matchingProps.Product.ProdName} - {matchingProps.Supplier.SupName})"); } } // Reload data checkboxFilterProducts_CheckedChanged(sender, e);//this updates the main datagrid based on whether the filter is on RefreshPackagesByProdSuppGrid(); RefreshProdSupId(); } // end modify else // if in add mode { // Validate to ensure the combo is new Products_Supplier match = Validator.prodSupComboAlreadyExists(db, newProdId, newSupID); if (match == null) //if new { // create a new Product_Supplier with the data Products_Supplier newProdSup = new Products_Supplier { ProductId = newProdId, SupplierId = newSupID }; // insert into db and save db.Products_Suppliers.InsertOnSubmit(newProdSup); db.SubmitChanges(); // Re-enable Add new button btnAddProdSupp.Enabled = true; // Reload main data checkboxFilterProducts_CheckedChanged(sender, e); //updates main datagrid based on status of filter checkbox // Go to the new entry in the gridview checkboxFilterProducts.Checked = false; // uncheck filter so new product can be seen int lastIndex = grdProductSuppliers.Rows.Count - 1; // get last row of the grid grdProductSuppliers.Rows[lastIndex].Selected = true; // select it grdProductSuppliers.FirstDisplayedScrollingRowIndex = lastIndex; // go down to it grdProductSuppliers_CellClick(sender, new DataGridViewCellEventArgs(1, lastIndex)); // Reload related data RefreshPackagesByProdSuppGrid(); productIdComboBox_SelectedIndexChanged(sender, e); RefreshProdSupId(); } else // if the combo already exists { // Alert the user MessageBox.Show($"That product/supplier combination already exists (ID #{match.ProductSupplierId})."); } } } }