// 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());
}
}
