/// <summary> /// Katrina: Returns a list of new product suppliers that are not yet within the selected package /// </summary> /// <param name="pkgId"></param> /// <returns>list of new prod sups</returns> public static List <PackageProductSupplier> GetNewProductsSuppliers(int pkgId) { List <PackageProductSupplier> prodsSups = new List <PackageProductSupplier>(); // empty PackageProductSupplier prodSup; // connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT DISTINCT ps.ProductSupplierId, ps.ProductId, ps.SupplierId, ProdName, SupName " + "FROM Packages_Products_Suppliers AS pps " + "RIGHT JOIN Products_Suppliers AS ps " + "ON pps.ProductSupplierId = ps.ProductSupplierId " + "JOIN Products ON ps.ProductId = Products.ProductId " + "JOIN Suppliers ON ps.SupplierId = Suppliers.SupplierId " + "WHERE ps.ProductSupplierId NOT IN " + "(SELECT ps.ProductSupplierId " + "FROM Products_Suppliers AS ps " + "JOIN Packages_Products_Suppliers AS pps " + "ON ps.ProductSupplierId = pps.ProductSupplierId " + "AND PackageId = @PackageId) " + "ORDER BY ps.ProductSupplierId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.AddWithValue("@PackageId", pkgId); // run command and process data connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) // while there is data { prodSup = new PackageProductSupplier(); prodSup.ProductSupplierId = (int)dr["ProductSupplierId"]; prodSup.ProductId = (int)dr["ProductId"]; prodSup.ProdName = (string)dr["ProdName"]; prodSup.SupplierId = (int)dr["SupplierId"]; prodSup.SupName = (string)dr["SupName"]; prodsSups.Add(prodSup); } } } } return(prodsSups); }
/// <summary> /// Katrina Spencer: Retrieves list of products-suppliers with given packageid /// </summary> /// <param name="pkgId">id of package to get</param> /// <returns>products-suppliers of a package</returns> public static List <PackageProductSupplier> GetProductsSuppliersByPackageId(int pkgId) { List <PackageProductSupplier> ppsList = new List <PackageProductSupplier>(); // empty PackageProductSupplier pps; // connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT pps.ProductSupplierID, " + "ProdName + ', ' + SupName AS ProductSupplierName " + "FROM Packages_Products_Suppliers AS pps " + "JOIN Products_Suppliers AS ps " + "ON pps.ProductSupplierId = ps.ProductSupplierId " + "JOIN Products AS p " + "ON ps.ProductId = p.ProductId " + "JOIN Suppliers AS s " + "ON ps.SupplierId = s.SupplierId " + "WHERE PackageId = @PackageId " + "ORDER BY ProductSupplierId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.AddWithValue("@PackageId", pkgId); // run command and process data connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) // while there is data { pps = new PackageProductSupplier(); pps.ProductSupplierId = (int)dr["ProductSupplierId"]; pps.ProductSupplierName = (string)dr["ProductSupplierName"]; ppsList.Add(pps); } } } } return(ppsList); }