//Method to used to change combobox suppliers based on the selected product
private void prodComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
//int selectedId = (int)(prodComboBox.SelectedValue);
//MessageBox.Show("Selected ID" + selectedId);
//supComboSelection(dbContext, selectedId);
int selectedID;
if (prodComboBox.SelectedIndex > 0)
{
string selectedProd = prodComboBox.Text;
var ProdId = (from prod in dbContext.Products
where (prod.ProdName == selectedProd)
select new
{
productId = prod.ProductId
}).Take(1);
//Transforms the pkg
selectedID = ProdId.FirstOrDefault().productId;
}
else if (prodComboBox.SelectedIndex == 0)
{
selectedID = 1;
}
else
{
return;
}
supComboSelection(dbContext, selectedID);
}
}
private void prodNameComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
int selectedId = prodNameComboBox.SelectedIndex + 1;
TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext();
var listSuppliers = (from x in dbContext.Suppliers select x).ToList();
//selects the product suppliers using an sql query and joining three tables
var productSuppliers = (from prod in dbContext.Products
join prodSup in dbContext.Products_Suppliers
on prod.ProductId equals prodSup.ProductId
join sup in dbContext.Suppliers
on prodSup.SupplierId equals sup.SupplierId
where prod.ProductId == selectedId
select new
{
supplierId = sup.SupplierId,
supName = sup.SupName
}).ToList();
//int dummy = productSuppliers[0].supId;
supNameComboBox.DataSource = productSuppliers;
supNameComboBox.DisplayMember = "SupName";
supNameComboBox.ValueMember = "SupplierId";
}
/* only for test*/
//private void loginButton_Click(object sender, EventArgs e)
//{
// MainForm mf = new MainForm();
// mf.Show();
//}
private void loginButton_Click(object sender, EventArgs e)
{
if (isValid(usernameTextBox.Text.Trim(), passwordTextBox.Text.Trim()))
{
username = usernameTextBox.Text.Trim();
password = passwordTextBox.Text.Trim();
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
var login = dbContext.Agents.Where(agent => agent.Username == username &&
agent.Password == password).FirstOrDefault();
if (login != null)
{
MainForm mf = new MainForm();
mf.Show();
}
else
{
MessageBox.Show("Username or Password is not correct");
return;
}
}
}
else
{
MessageBox.Show("Please enter valid username and password");
}
}
//Method used to load the combo boxes
private void LoadCombos()
{
if (isAddMode) // Add mode
{
TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext();
var listProducts = (from x in dbContext.Products select x).ToList();
//Populate the Product List ComboBox
prodNameComboBox.DataSource = listProducts;
prodNameComboBox.ValueMember = "ProductID";
prodNameComboBox.DisplayMember = "ProdName";
prodNameComboBox.SelectedIndex = -1;
//Populate the Supplier List Combo Box
var listSuppliers = (from x in dbContext.Suppliers select x).ToList();
supNameComboBox.DataSource = listSuppliers;
supNameComboBox.ValueMember = "SupplierId";
supNameComboBox.DisplayMember = "SupName";
supNameComboBox.SelectedIndex = -1;
}
else
{
loadProductsCombo();
loadSuppliersCombo();
}
}
private void button1_Click(object sender, EventArgs e)
{
TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext();
int prd = (int)prodNameComboBox.SelectedValue;
prdList.Add(prd); // Stores the selected product in a list
var prodNameList = (from prod in dbContext.Products
where prod.ProductId == prd
select new
{
name = prod.ProdName
}).ToList();
string prodName = prodNameList[0].name;
int supl = (int)supNameComboBox.SelectedValue;
suplList.Add(supl);
var suppNameList = (from sup in dbContext.Suppliers
where sup.SupplierId == supl
select new
{
name = sup.SupName
}).ToList();
string suppName = suppNameList[0].name;
string item = prodName + " | " + suppName;
productsSuppliersListBox.Items.Add(item);
}
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
}
}
//Method used to add a product and supplier in the list box
private void addProductButton_Click(object sender, EventArgs e)
{
if (prodNameComboBox.SelectedItem == null || supNameComboBox.SelectedItem == null)
{
MessageBox.Show("Please select one product and supplier");
return;
}
if (isAddMode) // Add mode
{
TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext();
int prd = (int)prodNameComboBox.SelectedValue;
prdList.Add(prd); // Stores the selected product in a list
var prodNameList = (from prod in dbContext.Products
where prod.ProductId == prd
select new
{
name = prod.ProdName
}).ToList();
string prodName = prodNameList[0].name;
int supl = (int)supNameComboBox.SelectedValue;
suplList.Add(supl);
var suppNameList = (from sup in dbContext.Suppliers
where sup.SupplierId == supl
select new
{
name = sup.SupName
}).ToList();
string suppName = suppNameList[0].name;
string item = prodName + " | " + suppName;
prodSuppListBox.Items.Add(item);
}
else
{
// get product ame and supplier ID by product name and supplier name
addProdList.Add(prodNameComboBox.SelectedItem.ToString());
addSuppList.Add(supNameComboBox.SelectedItem.ToString());
// add to list box
prodSuppListBox.Items.Add(prodNameComboBox.SelectedItem.ToString()
+ " " + supNameComboBox.SelectedItem.ToString());
}
}
private void AddEditPackage_Load(object sender, EventArgs e)
{
TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext();
var listProducts = (from x in dbContext.Products select x).ToList();
//Populate the Product List ComboBox
prodNameComboBox.DataSource = listProducts;
prodNameComboBox.ValueMember = "ProductID";
prodNameComboBox.DisplayMember = "ProdName";
//Populate the Supplier List Combo Box
var listSuppliers = (from x in dbContext.Suppliers select x).ToList();
supNameComboBox.DataSource = listSuppliers;
supNameComboBox.ValueMember = "SupplierId";
supNameComboBox.DisplayMember = "SupName";
}
// Method used to load the products Combo Box
private void loadProductsCombo()
{
List <int> ids = new List <int>();
List <string> names = new List <string>();
// create supplier dictionary
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
ids = (from supp in dbContext.Suppliers select supp.SupplierId).ToList();
names = (from supp in dbContext.Suppliers select supp.SupName).ToList();
}
// create dictionary
for (int i = 0; i < ids.Count; i++)
{
supplierDict.Add(ids[i], names[i]);
supNameComboBox.Items.Add(names[i]);
}
}
//Method used to set up the product and supplier combbox at form loading
public void LoadMaintenanceCombos()
{
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
var listProducts = (from x in dbContext.Products select x).ToList();
//Populate the Product List ComboBox
prodComboBox.DataSource = listProducts;
prodComboBox.ValueMember = "ProductID";
prodComboBox.DisplayMember = "ProdName";
//Populate the Supplier List Combo Box
int selectedId = prodComboBox.SelectedIndex + 1;
var listSuppliers = (from x in dbContext.Suppliers select x).ToList();
supComboSelection(dbContext, selectedId);
}
}
//Method used to select the suppliers
private void supComboSelection(TravelExpertsLINQDataContext dbContext, int selectedId)
{
var productSuppliers = (from prod in dbContext.Products
join prodSup in dbContext.Products_Suppliers
on prod.ProductId equals prodSup.ProductId
join sup in dbContext.Suppliers
on prodSup.SupplierId equals sup.SupplierId
where prod.ProductId == selectedId
orderby sup.SupName
select new
{
supplierId = sup.SupplierId,
supName = sup.SupName
}).ToList();
supComboBox.DataSource = productSuppliers;
supComboBox.DisplayMember = "SupName";
supComboBox.ValueMember = "SupplierId";
}
//Method used to trigger the display of the product and suppliers maintenance form
private void prodSuppMaintButton_Click(object sender, EventArgs e)
{
ProdSuppMaintenance maintenanceForm = new ProdSuppMaintenance();
//maintenanceForm.mainForm = this;
//Shows the Add form
DialogResult result = maintenanceForm.ShowDialog();
if (result == DialogResult.OK) // Refresh
{
//Refresh the Grid View
//dbContext.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues,
// dbContext.Products);
dbContext = new TravelExpertsLINQDataContext();
packageDataGridView.DataSource = dbContext.Packages;
}
}
//Method to load the suppliers combos
private void loadSuppliersCombo()
{
List <int> ids = new List <int>();
List <string> names = new List <string>();
// create product dictionary
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
// get
ids = (from prod in dbContext.Products select prod.ProductId).ToList();
names = (from prod in dbContext.Products select prod.ProdName).ToList();
}
// create dictionary
for (int i = 0; i < ids.Count; i++)
{
productDict.Add(ids[i], names[i]);
prodNameComboBox.Items.Add(names[i]);
}
}
private void button1_Click(object sender, EventArgs e)
{
AddEditPackage addEditForm = new AddEditPackage();
addEditForm.mainForm = this;
//Shows the Add form
DialogResult result = addEditForm.ShowDialog();
if (result == DialogResult.OK) // Save
{
//Refresh the Grid View
//dbContext.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues,
// dbContext.Products);
dbContext = new TravelExpertsLINQDataContext();
packageDataGridView.DataSource = dbContext.Packages;
}
}
//Method used to trigger the deletion of a package
private void deletePackageButton_Click(object sender, EventArgs e)
{
bool flag = packageDataGridView.CurrentRow.Selected;
if (flag)
{
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
// index of the current row
int row = packageDataGridView.CurrentCell.RowIndex;
// Column for Package ID
string packageId = packageDataGridView["dataGridViewTextBoxColumn1", row].Value.ToString();
string packageName = packageDataGridView["dataGridViewTextBoxColumn2", row].Value.ToString();
// Delete from child table
DialogResult dr = MessageBox.Show("Are you sure you want to delete the " + packageName + "package",
"Delete Confirmation", MessageBoxButtons.YesNo);
switch (dr)
{
case DialogResult.Yes:
var pkgProdSupToDelete = dbContext.Packages_Products_Suppliers.Where(r => r.PackageId == int.Parse(packageId));
dbContext.Packages_Products_Suppliers.DeleteAllOnSubmit(pkgProdSupToDelete);
dbContext.SubmitChanges();
// delete from Parent table
Package packageToDelete = dbContext.Packages.SingleOrDefault(r => r.PackageId == int.Parse(packageId));
dbContext.Packages.DeleteOnSubmit(packageToDelete);
dbContext.SubmitChanges();
RefreshGridView();
break;
case DialogResult.No:
break;
}
}
}
else
{
MessageBox.Show("Please select a row to delete.");
}
}
//Method used to display the add edit supplier form in supplier add mode
private void suppAddButton_Click(object sender, EventArgs e)
{
AddEditSupplier aesForm = new AddEditSupplier();
aesForm.supAddMode = true;
aesForm.prodValue = (int)prodComboBox.SelectedValue;
//Shows the AddEditSupplier form
DialogResult result = aesForm.ShowDialog();
if (result == DialogResult.OK) // Save
{
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
supComboBox.DataSource = dbContext.Suppliers;
prodComboBox.DataSource = dbContext.Products;
prodComboBox.SelectedIndex = 0;
}
}
}
//Method used to display the add edit supplier form in product edit mode
private void prodEditButton_Click(object sender, EventArgs e)
{
AddEditSupplier aesForm = new AddEditSupplier();
//Sets the Add mode to true
aesForm.prodEditMode = true;
aesForm.prodToEdit = prodComboBox.Text;
//Shows the AddEditSupplier form
DialogResult result = aesForm.ShowDialog();
if (result == DialogResult.OK) // Save
{
//Refresh the Grid View
//dbContext.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues,
// dbContext.Products);
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
prodComboBox.DataSource = dbContext.Products;
}
}
}
//Method used to display the add edit supplier form in supplier edit mode
private void suppEditButton_Click(object sender, EventArgs e)
{
AddEditSupplier aesForm = new AddEditSupplier();
aesForm.supToEdit = supComboBox.Text;
aesForm.supEditMode = true;
//supToEdit = supComboBox.Text;
DialogResult result = aesForm.ShowDialog();
if (result == DialogResult.OK) // Save
{
//Refresh the Grid View
//dbContext.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues,
// dbContext.Products);
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
supComboBox.DataSource = dbContext.Suppliers;
prodComboBox.SelectedIndex = 0;
}
}
}
//Method to trigger the display of the addEditpackage form in add mode
private void addPackageButton_Click(object sender, EventArgs e)
{
AddEditPackageForm addEditForm = new AddEditPackageForm();
addEditForm.mainForm = this;
//Shows the Add form
DialogResult result = addEditForm.ShowDialog();
if (result == DialogResult.OK) // Save
{
//Refresh the Grid View
//dbContext.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues,
// dbContext.Products);
dbContext = new TravelExpertsLINQDataContext();
packageDataGridView.DataSource = dbContext.Packages;
//Sorts the Package Gridview by package ID
packageDataGridView.Sort(packageDataGridView.Columns[0], ListSortDirection.Ascending);
}
}
//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;
}
}
}
//Method used to trigger the display of the addEditpackage form in edit mode
private void modifyPackageButton_Click(object sender, EventArgs e)
{
// get the key of the current package in the data grid view
// index of the current row
int rowNum = packageDataGridView.CurrentCell.RowIndex;
// Column for Package ID
string packageId = packageDataGridView["dataGridViewTextBoxColumn1", rowNum].Value.ToString();
// Package details
Package currentPackage;
// ProductSupplierId list for current package
List <Packages_Products_Supplier> currentProdSuppIds = new List <Packages_Products_Supplier>();
// Product Id and Supplier Id list for current package
List <Products_Supplier> currentProdAndSupp = new List <Products_Supplier>();
// Product name (Key) Supplier name (Value)
List <string> prodList = new List <string>();
List <string> suppList = new List <string>();
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
// Package table for package details
currentPackage = (from p in dbContext.Packages
where p.PackageId == int.Parse(packageId)
select p).Single();
// Packages_Products_Suppliers table for list of ProdSuppId in current package
currentProdSuppIds = (from ps in dbContext.Packages_Products_Suppliers
where ps.PackageId == int.Parse(packageId)
select ps).ToList();
// Products_Suppliers table for list of ProductId and SuppId in current package
foreach (Packages_Products_Supplier ps in currentProdSuppIds)
{
currentProdAndSupp.Add((from psd in dbContext.Products_Suppliers
where psd.ProductSupplierId == ps.ProductSupplierId
select psd).Single());
}
// Products table and Supplier table for product name and supplier name
foreach (Products_Supplier pas in currentProdAndSupp)
{
prodList.Add((from p in dbContext.Products
where p.ProductId == pas.ProductId
select p.ProdName).Single());
suppList.Add((from s in dbContext.Suppliers
where s.SupplierId == pas.SupplierId
select s.SupName).Single());
}
}
AddEditPackageForm modForm = new AddEditPackageForm();
modForm.isAddMode = false; // Modify mode
modForm.currentPackage = currentPackage;
modForm.currentProdSuppIds = currentProdSuppIds;
modForm.currentProdAndSupp = currentProdAndSupp;
modForm.prodList = prodList;
modForm.suppList = suppList;
DialogResult result = modForm.ShowDialog(); // display second form modal
if (result == DialogResult.OK || result == DialogResult.Retry) // successful update or concurrency exception
{
RefreshGridView();
}
}
//method used to refresh the packages gridview
private void RefreshGridView()
{
TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext();
packageDataGridView.DataSource = dbContext.Packages;
}
//Method to add / Edit supplier or product based on the mode
private void addSuppLierButton_Click(object sender, EventArgs e)
{
if (supEditMode)
{
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
Supplier supEdit = (from sup in dbContext.Suppliers
where (sup.SupName == supToEdit)
select sup).SingleOrDefault();
string suppName = newSuppTextBox.Text;
if (suppName != "" && suppName != supEdit.SupName)
{
supEdit.SupName = newSuppTextBox.Text;
}
else
{
MessageBox.Show("Supplier name cannot be empty and needs to be updated for edit");
return;
}
try
{
dbContext.SubmitChanges();
MessageBox.Show("Edited supplier saved to the List of of Suppliers");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
}
else if (supAddMode)
{
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
Supplier supp = new Supplier();
string suppID = newSuppIDTextBox.Text;
string suppName = newSuppTextBox.Text;
//Attempt to validate
if (suppID != "" && suppName != "")
{
int suppIDValue = Convert.ToInt32(newSuppIDTextBox.Text);
supp.SupplierId = suppIDValue;
supp.SupName = suppName;
}
else
{
MessageBox.Show("You need to enter a Supplier Name and ID");
return;
}
dbContext.Suppliers.InsertOnSubmit(supp);
try
{
dbContext.SubmitChanges();
MessageBox.Show("Supplier Succesfuly Added to the List of of Suppliers");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
return;
}
//Altering the Product_Supplier Database
Products_Supplier prodSup = new Products_Supplier();
String prodSupID = newSuppIDTextBox.Text;
int prodSuppIDValue = Convert.ToInt32(newSuppIDTextBox.Text);
//Attempt to validate
if (prodSupID != "")
{
prodSup.SupplierId = supp.SupplierId;
prodSup.ProductId = prodValue;
}
else
{
MessageBox.Show("You need to enter a valid Product Id");
return;
}
dbContext.Products_Suppliers.InsertOnSubmit(prodSup);
try
{
dbContext.SubmitChanges();
MessageBox.Show("Succesfuly updated the Product Suppliers Table");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
}
else if (prodEditMode)
{
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
Product prodEdit = (from prod in dbContext.Products
where (prod.ProdName == prodToEdit)
select prod).SingleOrDefault();
string prodEditName = newSuppTextBox.Text;
if (prodEditName != "" && prodEditName != prodEdit.ProdName)
{
prodEdit.ProdName = prodEditName;
}
else
{
MessageBox.Show("Product name cannot be empty and needs to be updated for edit");
return;
}
try
{
dbContext.SubmitChanges();
MessageBox.Show("Product Succesfuly Edited");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
return;
}
}
}
else if (prodAddMode)
{
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
Product prod = new Product();
string prodName = newSuppTextBox.Text;
//Attempt to validate
if (prodName != "")
{
prod.ProdName = prodName;
}
else
{
MessageBox.Show("You need to enter a valid Product Name");
return;
}
dbContext.Products.InsertOnSubmit(prod);
try
{
dbContext.SubmitChanges();
MessageBox.Show("Product Succesfuly Added to the List of of Products");
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
}
}
//Method used to handle the change of product in the combobox
private void prodNameComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
// clear all the preloaded suppliers list
// it will be assigned new values based on which product the
// client selected
if (isAddMode) // Add mode
{
int selectedId;
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
if (prodNameComboBox.SelectedIndex > 0)
{
string selectedProd = prodNameComboBox.Text;
var ProdId = (from prod in dbContext.Products
where (prod.ProdName == selectedProd)
select new
{
productId = prod.ProductId
}).Take(1);
//Transforms the pkg
selectedId = ProdId.FirstOrDefault().productId;
}
else if (prodNameComboBox.SelectedIndex == 0)
{
selectedId = 1;
}
else
{
return;
}
var listSuppliers = (from x in dbContext.Suppliers select x).ToList();
var productSuppliers = (from prod in dbContext.Products
join prodSup in dbContext.Products_Suppliers
on prod.ProductId equals prodSup.ProductId
join sup in dbContext.Suppliers
on prodSup.SupplierId equals sup.SupplierId
where prod.ProductId == selectedId
select new
{
supplierId = sup.SupplierId,
supName = sup.SupName
}).ToList();
supNameComboBox.DataSource = productSuppliers;
supNameComboBox.DisplayMember = "SupName";
supNameComboBox.ValueMember = "SupplierId";
}
}
else
{
string selectedProduct = prodNameComboBox.SelectedItem.ToString();
int productId; // local variable to store the selected product's id
// retrieve the selected product's id
using (TravelExpertsLINQDataContext dbContext = new TravelExpertsLINQDataContext())
{
productId = dbContext.Products.Single(product => product.ProdName == selectedProduct).ProductId;
var productSuppliers = (from prod in dbContext.Products
join prodSup in dbContext.Products_Suppliers
on prod.ProductId equals prodSup.ProductId
join sup in dbContext.Suppliers
on prodSup.SupplierId equals sup.SupplierId
where prod.ProductId == productId
select new
{
supplierId = sup.SupplierId,
supName = sup.SupName
}).ToList();
supNameComboBox.Items.Clear();
for (int i = 0; i < productSuppliers.Count; i++)
{
supNameComboBox.Items.Add(productSuppliers[i].supName);
}
}
}
}