/// <summary>
/// function is used to add data to Product_Supplier table
/// </summary>
/// <param name="PS"></param>
/// <returns> product supplier id</returns>
public static int AddProduct_Supplier(Product_Supplier productSupplier)
{
int productSupplierID;//holds value of product supplier id
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Products_Suppliers (ProductID, SupplierID) " +
"VALUES (@ProductID, @SupplierID)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
//values to be added
cmd.Parameters.AddWithValue("@ProductID", productSupplier.ProductID);
cmd.Parameters.AddWithValue("@SupplierID", productSupplier.SupplierID);
try
{
con.Open();
cmd.ExecuteNonQuery();
//TODO: Might not need this code below
string selectQuery = "SELECT IDENT_CURRENT ('Products_Suppliers')";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
productSupplierID = Convert.ToInt32(selectCmd.ExecuteScalar());
//
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(productSupplierID);
}
/// <summary>
/// function is used to delete the data from Product_supplier table
/// </summary>
/// <param name="PS"></param>
/// <returns> true or false value, true if count is greater than 1 menaing more than 1 row was affected </returns>
public static bool DeleteProduct_Supplier(Product_Supplier productSupplier)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string deleteStatement = "DELETE FROM Products_Suppliers WHERE ProductSupplierID = @ProductSupplierID " +
"AND ProductID=@ProductID " +
"AND SupplierID=@SupplierID";
SqlCommand cmd = new SqlCommand(deleteStatement, con);
//values to be deleted
cmd.Parameters.AddWithValue("@ProductSupplierID", productSupplier.ProductSupplierID);
cmd.Parameters.AddWithValue("@ProductID", productSupplier.ProductID);
cmd.Parameters.AddWithValue("@SupplierID", productSupplier.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>
/// function update the product supplier table
/// </summary>
/// <param name="oldProduct_Supplier"></param>
/// <param name="newProduct_Supplier"></param>
/// <returns> true or false value, true if count is greater than 1 menaing more than 1 row was affected</returns>
public static bool UpdateProduct_Supplier(Product_Supplier 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.ProductSupplierID);
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>
/// function to get data from Product_Supplier table
/// </summary>
/// <param name="ProdcutSupplierID"></param>
/// <returns> list of product_supplier </returns>
public static List <Product_Supplier> GetProduct_Supplier()
{
List <Product_Supplier> productSuppliers = new List <Product_Supplier>(); //holds list of product_supplier objects
Product_Supplier ps;
SqlConnection con = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT ProductSupplierID, ProdName, SupName, ps.ProductID, ps.SupplierID " +
"FROM Products_Suppliers ps " +
"INNER JOIN Products p ON ps.ProductID = p.ProductID " +
"INNER JOIN Suppliers s ON ps.SupplierID = s.SupplierID " +
"ORDER BY ProductSupplierID";
SqlCommand cmd = new SqlCommand(selectStatement, con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //reads until nothing left to read
{
ps = new Product_Supplier();
ps.ProductSupplierID = (int)reader["ProductSupplierID"];
ps.ProductID = (int)reader["ProductID"];
ps.SupplierID = (int)reader["SupplierID"];
ps.ProdName = reader["ProdName"].ToString();
ps.SupName = reader["SupName"].ToString();
productSuppliers.Add(ps); //adds product_supplier to list
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(productSuppliers);
}
/// <summary>
/// adds row to package prodcut supplier connecting product and supplier info for a certain package
/// takes both a product supplier object and package object
/// </summary>
/// <param name="productSupplier"></param>
/// <param name="package"></param>
public static void Add(Product_Supplier productSupplier, Package package)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Packages_Products_Suppliers (PackageID, ProductSupplierID) " +
"VALUES (@PackageID, @ProductSupplierID)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@PackageID", package.PackageId);
cmd.Parameters.AddWithValue("@ProductSupplierID", productSupplier.ProductSupplierID);
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}