/// <summary>
/// Inserts a new packaage product supplier
/// from an existing package and product supplier
/// </summary>
/// <param name="pack"></param>
/// <param name="prod_supp"></param>
/// <returns>Indicator of success</returns>
public static bool AddPackages_Products_Supplier(Package pack, Products_Supplier prod_supp)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string insertStatement = "INSERT INTO Packages_Products_Suppliers(PackageId, ProductSupplierId) " +
"VALUES(@PackageId, @ProductSupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@PackageId", pack.PackageId);
cmd.Parameters.AddWithValue("@ProductSupplierId", prod_supp.ProductSupplierId);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Updates existing supplier
/// </summary>
/// <param name="oldSup">data before update</param>
/// <param name="newSup">new data for the update</param>
/// <returns>indicator of success</returns>
public static bool UpdateSupplier(Products_Supplier oldProdSupp, Products_Supplier newProdSupp)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string updateStatement = "UPDATE Products_Suppliers " +
"SET SupplierId = @NewSupplierId, " +
"ProductId = @NewProductId " +
"WHERE ProductSupplierId = @OldProductSupplierId" +
"AND SupplierId = @OldSupplierId " +
"AND ProductId = @OldProductId"; // remaining conditions - to ensure optimistic concurrency
SqlCommand cmd = new SqlCommand(updateStatement, connection);
cmd.Parameters.AddWithValue("@NewSupplierId", newProdSupp.SupplierId);
cmd.Parameters.AddWithValue("@NewProductId", newProdSupp.ProductId);
cmd.Parameters.AddWithValue("@OldProductSupplierId", oldProdSupp.ProductSupplierId);
cmd.Parameters.AddWithValue("@OldProductId", oldProdSupp.ProductId);
cmd.Parameters.AddWithValue("@OldSupplierId", oldProdSupp.SupplierId);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Get unique products suppliers with passed product supplier ID
/// </summary>
/// <param name="prodId"></param>
/// <returns></returns>
public static List <Products_Supplier> GetUniqueProduct_Suppliers(int prodId)
{
List <Products_Supplier> products_Suppliers = new List <Products_Supplier>();
Products_Supplier prod_supp = null;;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "select SupName, Products_Suppliers.SupplierId, ProductSupplierId " +
"from Products_Suppliers, Suppliers " +
"where ProductId = @ProdId " +
"and Suppliers.SupplierId = Products_Suppliers.SupplierId " +
"order by SupName";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@ProdId", prodId);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
prod_supp = new Products_Supplier();
prod_supp.ProductSupplierId = (int)dr["ProductSupplierId"];
prod_supp.SupplierId = (int)dr["SupplierId"];
prod_supp.SupName = dr["SupName"].ToString();
products_Suppliers.Add(prod_supp);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(products_Suppliers);
}
/// <summary>
/// Delete Product Supplier
/// </summary>
/// <param name="prod_supp"></param>
/// <returns></returns>
public static bool DeleteProdSupplier(Products_Supplier prod_supp)
{
SqlConnection connection = TravelExpertConnect.GetConnection();
string deleteStatement = "DELETE FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId " + // to identify the product to be deleted
"AND SupplierId = @SupplierId " +
"AND ProductId = @ProductId "; // remaining conditions - to ensure optimistic concurrency
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@ProductSupplierId", prod_supp.ProductSupplierId);
cmd.Parameters.AddWithValue("@SupplierId", prod_supp.SupplierId);
cmd.Parameters.AddWithValue("@ProductId", prod_supp.ProductId);
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 all products suppliers and returns a list
/// </summary>
/// <returns></returns>
public static List <Products_Supplier> GetAllProduct_Suppliers()
{
List <Products_Supplier> products_Suppliers = new List <Products_Supplier>();
Products_Supplier prod_supp = null;;
SqlConnection connection = TravelExpertConnect.GetConnection();
string selectStatement = "SELECT ProductSupplierId, Products_Suppliers.SupplierId, Products_Suppliers.ProductId, SupName, ProdName " +
"From Products_Suppliers, Suppliers, Products " +
"Where Products_Suppliers.ProductId = Products.ProductId " +
"AND Products_Suppliers.SupplierId = Suppliers.SupplierId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
prod_supp = new Products_Supplier();
prod_supp.ProductSupplierId = (int)dr["ProductSupplierId"];
prod_supp.SupplierId = (int)dr["SupplierId"];
prod_supp.ProductId = (int)dr["ProductId"];
prod_supp.ProdName = dr["ProdName"].ToString();
prod_supp.SupName = dr["SupName"].ToString();
products_Suppliers.Add(prod_supp);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(products_Suppliers);
}
