/// <summary> /// Get Products and Suppliers details from a list of Products /// </summary> /// <param name="lstProd"></param> /// <returns>List of Products_Suppilers</returns> public List <Products_Supplier> GetProducts_Suppliers(List <Product> lstProd) { // set up DataAccess TravelExpertsDataContext dbContext = new TravelExpertsDataContext(); Products_Supplier ps; // temp Product_Supplier object List <Products_Supplier> lstPS = new List <Products_Supplier>(); // temp list // get ProductSupplierID based on ProductID foreach (Product pd in lstProd) { ps = new Products_Supplier(); // create a new object each iteration // get the ProductSupplierId based on the ProductID of the current item ps.ProductSupplierId = Convert.ToInt32((from p in dbContext.Products_Suppliers where p.ProductId == pd.ProductId select p.ProductSupplierId).First()); ps.ProductId = pd.ProductId; // set ProductID lstPS.Add(ps); // add it to the list } return(lstPS); //return the list of Product_Supplier }
/// <summary> /// When Save button clicked, proceed based on whether add or edit, validate inputs, then store in DB /// </summary> private void btnSave_Click(object sender, EventArgs e) { int suppID; // to hold the supplier ID later // global validations: are there assigned products? is there a name? if (lbAssigned.Items.Count == 0) // if no products are assigned { MessageBox.Show("The supplier must have at least one assigned product", "Missing data"); return; } else if (txtSuppName.Text.Length == 0) // if no name { MessageBox.Show("The supplier must have a name", "Missing data"); txtSuppName.Focus(); return; } else if (!Int32.TryParse(txtSuppID.Text, out suppID)) // if the ID isn't a valid integer { MessageBox.Show("The supplier ID must be a number without decimals", "Incorrect data"); txtSuppID.Text = ""; txtSuppID.Focus(); return; } try { using (TravelExpertsDataContext db = new TravelExpertsDataContext()) { if (isNew) // if we're adding a new record { // new record validations if (txtSuppID.Text.Length == 0) // if no ID { MessageBox.Show("The supplier must have an ID", "Missing data"); txtSuppID.Focus(); return; } // check if the supplier ID is already used // look in the DB for the number of records with that ID -- expecting 0 or 1 int checkID = (from s in db.Suppliers where s.SupplierId == suppID select s).Count(); if (checkID > null) // if it found something, do an error { MessageBox.Show("The supplier ID is already in use. Please use another", "Incorrect data"); txtSuppID.Text = ""; txtSuppID.Focus(); return; } // make a new supplier object and give it the properties the user entered Supplier newSupp = new Supplier(); newSupp.SupplierId = suppID; newSupp.SupName = txtSuppName.Text; // put it in the DB db.Suppliers.InsertOnSubmit(newSupp); // add the products to the Products_Suppliers table foreach (Product p in lbAssigned.Items) { // create a new product-supplier record and give it the product and supplier IDs Products_Supplier ps = new Products_Supplier(); ps.ProductId = p.ProductId; ps.SupplierId = suppID; // put it in the table db.Products_Suppliers.InsertOnSubmit(ps); } } else // if we're editing an existing record { // delete removed entries foreach (Product p in originalProdList) { if (!lbAssigned.Items.Contains(p)) { // find the record to delete Products_Supplier deletedRecord = (Products_Supplier)db.Products_Suppliers.Where(ps => (ps.SupplierId == suppID && ps.ProductId == p.ProductId)).Single(); // delete it from the DB db.Products_Suppliers.DeleteOnSubmit(deletedRecord); } } // add new entries foreach (Product p in lbAssigned.Items) { if (!originalProdList.Contains(p)) { // create the record to add, with product ID and supplier ID Products_Supplier addedRecord = new Products_Supplier(); addedRecord.ProductId = p.ProductId; addedRecord.SupplierId = suppID; // add it to the DB db.Products_Suppliers.InsertOnSubmit(addedRecord); } } // update the name in suppliers table (ID can't be changed) Supplier curSupp = db.Suppliers.Single(s => s.SupplierId == suppID); curSupp.SupName = txtSuppName.Text; } db.SubmitChanges(); // make the changes happen if we've got to this point with no problems. } } catch (SqlException) // this will be thrown if there's a foreign key constraint problem { MessageBox.Show("Problem Saving Changes: One of the products you're trying to remove from this supplier is assigned to a package. " + "Please remove this supplier's product from the package first, then try again", "Product In Use"); return; } catch (Exception ex) // generic exception catching { MessageBox.Show("Problem saving to database: " + ex.Message, ex.GetType().ToString()); } finally { // update the DGV LoadSuppliers(); if (!isNew) // if completing an edit, reload the details { LoadSupplierDetails(suppID); } else // if completing an add, clear to prepare for the next new supplier { PrepareNew(); } } }
/// <summary> /// Fires when Save is clicked /// </summary> private void btnSave_Click(object sender, EventArgs e) { // set booleans for various checks later on bool allGoodAdd = false; // True when adding Packages_Products_Suppliers records succesfully bool allGoodRmv = false; // True when deleting Packages_Products_Suppliers records succesfully bool noItems = false; // set to true if we have no items (no changes in Products of package) // temporary lists and objects List <Products_Supplier> prodsToAdd = new List <Products_Supplier>(); Packages_Products_Supplier ppsd = new Packages_Products_Supplier(); List <Packages_Products_Supplier> ppsdList = new List <Packages_Products_Supplier>(); Products_Supplier ps = new Products_Supplier(); // setup DataAccess TravelExpertsDataContext dbContext = new TravelExpertsDataContext(); // if we are not adding a new record if (!isAdd) { try { // Add new products to existing record if (addProd.Count > 0) { // create lists for Products_Suppliers, Packages_Products_Suppliers prodsToAdd = GetProducts_Suppliers(addProd); ppsdList = GetPackages_Products_Suppliers(prodsToAdd); // call the Save method, indicating true for save allGoodAdd = Save_Packages_Products_Suppliers(ppsdList, true); } else { noItems = true; // no items to save } //Remove products from existing package if (rmvProd.Count > 0) { // create lists for Products_Suppliers, Packages_Products_Suppliers prodsToAdd = GetProducts_Suppliers(rmvProd); ppsdList = GetPackages_Products_Suppliers(prodsToAdd); // call the Save method, indicating false for delete allGoodRmv = Save_Packages_Products_Suppliers(ppsdList, false); } else { noItems = true; // no items to remove } // Save main record detail // if there are noItems or Add/Remove methods were successful if (noItems || allGoodAdd || allGoodRmv) { // setup variables for later use decimal basePrice, agcyComm; // create a Package object with detail from DB based on PackageID Package pkg = dbContext.Packages.Single(p => p.PackageId == Convert.ToInt32(txtPackageID.Text)); // set the various attributes of the object from form controls pkg.PkgName = txtPkgName.Text; pkg.PkgDesc = txtPkgDesc.Text; pkg.PkgStartDate = dtpPkgStart.Value.Date; pkg.PkgEndDate = dtpPkgEnd.Value.Date; //if (pkg.PkgStartDate < pkg.PkgEndDate) //{ if (txtPkgBase.Text.StartsWith("$")) // remove the leading $ if it exists { basePrice = Convert.ToDecimal(txtPkgBase.Text.Remove(0, 1)); } else { basePrice = Convert.ToDecimal(txtPkgBase.Text); } if (txtPakComm.Text.StartsWith("$")) // remove the leading $ if it exists { agcyComm = Convert.ToDecimal(txtPakComm.Text.Remove(0, 1)); } else { agcyComm = Convert.ToDecimal(txtPakComm.Text); } //set the object attributes pkg.PkgBasePrice = basePrice; pkg.PkgAgencyCommission = agcyComm; if (basePrice > agcyComm) // check the Commision is not more than the base price { dbContext.SubmitChanges(); // save the changes } else { MessageBox.Show("Agency Commision is too high"); } //} } else { MessageBox.Show("An error occurred saving the data, tasks cancelled"); } } catch (ChangeConflictException) { // if we have concurency exceptions, resolve them and contine the save dbContext.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges); dbContext.SubmitChanges(); } catch (Exception ex) { MessageBox.Show(ex.Message + " - " + ex.ToString()); } } else // this is a new Package { // create lists, objects and variables needed later prodsToAdd = GetProducts_Suppliers(addProd); ppsdList = GetPackages_Products_Suppliers(prodsToAdd); allGoodAdd = Save_Packages_Products_Suppliers(ppsdList, true); decimal basePrice, agcyComm; Package pkg = new Package(); // create a new Package object // set object attributes based on form controls pkg.PkgName = txtPkgName.Text; pkg.PkgDesc = txtPkgDesc.Text; pkg.PkgStartDate = dtpPkgStart.Value.Date; pkg.PkgEndDate = dtpPkgEnd.Value.Date; if (txtPkgBase.Text.StartsWith("$")) // remove the leading $ if it exists { basePrice = Convert.ToDecimal(txtPkgBase.Text.Remove(0, 1)); } else { basePrice = Convert.ToDecimal(txtPkgBase.Text); } if (txtPakComm.Text.StartsWith("$")) // remove the leading $ if it exists { agcyComm = Convert.ToDecimal(txtPakComm.Text.Remove(0, 1)); } else { agcyComm = Convert.ToDecimal(txtPakComm.Text); } // set object attributes pkg.PkgBasePrice = basePrice; pkg.PkgAgencyCommission = agcyComm; if (basePrice > agcyComm) // ensure commision is less than base price { dbContext.Packages.InsertOnSubmit(pkg); dbContext.SubmitChanges(); // submit the changes to the DB currPkg = (from pk in dbContext.Packages where pk.PkgName == pkg.PkgName select pk).Single(); } else { MessageBox.Show("Agency Commision is too high"); } // need to retrieve the newly created package ID prodsToAdd = GetProducts_Suppliers(addProd); ppsdList = GetPackages_Products_Suppliers(prodsToAdd); allGoodAdd = Save_Packages_Products_Suppliers(ppsdList, true); NewOrClear(); } LoadDGV(); lbAvail.Items.Clear(); gbDetails.Enabled = false; }
private void detach_Products_Suppliers(Products_Supplier entity) { this.SendPropertyChanging(); entity.Product = null; }
private void attach_Products_Suppliers(Products_Supplier entity) { this.SendPropertyChanging(); entity.Supplier = this; }
partial void DeleteProducts_Supplier(Products_Supplier instance);
partial void UpdateProducts_Supplier(Products_Supplier instance);
partial void InsertProducts_Supplier(Products_Supplier instance);