/// <summary>
/// Selects all package products suppliers
/// </summary>
/// <returns>A list of existing package products suppliers</returns>
public static List <Packages_Products_Supplier> GetAllPackages_Product_Suppliers()
{
List <Packages_Products_Supplier> packages_Products_Suppliers = new List <Packages_Products_Supplier>();
Packages_Products_Supplier pack_prod_supp = null;;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "SELECT PkgName, Packages_Products_Suppliers.PackageId, ProductSupplierId " +
"From Packages_Products_Suppliers, Packages " +
"where Packages_Products_Suppliers.PackageId = Packages.PackageId " +
"ORDER BY PkgName";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
pack_prod_supp = new Packages_Products_Supplier();
pack_prod_supp.PackageId = (int)dr["PackageId"];
pack_prod_supp.ProductSupplierId = (int)dr["ProductSupplierId"];
packages_Products_Suppliers.Add(pack_prod_supp);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packages_Products_Suppliers);
}
/// <summary>
/// Deletes a package product supplier
/// </summary>
/// <param name="pack_prod_supp"></param>
/// <returns>Indicator of success</returns>
public static bool DeletePackages_Products_Supplier(Packages_Products_Supplier pack_prod_supp)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string deleteStatement = "DELETE FROM Packages_Products_Suppliers " +
"WHERE PackageId = @PackageId " + // to identify the product to be deleted
"AND ProductSupplierId = @ProductSupplierId";
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@ProductSupplierId", pack_prod_supp.ProductSupplierId);
cmd.Parameters.AddWithValue("@PackageId", pack_prod_supp.PackageId);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Gets packages product suppliers with a specific package ID
/// </summary>
/// <param name="packageId"></param>
/// <returns></returns>
public static List <Packages_Products_Supplier> GetPackages_Product_Suppliers(int packageId)
{
List <Packages_Products_Supplier> packages_Products_Suppliers = new List <Packages_Products_Supplier>();
Packages_Products_Supplier pack_prod_supp = null;;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "SELECT Packages_Products_Suppliers.PackageId, Products_Suppliers.ProductSupplierId, ProdName, SupName " +
"From Packages_Products_Suppliers, Products_Suppliers, Products, Suppliers " +
"where Packages_Products_Suppliers.PackageId = @PackageId " +
"And Packages_Products_Suppliers.ProductSupplierId = Products_Suppliers.ProductSupplierId " +
"and Products_Suppliers.ProductId = Products.ProductId " +
"and Products_Suppliers.SupplierId = Suppliers.SupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@PackageId", packageId);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
pack_prod_supp = new Packages_Products_Supplier();
pack_prod_supp.PackageId = (int)dr["PackageId"];
pack_prod_supp.ProductSupplierId = (int)dr["ProductSupplierId"];
pack_prod_supp.ProdName = dr["ProdName"].ToString();
pack_prod_supp.SupName = dr["SupName"].ToString();
packages_Products_Suppliers.Add(pack_prod_supp);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packages_Products_Suppliers);
}
/// <summary>
/// Updates existing package_product_supplier
/// </summary>
/// <param name="oldPackProdSupp">data before update</param>
/// <param name="newPackProdSupp">new data for the update</param>
/// <returns>indicator of success</returns>
public static bool UpdateSupplier(Packages_Products_Supplier oldPackProdSupp, Packages_Products_Supplier newPackProdSupp)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string updateStatement = "UPDATE Packages_Products_Suppliers " +
"SET PackageId = @NewPackageId, " +
"ProductSupplierId = @NewProductSupplierId " +
"WHERE PackageId = @OldPackageId " +
"AND ProductSupplierId = @OldProductSupplierId";
SqlCommand cmd = new SqlCommand(updateStatement, connection);
cmd.Parameters.AddWithValue("@NewProductSupplierId", newPackProdSupp.ProductSupplierId);
cmd.Parameters.AddWithValue("@NewPackageId", newPackProdSupp.PackageId);
cmd.Parameters.AddWithValue("@OldProductSupplierId", oldPackProdSupp.ProductSupplierId);
cmd.Parameters.AddWithValue("@OldPackageId", oldPackProdSupp.PackageId);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
