// DELETE DATA public static bool Delete(Products_Suppliers pSupplier) { bool result = false; SqlConnection con = Connection.GetConnection(); try { con.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "DELETE FROM Products_Suppliers WHERE ProductSupplierId = @id"; SqlParameter idPar = new SqlParameter("@id", SqlDbType.Int); cmd.Parameters.Add(idPar); cmd.ExecuteNonQuery(); result = true; } catch (Exception ex) { throw ex; } finally { con.Close(); } return(result); }
// UPDATE DATA public static bool Update(Products_Suppliers oldPsupplier, Products_Suppliers newPsupplier) { bool result = false; SqlConnection con = Connection.GetConnection(); try { con.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "UPDATE Products_Suppliers SET SupplierId = @newSid WHERE SupplierId = @oldSid AND ProductId = @oldPid"; SqlParameter newSidPar = new SqlParameter("@newSid", SqlDbType.Int); newSidPar.Value = newPsupplier.SupplierId; cmd.Parameters.Add(newSidPar); SqlParameter oldSidPar = new SqlParameter("@oldSid", SqlDbType.Int); oldSidPar.Value = oldPsupplier.SupplierId; cmd.Parameters.Add(oldSidPar); SqlParameter oldPIdPar = new SqlParameter("@oldPid", SqlDbType.Int); oldPIdPar.Value = oldPsupplier.ProductId; cmd.Parameters.Add(oldPIdPar); cmd.ExecuteNonQuery(); result = true; } catch (Exception ex) { throw ex; } finally { con.Close(); } return(result); }
{ // creatge a list to store the information public static List <Products_Suppliers> GetSuppliers() { Products_Suppliers pSupplier = null; SqlConnection conn = Connection.GetConnection(); string query = "SELECT ProductSupplierId, ProductId, SupplierId " + "FROM Products_Suppliers;"; SqlCommand cmd = new SqlCommand(query, conn); List <Products_Suppliers> psList = new List <Products_Suppliers>(); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { pSupplier = new Products_Suppliers((int)reader["ProductSupplierId"], (int)reader["ProductId"], (int)reader["SupplierId"]); psList.Add(pSupplier); } } catch (SqlException ex) { throw ex; } finally { conn.Close(); } return(psList); }
public ActionResult SupplierEdit(int id, FormCollection collection) { try { using (TravelExpertsEntities1 db = new TravelExpertsEntities1()) { Supplier editedSupp = db.Suppliers.Where(s => s.SupplierId == id).SingleOrDefault(); // get a reference to the record in the DB for editing editedSupp.SupName = collection["SupName"]; // update the supplier name // get a list of their current products List <Products_Suppliers> current = db.Products_Suppliers.Where(ps => ps.SupplierId == id).ToList(); // get the list of the products that were selected List <Products_Suppliers> selected = new List <Products_Suppliers>(); // create a placeholder list foreach (Product p in db.Products.ToList()) // go through each possible product { if (Convert.ToInt32(collection[p.ProdName]) == p.ProductId) // look for whether it was selected in the form { Products_Suppliers ps = new Products_Suppliers(); // if so, make a new products_supplier ps.ProductId = p.ProductId; // populate it with product ID ps.SupplierId = id; // and supplier ID selected.Add(ps); // add it to the list } } // for each current product foreach (Products_Suppliers ps in current) { // if it's not in the new list if (!selected.Contains(ps)) { // delete it from the DB db.Products_Suppliers.Remove(ps); } } // for each selected product foreach (Products_Suppliers ps in selected) { // if it's not in the old list if (!current.Contains(ps)) { // add it db.Products_Suppliers.Add(ps); } } db.SaveChanges(); // commit } return(RedirectToAction("SupplierIndex")); // go back to index } catch // if there's any problem, go back to the form so they can try again { return(View()); } }
private void btnAddSuppProd_Click(object sender, EventArgs e) { Products_Suppliers prodSupp = new Products_Suppliers(); prodSupp.ProductId = Convert.ToInt32(cbProductforCombination.SelectedValue); prodSupp.SupplierId = Convert.ToInt32(cbSupplierIDForCombined.SelectedValue); Products_SuppliersDB.AddProductsSuppliers(prodSupp); lblAddSuppProd.Text = "Supplier/Product Combo has been added"; supplierDataGridView.DataSource = SupplierDB.GetSuppliers(); }
// INSERT DATA public static int Insert(Products_Suppliers pSupplier) { int result = -1; SqlConnection con = Connection.GetConnection(); try { con.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = "INSERT INTO Products_Suppliers (ProductId, SupplierId) OUTPUT INSERTED.ProductSupplierId values (@pId, @sId)"; SqlParameter pIdPar = new SqlParameter("@pId", SqlDbType.Int); pIdPar.Value = pSupplier.ProductId; cmd.Parameters.Add(pIdPar); SqlParameter sIdPar = new SqlParameter("@sId", SqlDbType.Int); sIdPar.Value = pSupplier.SupplierId; cmd.Parameters.Add(sIdPar); Int32 newID = (Int32)cmd.ExecuteScalar(); result = newID; } catch (SqlException e) { switch (e.Number) { case 2627: throw new DuplicateKeyException(string.Format("Duplicate ID entry, please enter a different ID and try again {0}", pSupplier.SupplierId)); default: throw; } } catch (Exception ex) { throw ex; } finally { con.Close(); } return(result); }
private void products_SuppliersDataGridView_CellContentClick(object sender, DataGridViewCellEventArgs e) { if (e.ColumnIndex == SUPP_PROD_EDIT) { Products_Suppliers oldSuppProd = productSupplierList[e.RowIndex].CopyCombo(); UpdateProductSuppliers updateForm = new UpdateProductSuppliers(); updateForm.newSuppProd = productSupplierList[e.RowIndex]; //passing the current customer to the form updateForm.oldSuppProd = oldSuppProd; DialogResult result = updateForm.ShowDialog(); //display modal second form if (result == DialogResult.OK) //Update Accepted { //refresh the grid view CurrencyManager cm = (CurrencyManager)products_SuppliersDataGridView.BindingContext[SupplierDB.GetSuppliers()]; cm.Refresh(); } else//Update cancelled { productSupplierList[e.RowIndex] = oldSuppProd; //revert to old customer data } } }
public ActionResult SupplierCreate(Supplier supp, FormCollection collection) { try { using (TravelExpertsEntities1 db = new TravelExpertsEntities1()) { db.Suppliers.Add(supp); // add the new supplier to the database List <Products_Suppliers> prodlist = new List <Products_Suppliers>(); // a list to hold their product offerings foreach (Product prod in db.Products.ToList()) // for each potential product that could exist { if (Convert.ToInt32(collection[prod.ProdName]) == prod.ProductId) // see if its box was checked in the form { Products_Suppliers offering = new Products_Suppliers(); // make a new object to hold the offering details offering.SupplierId = supp.SupplierId; offering.ProductId = prod.ProductId; db.Products_Suppliers.Add(offering); // add it to the database } } db.SaveChanges(); // commit } TempData["Status"] = ""; return(RedirectToAction("SupplierIndex")); // go back to the supplier listing } catch (Exception ex) { // this isn't being caught as an "SqlException" for some reason, so have to resort to this if (ex.GetBaseException().GetType().ToString() == "System.Data.SqlClient.SqlException") // if they tried to use a duplicate primary key { TempData["Status"] = "That supplier ID is already in use. Please choose another number."; } else // otherwise... { TempData["Status"] = ex.GetBaseException().GetType().ToString() + ": " + ex.GetBaseException().Message; } return(View()); // go back to the creation page for them to try again } }
public ActionResult PackageEdit(int id, FormCollection collection) { TempData["Status"] = ""; // reset any error message // first: make sure dates are valid DateTime?start = Convert.ToDateTime(collection["PkgStartDate"]); // get the dates DateTime?end = Convert.ToDateTime(collection["PkgEndDate"]); // if the start is after the end if (start > end) { TempData["Status"] = "Start date must come before end date"; return(RedirectToAction("PackageEdit")); // go back to the page for correction } // then update the DB try { using (TravelExpertsEntities1 db = new TravelExpertsEntities1()) { Package editedPkg = db.Packages.Where(p => p.PackageId == id).SingleOrDefault(); // get this record from the DB // update all the fields editedPkg.PkgAgencyCommission = Convert.ToDecimal(collection["PkgAgencyCommission"]); editedPkg.PkgBasePrice = Convert.ToDecimal(collection["PkgBasePrice"]); editedPkg.PkgDesc = collection["PkgDesc"]; editedPkg.PkgEndDate = Convert.ToDateTime(collection["PkgEndDate"]); editedPkg.PkgImageFile = collection["PkgImageFile"]; editedPkg.PkgName = collection["PkgName"]; editedPkg.PkgStartDate = Convert.ToDateTime(collection["PkgStartDate"]); List <Products_Suppliers> changes = new List <Products_Suppliers>();// somewhere to put the changes we're making below // any product_supplier that's in the original package but not in the form selections, remove foreach (Products_Suppliers ps in editedPkg.Products_Suppliers) { int suppID = Convert.ToInt32(collection[ps.Product.ProdName]); // get the drop down matching that product name in the form if (!(suppID > 0)) // if a supplier wasn't selected { changes.Add(ps); // put on the list of things to remove } } foreach (Products_Suppliers ps in changes)// remove them from the db reference { editedPkg.Products_Suppliers.Remove(ps); } changes.Clear(); // clear the list for the next part // anything that's in the form selections but not in the original package, add foreach (Product p in db.Products.ToList()) { int suppID = Convert.ToInt32(collection[p.ProdName]); // get the drop down matching that product name in the form if (suppID > 0) // if a supplier was selected { // get that product_supplier from the DB Products_Suppliers testCase = db.Products_Suppliers.Where(ps => ps.SupplierId == suppID && ps.ProductId == p.ProductId).Single(); if (!editedPkg.Products_Suppliers.Contains(testCase)) // if it's not in the original list { changes.Add(testCase); // add it to the list of changes } } } foreach (Products_Suppliers ps in changes) { editedPkg.Products_Suppliers.Add(ps); // add it to the db reference } db.SaveChanges(); // commit } return(RedirectToAction("PackageIndex")); } catch (Exception ex) // if something went wrong, return them to this page to try again { TempData["Status"] = ex.GetType().ToString() + ": " + ex.Message; return(View()); } }