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