/// <summary> /// Generates a package's product information. /// </summary> /// <param name="packageId">Package's package ID</param> /// <returns>List of product names and supplier names.</returns> public static List <PS> GetPPSWithPackageId(int packageId) { PS ps; List <PS> psList = new List <PS>(); using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT Products.ProdName, Suppliers.SupName " + "FROM Packages_Products_Suppliers " + "JOIN Products ON Packages_Products_Suppliers.ProductId = Products.ProductId " + "JOIN Suppliers ON Packages_Products_Suppliers.SupplierId = Suppliers.SupplierId " + "WHERE PackageId = " + packageId.ToString(); using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { ps = new PS(); ps.ProdName = (string)dr["ProdName"]; ps.SupName = (string)dr["SupName"]; psList.Add(ps); } } } } return(psList); }
/// <summary> /// Generates all product-supplier combinations. /// </summary> /// <param name="packageId">Package's package ID</param> /// <returns>List of product names and supplier names.</returns> public static List <PS> GetPS(string prodNameCondition, string supNameCondition) { PS ps; List <PS> psList = new List <PS>(); using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT * " + "FROM Products_Suppliers " + "JOIN Products ON Products_Suppliers.ProductId = Products.ProductId " + "JOIN Suppliers ON Products_Suppliers.SupplierId = Suppliers.SupplierId " + "WHERE Products.ProductId > 0" + prodNameCondition + supNameCondition + "ORDER BY Products.ProdName, Suppliers.SupName"; using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { ps = new PS(); ps.ProdName = (string)dr["ProdName"]; ps.SupName = (string)dr["SupName"]; psList.Add(ps); } } } } return(psList); }