public static bool Delete(Packages_Products_Suppliers packProdSup, Package package)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "DELETE FROM Packages_Products_Suppliers " +
"WHERE PackageID = @PackageID " +
"AND ProductSupplierID = @ProductSupplierID";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@PackageID", package.PackageId);
cmd.Parameters.AddWithValue("@ProductSupplierID", packProdSup.ProductSupplerID);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static bool UpdateProduct_Supplier_Packages(Packages_Products_Suppliers oldProduct_Supplier, Product_Supplier newProduct_Supplier)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string updateStatement = "UPDATE Products_Suppliers " +
"SET ProductID = @newProductID, " +
"SupplierID = @newSupplierID " +
"WHERE ProductSupplierID = @oldProductSupplierID " +
"AND ProductID = @oldProductID " +
"AND SupplierID = @oldSupplierID ";
SqlCommand cmd = new SqlCommand(updateStatement, con);
//new value to be entered
cmd.Parameters.AddWithValue("@newProductID", newProduct_Supplier.ProductID);
cmd.Parameters.AddWithValue("@newSupplierID", newProduct_Supplier.SupplierID);
//start adding old product supplier values
cmd.Parameters.AddWithValue("@oldProductSupplierID", oldProduct_Supplier.ProductSupplerID);
cmd.Parameters.AddWithValue("@oldProductID", oldProduct_Supplier.ProductID);
cmd.Parameters.AddWithValue("@oldSupplierID", oldProduct_Supplier.SupplierID);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// gets product name and supplier name for certain package id
/// joins products suppliuer and pakcages product suppliers to match the product and supplier id to a package id
/// joins the package supplier to the supplier and product table to match a package id and supplier id with a package name and supplier name
/// </summary>
/// <param name="package"></param>
/// <returns> list of produt name and supplier name</returns>
public static List <Packages_Products_Suppliers> GetAllPackagesProductsSuppliers(Package package)
{
List <Packages_Products_Suppliers> packProdSups = new List <Packages_Products_Suppliers>();
Packages_Products_Suppliers packProdSup = null;
SqlConnection con = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT p.ProdName, s.SupName, pps.ProductSupplierID, ps.ProductID, ps.SupplierID " +
"FROM Packages_Products_Suppliers pps " +
"INNER JOIN Products_Suppliers ps ON pps.ProductSupplierID = ps.ProductSupplierID " +
"INNER JOIN Products p ON ps.ProductID = p.ProductID " +
"INNER JOIN Suppliers s ON ps.SupplierID = s.SupplierID " +
"WHERE PackageID = @PackageID";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@PackageID", package.PackageId);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //while there is still data to be read
{
packProdSup = new Packages_Products_Suppliers();
packProdSup.ProductSupplerID = (int)reader["ProductSupplierID"];
packProdSup.ProductID = (int)reader["ProductID"];
packProdSup.SupplierID = (int)reader["SupplierID"];
packProdSup.ProdName = reader["ProdName"].ToString();
packProdSup.SupName = reader["SupName"].ToString();
packProdSups.Add(packProdSup); // add package products supplier object to the list
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(packProdSups); //list to be returned
}