private void addPackageButton_Click(object sender, EventArgs e)
{
if (true) //replace true with data validation
{
//Create new Package with data from texctboxes
Package newPackage = new Package
{
PkgName = pkgNameTextBox.Text,
PkgStartDate = Convert.ToDateTime(pkgStartDateDateTimePicker.Text),
PkgEndDate = Convert.ToDateTime(pkgEndDateDateTimePicker.Text),
PkgBasePrice = Convert.ToDecimal(pkgBasePriceTextBox.Text),
PkgAgencyCommission = Convert.ToDecimal(pkgAgencyCommissionTextBox.Text),
PkgDesc = pkgDescTextBox.Text
};
//used dbContext from the main form to insert new product
mainForm.dbContext.Packages.InsertOnSubmit(newPackage);
mainForm.dbContext.SubmitChanges();
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
int i = 0;
foreach (int item in prdList)
{
//Adds a new Product_Supplier object in the Product_Supplier Table
Products_Supplier newProdSuppliers = new Products_Supplier();
newProdSuppliers.ProductId = prdList[i];
newProdSuppliers.SupplierId = suplList[i];
mainForm.dbContext.Products_Suppliers.InsertOnSubmit(newProdSuppliers);
mainForm.dbContext.SubmitChanges();
//Gets the Product Supplier Id that was just created after the Insertion
prodSuplList.Add(newProdSuppliers.ProductSupplierId);
//Adds a new Product_Supplier object in the Product_Supplier Table
Packages_Products_Supplier newPkgProdSupplier = new Packages_Products_Supplier();
newPkgProdSupplier.PackageId = newPackage.PackageId;
newPkgProdSupplier.ProductSupplierId = prodSuplList[i];
mainForm.dbContext.Packages_Products_Suppliers.InsertOnSubmit(newPkgProdSupplier);
mainForm.dbContext.SubmitChanges();
i++;
}
}
DialogResult = DialogResult.OK;
//Refresh the Grid View
}
}
private void detach_Packages_Products_Suppliers(Packages_Products_Supplier entity)
{
this.SendPropertyChanging();
entity.Products_Supplier = null;
}
partial void DeletePackages_Products_Supplier(Packages_Products_Supplier instance);
private void attach_Packages_Products_Suppliers(Packages_Products_Supplier entity)
{
this.SendPropertyChanging();
entity.Package = this;
}
partial void InsertPackages_Products_Supplier(Packages_Products_Supplier instance);
//Method used to save the new package in the dabase depending on whether
// the package is new or just edited
private void saveButton_Click(object sender, EventArgs e)
{
// clear error text
clearErrorText();
// validations
if (!validations())
{
return;
}
if (isAddMode) //replace true with data validation
{
//Create new Package with data from texctboxes
Package newPackage = new Package
{
PkgName = pkgNameTextBox.Text,
PkgStartDate = Convert.ToDateTime(pkgStartDateDateTimePicker.Text),
PkgEndDate = Convert.ToDateTime(pkgEndDateDateTimePicker.Text),
PkgBasePrice = Convert.ToDecimal(pkgBasePriceTextBox.Text),
PkgAgencyCommission = Convert.ToDecimal(pkgAgencyCommissionTextBox.Text),
PkgDesc = pkgDescTextBox.Text
};
//used dbContext from the main form to insert new product
mainForm.dbContext.Packages.InsertOnSubmit(newPackage);
mainForm.dbContext.SubmitChanges();
List <int> prodSuppIdList = new List <int>();
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
int i = 0;
foreach (int item in prdList)
{
////Adds a new Product_Supplier object in the Product_Supplier Table
//Products_Supplier newProdSuppliers = new Products_Supplier();
//newProdSuppliers.ProductId = prdList[i];
//newProdSuppliers.SupplierId = suplList[i];
int productID = prdList[i];
int supplierID = suplList[i];
var pkgProdSuppId = (from prodSup in dbContext.Products_Suppliers
where (prodSup.ProductId == prdList[i] &&
prodSup.SupplierId == suplList[i])
select new
{
productSupplierId = prodSup.ProductSupplierId
}).Take(1);
//Transforms the pkg
int Id = pkgProdSuppId.FirstOrDefault().productSupplierId;
//Gets the Product Supplier Id that was just created after the Insertion
//prodSuplList.Add(newProdSuppliers.ProductSupplierId);
//Adds a new Product_Supplier object in the Product_Supplier Table
Packages_Products_Supplier newPkgProdSupplier = new Packages_Products_Supplier();
newPkgProdSupplier.PackageId = newPackage.PackageId;
newPkgProdSupplier.ProductSupplierId = Id;
mainForm.dbContext.Packages_Products_Suppliers.InsertOnSubmit(newPkgProdSupplier);
mainForm.dbContext.SubmitChanges();
i++;
}
MessageBox.Show("Package Successuly Added to the List");
}
DialogResult = DialogResult.OK;
}
else
{
// check it is add or delete product/supply
if (isAddProSupp)
{
try
{
// write to Products_Suppliers table and get last number
for (int cnt = 0; cnt < addProdList.Count; cnt++)
{
string selProdName = addProdList[cnt];
string selSuppName = addSuppList[cnt];
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
var productId = (from prod in dbContext.Products
where prod.ProdName == selProdName
select new
{
prodId = prod.ProductId
}).Take(1);
//selected Product Id
int selProdId = productId.FirstOrDefault().prodId;
//Search for the supplier ID
var supplierId = (from sup in dbContext.Suppliers
where sup.SupName == selSuppName
select new
{
suppId = sup.SupplierId
}).Take(1);
int selSuppId = supplierId.FirstOrDefault().suppId;
//Products_Supplier modProdSup = new Products_Supplier // create product using provided data
//{
// ProductId = productDict.FirstOrDefault(x => x.Value == addProdList[cnt]).Key,
// SupplierId = supplierDict.FirstOrDefault(x => x.Value == addSuppList[cnt]).Key,
//};
// find ProductSupplierId in Products_Suppliers table
var pkgProdSuppId = (from prodSup in dbContext.Products_Suppliers
where (prodSup.ProductId == selProdId &&
prodSup.SupplierId == selSuppId)
select new
{
productSupplierId = prodSup.ProductSupplierId
}).Take(1);
//Transforms the pkg
addPkgProdSuppList.Add(pkgProdSuppId.FirstOrDefault().productSupplierId); //get Products_Supplier table index
}
}
// copy addProdList and addSuppList to prodList and suppList,
// then clear addProdList and addSuppList for next add
prodList.AddRange(addProdList);
suppList.AddRange(addSuppList);
// update package table
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
Package modPackage = dbContext.Packages.Single(u => u.PackageId == currentPackage.PackageId);
modPackage.PkgName = pkgNameTextBox.Text;
modPackage.PkgDesc = pkgDescTextBox.Text;
modPackage.PkgStartDate = pkgStartDateDateTimePicker.Value;
modPackage.PkgEndDate = pkgEndDateDateTimePicker.Value;
modPackage.PkgBasePrice = decimal.Parse(pkgBasePriceTextBox.Text);
modPackage.PkgAgencyCommission = decimal.Parse(pkgAgencyCommissionTextBox.Text);
dbContext.SubmitChanges(); // submit to the database
}
// write to Packages_Products_Suppliers table
// get the package id
for (int cnt = 0; cnt < addPkgProdSuppList.Count; cnt++)
{
Packages_Products_Supplier modPkgProdSup = new Packages_Products_Supplier // create product using provided data
{
PackageId = currentPackage.PackageId,
ProductSupplierId = addPkgProdSuppList[cnt],
};
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
// insert through data context object from the main form
dbContext.Packages_Products_Suppliers.InsertOnSubmit(modPkgProdSup);
dbContext.SubmitChanges(); // submit to the database
}
}
// write to Packages table
}
catch (Exception excp)
{
MessageBox.Show($"saving error when add product-supply. {excp.Message}");
return;
}
}
else
{
try
{
// create delete
// write to Products_Suppliers table and get last number
int test = delProSupNum;
string selProdName = prodList[delProSupNum];
string selSuppName = suppList[delProSupNum];
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
var productId = (from prod in dbContext.Products
where prod.ProdName == selProdName
select new
{
prodId = prod.ProductId
}).Take(1);
//selected Product Id
int selProdId = productId.FirstOrDefault().prodId;
//Search for the supplier ID
var supplierId = (from sup in dbContext.Suppliers
where sup.SupName == selSuppName
select new
{
suppId = sup.SupplierId
}).Take(1);
int selSuppId = supplierId.FirstOrDefault().suppId;
// get ProductSupplierId in Product_Supply table
Products_Supplier modProdSup = new Products_Supplier // create product using provided data
{
ProductId = selProdId,
SupplierId = selSuppId,
};
var id = (from pps in dbContext.Packages_Products_Suppliers
join i in (from ps in dbContext.Products_Suppliers
where ps.ProductId == selProdId && ps.SupplierId == selSuppId
select ps)
on pps.ProductSupplierId equals i.ProductSupplierId
where pps.PackageId == currentPackage.PackageId
select pps).Single();
// delete data in Packages_Products_Suppliers table
dbContext.Packages_Products_Suppliers.DeleteOnSubmit(id);
// delete data in Products_Suppliers table
//dbContext.Products_Suppliers.DeleteOnSubmit(modProdSup);
dbContext.SubmitChanges(); // submit to the database
addPkgProdSuppList.Add(modProdSup.ProductSupplierId); //get Products_Supplier table index
}
// remove from original list
prodList.RemoveAt(delProSupNum);
suppList.RemoveAt(delProSupNum);
}
catch (Exception excp)
{
MessageBox.Show($"saving error when remove product-supply. {excp.Message}");
return;
}
}
// comfirmation box
if (DialogResult.OK == MessageBox.Show($"The Package has been successfully modified, Thank you!",
"Confirmation",
MessageBoxButtons.OK))
{
DialogResult = DialogResult.OK;
}
}
}
