private void btnDeleteProdSupplier_Click(object sender, EventArgs e)
{
try
{
if (MessageBox.Show("Delete Product?", "Delete Confirmation", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
var productSupplierID = gridProductSuppliers.CurrentRow.Cells[1].Value;
Products_Suppliers itemDelete = new Products_Suppliers();
Packages_Products_Suppliers pkgPrdDelete = new Packages_Products_Suppliers();
pkgPrdDelete.ProductSupplierId = Convert.ToInt32(productSupplierID);
itemDelete.ProductSupplierId = Convert.ToInt32(productSupplierID);
Packages_Products_SuppliersDB.DeletePackageProSupplierByID(pkgPrdDelete);
Products_SuppliersDB.DeleteProductsSuppliers(itemDelete);
gridProductSuppliers.DataSource = Products_SuppliersDB.GetProductsSuppliers();
ResetProductList();
ResetSupplierList();
ResetPrdSupplierPage();
ResetProductSupllierList();
}
}
catch
{
MessageBox.Show("Cannot Delete at this moment as this supplied id is linked with booking details table.Plaese select another one to delete");
}
}
// retrieve a single package product and supplier data
public static Products_Suppliers GetProdSup(int ProductSupplierId)
{
//creating the object to store the orders information
Products_Suppliers prodSup = null;
//opening a connection to SQL and inputting a query to access the specific orderIDs information
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string query = "SELECT * " +
"FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId";
using (SqlCommand cmd = new SqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@PackageID", ProductSupplierId);
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
//store values into the order object
if (reader.Read())
{
prodSup = new Products_Suppliers();
prodSup.ProductSupplierId = (int)reader["ProductSupplierId"];
prodSup.ProductId = (int)reader["ProductId"];
prodSup.SupplierId = (int)reader["SupplierId"];
}
}
}
}
return(prodSup);
}// Get Packages and suppliers method completed
}// Get Packages and suppliers method completed
public static int AddProdSupplier(Products_Suppliers pkg)
{
int ProductSupplierId = -1;
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string insertStatement =
"INSERT INTO Products_Suppliers(ProductId, SupplierId) " +
"OUTPUT inserted.ProductSupplierId " +
"VALUES(@ProductId, @SupplierId)";
using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
{
cmd.Parameters.AddWithValue("@ProductId", pkg.ProductId);
cmd.Parameters.AddWithValue("@SupplierId", pkg.SupplierId);
connection.Open();
//cmd.ExecuteNonQuery(); // INSERT statement
ProductSupplierId = (int)cmd.ExecuteScalar(); // fixes problem of retrieving ID
//Thread.Sleep(10000);
// retrieve generated customerID
//string secondQuery = "SELECT IDENT_CURRENT('Customers')"; // most recent value generated for identity column
//SqlCommand secondCmd = new SqlCommand(secondQuery, connection);
//customerID = Convert.ToInt32(secondCmd.ExecuteScalar());
}
}
return(ProductSupplierId);
}
private void saveProdSup_Click(object sender, EventArgs e)
{
ResetPrdSupplierPage();
try
{
Products_Suppliers addPrdSupp = new Products_Suppliers();
List <int> prod = ProductsDB.GetProductID();
List <int> supp = SuppliersDB.GetSupplierIDs();
foreach (var item in prod)
{
if (comboProduct.SelectedItem.ToString() == ProductsDB.GetProduct(item).ProdName)
{
addPrdSupp.ProductId = item;
}
}
foreach (var item in supp)
{
if (comboSupplier.SelectedItem.ToString() == SuppliersDB.GetSupplier(item).SupName)
{
addPrdSupp.SupplierId = item;
}
}
Products_SuppliersDB.AddProdSupplier(addPrdSupp);
gridProductSuppliers.DataSource = null;
gridProductSuppliers.DataSource = Products_SuppliersDB.GetProductsSuppliers();
gridProductSuppliers.Columns[0].Visible = false;
gridProductSuppliers.Columns[1].HeaderText = "Product Supplier ID";
gridProductSuppliers.Columns[2].HeaderText = "Product Name";
gridProductSuppliers.Columns[3].HeaderText = "Supplier Name";
gridProductSuppliers.Columns[1].Width = 150;
gridProductSuppliers.Columns[2].Width = 300;
gridProductSuppliers.Columns[3].Width = 300;
ResetProductList();
ResetSupplierList();
ResetPrdSupplierPage();
ResetProductSupllierList();
}
catch
{
lblPckProdError.Text = "Same Product Supplier Already Exist";
}
}
public static bool DeleteProductsSuppliers(Products_Suppliers prodsup)
{
int count = 0; // how many rows deleted
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
string deleteStatement =
"DELETE FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId ";
using (SqlCommand cmd = new SqlCommand(deleteStatement, connection))
{
cmd.Parameters.AddWithValue("@ProductSupplierId", prodsup.ProductSupplierId);
connection.Open();
count = cmd.ExecuteNonQuery(); // DELETE statement return # affected rows
}
}
return(count > 0);
}
