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