private void btnAddToList_Click(object sender, EventArgs e)
{
try
{
foreach (DataGridViewRow row in dgvSuppliers.SelectedRows)//get each row from dgvSuppliers
{
selectedSupplier = new Supplier(Convert.ToInt32(row.Cells[0].Value.ToString()),
row.Cells[1].Value.ToString()); //get value from row and assign it to selected supplier
productSupplier = new ProductSupplier(selectedProduct.ProductId, selectedSupplier.SupplierId); //get value from row and assign it to selected product supplier
ProductSupplierDB.InsertProductSupplier(productSupplier); //call insertProductSupplier
editedProductSuppliers = ProductDB.GetProductSuppliersByProduct(selectedProduct); //call GetProductSupplierByProduct
//suppliers.Remove(selectedSupplier);
suppliers = SupplierDB.GetSuppliersNotInList(selectedProduct); //call GetSupplierNotInList function
}
UpdateBinding();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());//show exception if there any
}
// only select the last row
dgvProductSuppliers.ClearSelection();//clear dgvProductSuppliers
int index = editedProductSuppliers.Count - 1;
dgvProductSuppliers.Rows[index].Selected = true;
txtSName.Clear();
}
//delete the selected product_supplier from the detail list(Packages_Products_Suppliers table)
private void btnRemove_Click(object sender, EventArgs e)
{
try
{
foreach (DataGridViewRow row in dgvPackageDetail.SelectedRows)
{
Product p = ProductDB.GetProductByName(row.Cells[3].Value.ToString());
Supplier s = SupplierDB.GetSupplierByName(row.Cells[4].Value.ToString());
int pkgId;
bool pkgOK = Int32.TryParse(cobPackages.SelectedValue.ToString(), out pkgId);
Package pkg = PackageDB.GetPackageById(pkgId);
string message = "Are you sure you want to remove [" + p.ProdName + " : " + s.SupName + "] from package [" + pkg.PkgName + "]?";
DialogResult button = MessageBox.Show(message, "Confirm Delete",
MessageBoxButtons.YesNo);
if (button == DialogResult.Yes)
{
ProductSupplier productSupplier = ProductSupplierDB.GetProductSupplierById(p.ProductId, s.SupplierId);
ProductSupplierDB.DeletePackageDetail(productSupplier, pkg);
details = PackageDB.GetProductSuppliersByPackage(pkg);
}
}
UpdateBinding();
dgvPackageDetail.ClearSelection();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, ex.GetType().ToString());
}
}
public static void DeleteProductSupplier(ProductSupplier dps)
{
//DeletePackageDetailByProductSupplierId(dps);
SqlConnection dbConn = TravelExpertsDB.GetConnection();//getting connection
string sql =
"DELETE FROM Products_Suppliers " +
"WHERE ProductId = @pid and SupplierId = @sid";
SqlCommand cmdDelete = new SqlCommand(sql, dbConn);
cmdDelete.Parameters.AddWithValue("@pid", dps.ProductId.ToString()); //assign value with parameter
cmdDelete.Parameters.AddWithValue("@sid", dps.SupplierId.ToString()); //assign value with parameter
try
{
dbConn.Open(); //open connection
cmdDelete.ExecuteNonQuery(); //execute query
}
catch (SqlException ex)
{
throw ex;//throw exception
}
finally
{
dbConn.Close();//close connection
}
}
public static ProductSupplier GetProductSupplierById(int pId, int sId)//for getting product suppliers by id
{
ProductSupplier ps = new ProductSupplier();
SqlConnection dbConn = TravelExpertsDB.GetConnection();//for getting connection
string sql =
"SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers " +
"WHERE ProductId = @pId and SupplierId =@sId";
SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
cmdSelect.Parameters.AddWithValue("@pId", pId); //assignd value with parameter
cmdSelect.Parameters.AddWithValue("@sId", sId); //assign with parameter
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader(); //read query
while (dbReader.Read()) //while reading product suppliers
{
ps.ProductSupplierId = Convert.ToInt32(dbReader["ProductSupplierId"]); //convert product supplier id to int
ps.ProductId = Convert.ToInt32(dbReader["ProductId"]); //convert productid to int
ps.SupplierId = Convert.ToInt32(dbReader["SupplierId"]); //convert supplierid to int
}
}
catch (SqlException ex)//throw exception
{
throw ex;
}
finally
{
dbConn.Close();//closing connection
}
return(ps);
}
public static void DeletePackageDetail(ProductSupplier dps, Package pkg)
{
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"DELETE FROM Packages_Products_Suppliers " +
"WHERE ProductSupplierId = @psid and PackageId = @sid";
SqlCommand cmdDelete = new SqlCommand(sql, dbConn);
cmdDelete.Parameters.AddWithValue("@psid", dps.ProductSupplierId); //assign value with parameter
cmdDelete.Parameters.AddWithValue("@sid", pkg.PackageId); //assign value with parameter
try
{
dbConn.Open(); //open connection
cmdDelete.ExecuteNonQuery(); //execute query
}
catch (SqlException ex)
{
throw ex;//throw exception
}
finally
{
dbConn.Close();//close connection
}
}
private void btnRemove_Click(object sender, EventArgs e)
{
try
{
foreach (DataGridViewRow row in dgvProductSuppliers.SelectedRows)//get each row from dgvProductSuppliers
{
selectedSupplier = new Supplier(Convert.ToInt32(row.Cells[0].Value.ToString()),
row.Cells[1].Value.ToString()); //get value from row and assign it to selected supplier
productSupplier = new ProductSupplier(selectedProduct.ProductId, selectedSupplier.SupplierId); //get value from row and assign it to selected product
ProductSupplierDB.DeleteProductSupplier(productSupplier); //call DeleteProductSupplier
editedProductSuppliers = ProductDB.GetProductSuppliersByProduct(selectedProduct); //call GetProductSuppliersByProduct function
suppliers = SupplierDB.GetSuppliersNotInList(selectedProduct); //call GetSuppliersNotInList function
}
UpdateBinding();
dgvProductSuppliers.ClearSelection();
}
catch (Exception ex)
{
MessageBox.Show("Deleting this supplier will cause you to lose a record in your booking history. \nThe delete action has been suspended. \nPlease contact DBA.");//show exception if there any
}
}
public static void InsertProductSupplier(ProductSupplier nps)
{
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql = "INSERT Products_Suppliers (ProductId,SupplierId) " +
"VALUES (@npsPid, @npsSid)";
SqlCommand cmdInsert = new SqlCommand(sql, dbConn); //get connection
cmdInsert.Parameters.AddWithValue("@npsPid", nps.ProductId); //assign value with parameter
cmdInsert.Parameters.AddWithValue("@npsSid", nps.SupplierId); //assign value with parameter
try
{
dbConn.Open(); //connection open
cmdInsert.ExecuteNonQuery(); //execute query
}
catch (SqlException ex)
{
throw ex;//throw exception
}
finally
{
dbConn.Close();//close connection
}
}