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