/// <summary>
/// Adds a new product supplier to Travel Experss DB
/// </summary>
/// <param name="productSupplier"> ProductSupplier object that contains data for the new record</param>
/// <returns>new productSupplier id</returns>
public static int AddProductSupplier(ProductSupplier productSupplier)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierId) " +
"VALUES (@ProductId, @SupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@ProductId", productSupplier.ProductID);
cmd.Parameters.AddWithValue("@SupplierId", productSupplier.SupplierID);
try
{
con.Open();
cmd.ExecuteNonQuery(); // run insert command
// get the generated ID - current identity value from the Products_Suppliers table
string selectQuery = "SELECT IDENT_CURRENT('Products_Suppliers') FROM Products_Suppliers";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int productSupplierId = Convert.ToInt32(selectCmd.ExecuteScalar());
return(productSupplierId);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// The GetOtherProductSuppliers() retrieves all other products/suppliers that can be added
/// to the package.
/// </summary>
/// <param name="pkgId"></param>
/// <returns>A list of other available products/suppliers</returns>
public static List <ProductSupplier> GetOtherProductSuppliers(int pkgId)
{
List <ProductSupplier> prodSupList = new List <ProductSupplier>(); // Empty list for storing the products/suppliers
ProductSupplier ps = null; // Empty package object for reading
// Open a connection to the database
SqlConnection con = TravelExpertsDB.GetConnection();
// Define the SQL statement and execute the command for retrieving the products/suppliers
// associated with a package
string selectStatement = "SELECT pps.ProductSupplierId as ProductSupplierId, " +
"p.ProductId as ProductId, ProdName, " +
"s.SupplierId as SupplierId, SupName " +
"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 pps.ProductSupplierID NOT IN " +
"(SELECT ProductSupplierID FROM Packages_Products_Suppliers " +
"WHERE PackageId = @PackageId)";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@PackageId", pkgId);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
ps = new ProductSupplier();
ps.ProductSupplierID = Convert.ToInt32(reader["ProductSupplierId"]);
ps.ProductID = Convert.ToInt32(reader["ProductId"]);
ps.ProductName = reader["ProdName"].ToString();
ps.SupplierID = Convert.ToInt32(reader["SupplierId"]);
ps.SupName = reader["SupName"].ToString();
prodSupList.Add(ps);
}
return(prodSupList);
}
// If unsuccessful, throw the exception (it will be an SqlException object)
catch (SqlException ex)
{
throw ex;
}
// Close the database connection
finally
{
con.Close();
}
}
public ProductSupplier GetCopy()
{
ProductSupplier copy = new ProductSupplier();
copy.ProductSupplierID = this.ProductSupplierID;
copy.ProductID = this.ProductID;
copy.ProductName = this.ProductName;
copy.SupplierID = this.SupplierID;
copy.SupName = this.SupName;
return(copy);
}
/// <summary>
/// Returns all the products suppliers from the Travel experts DB
/// </summary>
/// <returns>a list of products suppliers</returns>
public static List <ProductSupplier> GetAllProductSuppliers()
{
List <ProductSupplier> productSuppliers = new List <ProductSupplier>();
ProductSupplier currentProductSupplier = null;
SqlConnection con = TravelExpertsDB.GetConnection();
/*
* select ProductSupplierId, p.ProductId, ProdName, s.SupplierId, SupName
* from [dbo].[Products_Suppliers] ps
* inner join products p on ps.ProductId = p.ProductId
* inner join Suppliers s on ps.SupplierId = s.SupplierId
*/
string selectStatement = "SELECT ProductSupplierId, p.ProductId as ProductId, ProdName, s.SupplierId as SupplierId, SupName "
+ " 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()) //While there are orders
{
currentProductSupplier = new ProductSupplier();
currentProductSupplier.SupplierID = Convert.ToInt32(reader["SupplierId"]);
currentProductSupplier.SupName = Convert.ToString(reader["SupName"]);
currentProductSupplier.ProductSupplierID = Convert.ToInt32(reader["ProductSupplierId"]);
currentProductSupplier.ProductID = Convert.ToInt32(reader["ProductId"]);
currentProductSupplier.ProductName = Convert.ToString(reader["ProdName"]);
productSuppliers.Add(currentProductSupplier);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(productSuppliers);
}
/// <summary>
/// Updates existing productsupplier record.
/// Uses optimistic concurrency to ensure that the data hasn't changed
/// since we last read from the database.
/// </summary>
/// <param name="oldProdSup">data before update</param>
/// <param name="newProdSup">new data for the update</param>
/// <returns>indicator of success</returns>
public static bool UpdateProductSupplier(ProductSupplier oldProdSup, ProductSupplier newProdSup)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string updateStatement = "UPDATE Products_Suppliers " +
"SET ProductId = @NewProductId, SupplierId = @NewSupplpierID " +
"WHERE ProductSupplierId = @ProductSupplierId " +
"AND ProductId = @OldProductId " +//This is to check that the supplier name hasn't changed
"AND SupplierId = @OldSupplierId";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@NewProductId", newProdSup.ProductID);
cmd.Parameters.AddWithValue("@NewSupplpierID", newProdSup.SupplierID);
cmd.Parameters.AddWithValue("@ProductSupplierId", oldProdSup.ProductSupplierID);
cmd.Parameters.AddWithValue("@OldProductId", oldProdSup.ProductID);
cmd.Parameters.AddWithValue("@OldSupplierId", oldProdSup.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>
/// Deletes a productSupplier from the travel experts database
/// Uses optimistic concurrency to ensure that the data hasn't changed since we
/// last read from the database.
/// </summary>
/// <param name="prodSup">ProductSupplier to delete</param>
/// <returns>Bool value indicating success</returns>
public static bool DeleteProductSupplier(ProductSupplier prodSup)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string deleteStatement = "DELETE FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId " + // to identify the productSupplier to be deleted
" AND ProductId = @ProductId " + // remaining conditions - to ensure optimistic concurrency
" AND SupplierId = @SupplierId";
SqlCommand cmd = new SqlCommand(deleteStatement, con);
cmd.Parameters.AddWithValue("@ProductSupplierId", prodSup.ProductSupplierID);
cmd.Parameters.AddWithValue("@SupplierId", prodSup.SupplierID);
cmd.Parameters.AddWithValue("@ProductId", prodSup.ProductID);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}