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); }