private void btnModify_Click(object sender, EventArgs e)
{
// get the key of the current product in the data grid view
int rowNum = productDataGridView.CurrentCell.RowIndex; // index of the current row
string productId = productDataGridView["productIdDataGridViewTextBoxColumn", rowNum].Value.ToString(); // Column for ProductCode
Product currentProduct;
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
currentProduct = (from p in dbContext.Products // this is a query in the brackets
where p.ProductId == Convert.ToInt32(productId)
select p).SingleOrDefault(); // or Singleordefault, if no product for the code
}
frmAddModifyProducts secondForm = new frmAddModifyProducts();
secondForm.isAdd = false; // it Modify
secondForm.currentProduct = currentProduct;
DialogResult result = secondForm.ShowDialog(); // display second form modal
if (result == DialogResult.OK || result == DialogResult.Retry) // successful update or concurrency exception
{
RefreshGridView();
}
}
// ******************************** Product Supplier Tab ***************************************************
// code written by:
private void productSupplierIdComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
TravelExpertDataContext dataContext = new TravelExpertDataContext();
var selectedValue = (from m in dataContext.Products_Suppliers
where m.ProductSupplierId == Convert.ToInt32(productSupplierIdComboBox.SelectedValue)
select m).Single();
productIdTextBox.Text = selectedValue.ProductId.ToString();
supplierIdTextBox.Text = selectedValue.SupplierId.ToString();
var productName = (from m in dataContext.Products
where m.ProductId == Convert.ToInt32(selectedValue.ProductId)
select m).Single();
prodNameTextBox.Text = productName.ProdName;
var supplierName = (from m in dataContext.Suppliers
where m.SupplierId == Convert.ToInt32(selectedValue.SupplierId)
select m).Single();
supNameTextBox.Text = supplierName.SupName;
}
private void btnDeleteProductSup_Click(object sender, EventArgs e)
{
TravelExpertDataContext dbContext = new TravelExpertDataContext();
int sup_id = Convert.ToInt32(productSupplierIdComboBox.SelectedItem);
DialogResult answer = MessageBox.Show("Are you sure you want to delete Supplier Id: " + sup_id + "?", "Confirm", MessageBoxButtons.OKCancel);
if (answer == DialogResult.OK)
{
using (TravelExpertDataContext dataContext = new TravelExpertDataContext())
{
try
{
Products_Supplier selectedValue = (from m in dataContext.Products_Suppliers
where m.ProductSupplierId == sup_id
select m).Single();
dataContext.Products_Suppliers.DeleteOnSubmit(selectedValue);
dataContext.SubmitChanges();
productSupIds = (from m in dataContext.Products_Suppliers
select m.ProductSupplierId).ToList();
productSupplierIdComboBox.DataSource = productSupIds;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
}
/******* Attempt to separate the main window ability to add new product to database****/
private void btnNewProducts_Click(object sender, EventArgs e)
{
int pack_id = Convert.ToInt32(cboPackageId.SelectedValue);
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
Package currentPack = (from pack in dbContext.Packages
where pack.PackageId == pack_id
select pack).Single();
frmAddNewProductToPackage secondform = new frmAddNewProductToPackage();
secondform.currentPackage = currentPack;
DialogResult result = secondform.ShowDialog();
if (result == DialogResult.OK)// successful Add
{
packageIDs = (from pack in dbContext.Packages
select pack.PackageId).ToList();
cboPackageId.DataSource = packageIDs;
cboPackageId.SelectedIndex = 0; // refresh grid
}
}
}
/***************** WORKING ON ADDING PACKAGES INTO the PACKAGE ID *****/
// add products into the database and save it
private void btnSave_Click(object sender, EventArgs e)
{
if (Validator.IsProvided(txtProdSupID, "Prod Supllier ID"))
{
try
{
Packages_Products_Supplier newProduct = null;
using (TravelExpertDataContext dataContext = new TravelExpertDataContext())
{
newProduct = new Packages_Products_Supplier
{
// PackageId = Convert.ToInt32(cboPackageId.SelectedItem),
PackageId = currentPackage.PackageId,
ProductSupplierId = Convert.ToInt32(txtProdSupID.Text)
};// object initializer syntax
dataContext.Packages_Products_Suppliers.InsertOnSubmit(newProduct);
dataContext.SubmitChanges();
MessageBox.Show("Changes have been saved", "Data update");
}
DialogResult = DialogResult.OK;
}
catch (SqlException ex)
{
MessageBox.Show(
ex.Message,
ex.GetType().ToString());
}
}
else // validation failed
{
DialogResult = DialogResult.Cancel;
}
}
private void btnDeleteSupplier_Click(object sender, EventArgs e)
{
// get the key of the current product
int sup_id = Convert.ToInt32(cboSupplierId.SelectedValue);
DialogResult answer = MessageBox.Show("Are you sure you want to delete Supplier Id: " + sup_id + "?", "Confirm", MessageBoxButtons.OKCancel);
if (answer == DialogResult.OK)
{
try
{
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
Supplier currentSup = (from sup in dbContext.Suppliers
where sup.SupplierId == sup_id
select sup).Single();
dbContext.Suppliers.DeleteOnSubmit(currentSup);
dbContext.SubmitChanges();
loadComboBox_Supplier();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
private void loadComboBox_Package()
{
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
txtPackageId.Text = currentPackage.PackageId.ToString();
}
}
//load Supplier form fields when selected id is changed
private void cboSupplierId_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
int selectedID = Convert.ToInt32(cboSupplierId.SelectedValue);
{
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
var selected = (from sup in dbContext.Suppliers
where sup.SupplierId == selectedID
select sup).Single();
if (selected != null)
{
txtSupName.Text = selected.SupName;
}
else
{
loadComboBox_Supplier();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error while retrieving member with selected ID: " + ex.Message,
ex.GetType().ToString());
}
}
private void btnModProdSup_Click(object sender, EventArgs e)
{
frmAddModifyProdSuppltbl secondform = new frmAddModifyProdSuppltbl();
secondform.isModify = true;
int prodId = Convert.ToInt32(productIdTextBox.Text);
int supplId = Convert.ToInt32(supplierIdTextBox.Text);
using (TravelExpertDataContext dataContext = new TravelExpertDataContext())
{
secondform.activeProdSuppId = (from m in dataContext.Products_Suppliers
where m.ProductId == prodId && m.SupplierId == supplId
select m).Single();
}
DialogResult result = secondform.ShowDialog();
using (TravelExpertDataContext dataContext = new TravelExpertDataContext()) // refresh the grid
{
if (result == DialogResult.OK)
{
productSupIds = (from m in dataContext.Products_Suppliers
select m.ProductSupplierId).ToList();
productSupplierIdComboBox.DataSource = productSupIds;
}
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
// get the key of the current product in the data grid view
int rowNum = productDataGridView.CurrentCell.RowIndex; // index of the current row
string productId = productDataGridView["productIdDataGridViewTextBoxColumn", rowNum].Value.ToString(); // Column for ProductId
DialogResult answer = MessageBox.Show("Are you sure you want to delete product Id " + productId + "?", "Confirm", MessageBoxButtons.OKCancel);
if (answer == DialogResult.OK)
{
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
try
{
Product currentProduct = (from p in dbContext.Products
where p.ProductId == Convert.ToInt32(productId)
select p).SingleOrDefault();
dbContext.Products.DeleteOnSubmit(currentProduct);
dbContext.SubmitChanges();
RefreshGridView();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
}
}
// load combo box for Supplier Tab
private void loadComboBox_Supplier()
{
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
supplierIDs = (from sup in dbContext.Suppliers
select sup.SupplierId).ToList();
cboSupplierId.DataSource = supplierIDs;
cboSupplierId.SelectedIndex = 0;
}
}
// load combo box for Package Tab
private void loadComboBox_Package()
{
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
packageIDs = (from pack in dbContext.Packages
select pack.PackageId).ToList();
cboPackageId.DataSource = packageIDs;
cboPackageId.SelectedIndex = 0;
}
}
private void frmAddNewProductToPackage_Load(object sender, EventArgs e)
{
loadComboBox_Package();
// populate the list of available products in the list
using (TravelExpertDataContext dataContext = new TravelExpertDataContext())
{
productNameforList = (from prod in dataContext.Products
select prod.ProdName).ToList();
lbxAvailableProducts.DataSource = productNameforList;
}
}
//*************************************** Product Tab ********************************************
// code written by David Hahner
private void tabProducts_Click(object sender, EventArgs e)
{
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
productDataGridView.DataSource = from prod in dbContext.Products
orderby prod.ProdName
select prod;
}
{
RefreshGridView();
}
}
private void btnAddProdSup_Click(object sender, EventArgs e)
{
frmAddModifyProdSuppltbl secondform = new frmAddModifyProdSuppltbl();
DialogResult result = secondform.ShowDialog();
using (TravelExpertDataContext dataContext = new TravelExpertDataContext()) // refresh the grid
{
if (result == DialogResult.OK)
{
productSupIds = (from m in dataContext.Products_Suppliers
select m.ProductSupplierId).ToList();
productSupplierIdComboBox.DataSource = productSupIds;
}
}
}
private void lbxAvailableSupplier_SelectedIndexChanged_1(object sender, EventArgs e)
{
using (TravelExpertDataContext dataContext = new TravelExpertDataContext())
{
var y = (from m in dataContext.Products_Suppliers
join p in dataContext.Suppliers on m.SupplierId equals p.SupplierId
join x in dataContext.Products on m.ProductId equals x.ProductId
where p.SupName == lbxAvailableSupplier.SelectedItem.ToString() &&
x.ProdName == lbxAvailableProducts.SelectedItem.ToString()
select m).ToList();
foreach (var z in y)
{
txtProdSupID.Text = z.ProductSupplierId.ToString();
}
}
}
// validate the users entries
private void btnSaveMod_Click(object sender, EventArgs e)
{
if (Validator.IsProvided(txtPkgName, "Package Name") &&
Validator.IsProvided(txtPkgDesc, "Description") &&
Validator.IsNonNegativeDouble(txtPkgBasePrice, "Base Price") &&
Validator.IsNonNegativeDouble(txtPkgAgencyCommission, "Commission") &&
Validator.IsCorrectLength(txtPkgName, 50))
{
if (Convert.ToDecimal(txtPkgBasePrice.Text) <= Convert.ToDecimal(txtPkgAgencyCommission.Text))
{
MessageBox.Show("Base Price must be greater then commission", "Entry error");
txtPkgBasePrice.Focus();
}
else if (pkgEndDateDateTimePicker.Value <= pkgStartDateDateTimePicker.Value)
{
MessageBox.Show(" Package End Date Must be greater then Start Date", "Entry error");
pkgStartDateDateTimePicker.Focus();
}
else
{
try
{
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
// get the product with Code from the current text box
Package package = dbContext.Packages.Single(p => p.PackageId == Convert.ToInt32(txtPackageId.Text));
// make changes by copying values from text boxes
package.PkgName = Convert.ToString(txtPkgName.Text);
package.PkgStartDate = Convert.ToDateTime(pkgStartDateDateTimePicker.Value);
package.PkgEndDate = Convert.ToDateTime(pkgEndDateDateTimePicker.Value);
package.PkgDesc = Convert.ToString(txtPkgDesc.Text);
package.PkgBasePrice = Convert.ToDecimal(txtPkgBasePrice.Text);
package.PkgAgencyCommission = Convert.ToDecimal(txtPkgAgencyCommission.Text);
dbContext.SubmitChanges();
DialogResult = DialogResult.OK;
MessageBox.Show("Package changed successfully!", "Package Modified");
}
}
catch (ChangeConflictException)
{
MessageBox.Show("Another user changed or deleted the current record", "Concurrency Exception");
DialogResult = DialogResult.Retry;
}
}
//load package form fields when selected id is changed
private void cboPackageId_SelectedIndexChanged(object sender, EventArgs e)
{
lstCurrentProducts.Items.Clear();
int selectedID = Convert.ToInt32(cboPackageId.SelectedValue);
TravelExpertDataContext dbContext = new TravelExpertDataContext();
// retrieve data from Packages table
var selectedPackage = (from pack in dbContext.Packages
where pack.PackageId == selectedID
select pack).Single();
if (selectedPackage != null)
{
txtPkgName.Text = selectedPackage.PkgName;
pkgStartDateDateTimePicker.Value = Convert.ToDateTime(selectedPackage.PkgStartDate);
pkgEndDateDateTimePicker.Value = Convert.ToDateTime(selectedPackage.PkgEndDate);
txtPkgDesc.Text = selectedPackage.PkgDesc;
txtPkgBasePrice.Text = Convert.ToDouble(selectedPackage.PkgBasePrice).ToString("c");
txtPkgAgencyCommission.Text = Convert.ToDouble(selectedPackage.PkgAgencyCommission).ToString("c");
//retrieve data from Packages_Products_Supplier
var selectedPackProdSup = (from p in dbContext.Packages_Products_Suppliers
where p.PackageId == selectedID
select p).ToList();
// cboPPS.DataSource = selectedPackProdSup;
foreach (var x in selectedPackProdSup)
{
var pId = (from p in dbContext.Products_Suppliers
where p.ProductSupplierId == x.ProductSupplierId
select p).Single();
var selectedProd = (from p in dbContext.Products
where p.ProductId == pId.ProductId
select p.ProdName).Single();
lstCurrentProducts.Items.Add(selectedProd);
}
}
else
{
loadComboBox_Package();
}
}
// when user clicks modify button on Travel Package tab
private void btnModPackage_Click(object sender, EventArgs e)
{
int pkgId = Convert.ToInt32(cboPackageId.SelectedValue);
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
Package currentPackage = (from p in dbContext.Packages
where p.PackageId == pkgId
select p).Single();
frmModifyPackage secondFormModifyPackage = new frmModifyPackage();
secondFormModifyPackage.isAdd = false;
secondFormModifyPackage.currentPackage = currentPackage;
DialogResult result = secondFormModifyPackage.ShowDialog(); // display second form modal
if (result == DialogResult.OK) // new row got inserted
{
loadComboBox_Package();
}
}
}
private void lbxAvailableProducts_SelectedIndexChanged_1(object sender, EventArgs e)
{
lbxAvailableSupplier.Items.Clear();
TravelExpertDataContext dataContext = new TravelExpertDataContext();
var i = lbxAvailableProducts.Items[lbxAvailableProducts.SelectedIndex].ToString();
var selectedValue = (from m in dataContext.Products_Suppliers
join p in dataContext.Products on m.ProductId equals p.ProductId
join s in dataContext.Suppliers on m.SupplierId equals s.SupplierId
where p.ProdName == i
select s).ToList();
foreach (var x in selectedValue)
{
//lbxAvailableSupplier.Items.Add(x.SupplierId);
lbxAvailableSupplier.Items.Add(x.SupName);
}
}
private void btnAddPackage_Click(object sender, EventArgs e)
{
if (Validator.IsProvided(txtName, "Package Name") &&
Validator.IsProvided(txtDescription, "Description") &&
Validator.IsNonNegativeDouble(txtPrice, "Base Price") &&
Validator.IsNonNegativeDouble(txtCommision, "Commission") &&
Validator.IsCorrectLength(txtName, 50))
{
if (Convert.ToDecimal(txtPrice.Text) <= Convert.ToDecimal(txtCommision.Text))
{
MessageBox.Show("Base Price must be greater then commision", "Entry error");
txtPrice.Focus();
}
else if (pkgEndDateDateTimePicker.Value <= pkgStartDateDateTimePicker.Value)
{
MessageBox.Show(" Package End Date Must be greater then Start Date", "Entry error");
pkgStartDateDateTimePicker.Focus();
}
else
if (isAdd) // it is add
{
Package newPackage = new Package
{
PkgName = txtName.Text.ToString(),
PkgStartDate = Convert.ToDateTime(pkgStartDateDateTimePicker.Value),
PkgEndDate = Convert.ToDateTime(pkgEndDateDateTimePicker.Value),
PkgDesc = txtDescription.Text.ToString(),
PkgBasePrice = Convert.ToDecimal(txtPrice.Text),
PkgAgencyCommission = Convert.ToDecimal(txtCommision.Text)
};
//save to database
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
dbContext.Packages.InsertOnSubmit(newPackage);
dbContext.SubmitChanges(); //submit to database
}
DialogResult = DialogResult.OK;
}
}
}
//When user clicks edit button in Supplier Tab
private void btnEditSupplier_Click(object sender, EventArgs e)
{
//get the current supplier id to pass on to 2nd form
this.tabControl1.SelectedTab = tabSupplier;
int sup_id = Convert.ToInt32(cboSupplierId.SelectedValue);
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
Supplier currentSup = (from sup in dbContext.Suppliers
where sup.SupplierId == sup_id
select sup).Single();
frmAddEditSupplier secondformSupplier = new frmAddEditSupplier();
secondformSupplier.isAdd = false;
secondformSupplier.currentSupplier = currentSup;
DialogResult result = secondformSupplier.ShowDialog(); // display second form modal
if (result == DialogResult.OK || result == DialogResult.Retry) // successful update or concurrency exception
{
loadComboBox_Supplier();
}
}
}
// populate combo box as the form loads
private void Form1_Load(object sender, EventArgs e)
{
loadComboBox_Package();
loadComboBox_Supplier();
using (TravelExpertDataContext dataContext = new TravelExpertDataContext())
{
productSupIds = (from m in dataContext.Products_Suppliers
select m.ProductSupplierId).ToList();
productSupplierIdComboBox.DataSource = productSupIds;
productSupplierIdComboBox.SelectedIndex = 0;
}
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
productDataGridView.DataSource = from prod in dbContext.Products
orderby prod.ProdName
select prod;
}
{
RefreshGridView();
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (isAdd) // it is add
{
if (Validator.IsProvided(txtSupplierId, "Supplier Id") &&
Validator.IsInt32(txtSupplierId) &&
Validator.IsProvided(txtSupName, "Supplier Name") && Validator.isNotNumeric(txtSupName, "Supplier Name") &&
Validator.IsCorrectLength(txtSupName, 50))
{
// create a supplier by taking data from form inputs
Supplier newSupplier = new Supplier
{
SupplierId = Convert.ToInt32(txtSupplierId.Text),
SupName = txtSupName.Text
};
//save to database
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
dbContext.Suppliers.InsertOnSubmit(newSupplier);
dbContext.SubmitChanges(); //submit to database
MessageBox.Show("Changes have been saved", "Data update");
}
DialogResult = DialogResult.OK;
}
else // validation failed
{
DialogResult = DialogResult.Cancel;
}
}
else // it is modify
{
if (Validator.IsProvided(txtSupName, "Supplier Name") && Validator.isNotNumeric(txtSupName, "Supplier Name") &&
Validator.IsCorrectLength(txtSupName, 50))
{
try
{
using (TravelExpertDataContext dbContext = new TravelExpertDataContext())
{
Supplier supplierFromDb = (from sup in dbContext.Suppliers
where sup.SupplierId == currentSupplier.SupplierId
select sup).Single();
if (supplierFromDb != null)
{
supplierFromDb.SupName = txtSupName.Text;
//submit the changes to db
dbContext.SubmitChanges();
DialogResult = DialogResult.OK;
MessageBox.Show("Changes have been modified", "Data update");
}
}
}
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());
}
}
else // validation failed
{
DialogResult = DialogResult.Cancel;
}
}
}
private void RefreshGridView()
{
TravelExpertDataContext dbContext = new TravelExpertDataContext();
productDataGridView.DataSource = dbContext.Products;
}