/// <summary>
/// The AddProductToPackage() method adds a new record to the Packages_Products_Suppliers
/// table, in order to add a new product to a package.
/// </summary>
/// <param name="pkgId", "prodSupId"></param>
/// <returns>true if successful</returns>
public static bool AddProductToPackage(int pkgId, int prodSupId)
{
// Open a connection to the database
SqlConnection con = TravelExpertsDB.GetConnection();
// Define the SQL statement and execute the command for retrieving the package information.
// At most a single row will be returned.
string selectStatement = "INSERT INTO Packages_Products_Suppliers " +
"(PackageId, ProductSupplierId) " +
"VALUES (@PackageID, @ProductSupplierId)";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@PackageId", pkgId);
cmd.Parameters.AddWithValue("@ProductSupplierId", prodSupId);
try
{
con.Open();
cmd.ExecuteNonQuery();
}
// If unsuccessful, throw the exception (it will be an SqlException object)
catch (SqlException ex)
{
throw ex;
}
// Close the database connection
finally
{
con.Close();
}
return(true);
}
/// <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();
}
}
/// <summary>
/// The DeletePackage() methods deletes the specified package from the Packages table.
/// Optimistic concurrency is enforced by ensuring that all of the database fields
/// match the properties in the pkg object.
/// </summary>
/// <param name="pkg"></param>
/// <returns>true on success, false otherwise</returns>
public static bool DeletePackage(Package pkg)
{
// Open a connection to the database
SqlConnection con = TravelExpertsDB.GetConnection();
// Define the SQL statement and execute the command for deleting the package.
// Allow for null values where the database allows nulls.
string deleteStatement = "DELETE FROM Packages " +
"WHERE PackageId = @PackageId " +
"AND PkgName = @PkgName " +
"AND (PkgStartDate = @PkgStartDate OR (PkgStartDate IS NULL AND @PkgStartDate IS NULL)) " +
"AND (PkgEndDate = @PkgEndDate OR (PkgEndDate IS NULL AND @PkgEndDate IS NULL)) " +
"AND (PkgDesc = @PkgDesc OR (PkgDesc IS NULL AND @PkgDesc IS NULL)) " +
"AND PkgBasePrice = @PkgBasePrice " +
"AND (PkgAgencyCommission = @PkgAgencyCommission OR (PkgAgencyCommission IS NULL AND @PkgAgencyCommission IS NULL))";
SqlCommand cmd = new SqlCommand(deleteStatement, con);
cmd.Parameters.AddWithValue("@PackageId", pkg.PackageId);
cmd.Parameters.AddWithValue("@PkgName", pkg.PackageName);
cmd.Parameters.AddWithValue("@PkgStartDate", pkg.PkgStartDate);
cmd.Parameters.AddWithValue("@PkgEndDate", pkg.PkgEndDate);
cmd.Parameters.AddWithValue("@PkgDesc", pkg.PkgDesc);
cmd.Parameters.AddWithValue("@PkgBasePrice", pkg.PkgBasePrice);
cmd.Parameters.AddWithValue("@PkgAgencyCommission", pkg.PkgAgencyCommission);
try
{
con.Open();
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
return(true);
}
else
{
return(false);
}
}
// If unsuccessful, throw the exception (it will be an SqlException object)
catch (SqlException ex)
{
throw ex;
}
// Close the database connection
finally
{
con.Close();
}
}
/// <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>
/// The GetPackage() method returns the information from the Packages table with
/// the PackageId matching pkgId.
/// </summary>
/// <param name="pkgId"></param>
/// <returns>Package object</returns>
public static Package GetPackage(int pkgId)
{
Package pkg = null; // Empty Package object for storing the results
// Open a connection to the database
SqlConnection con = TravelExpertsDB.GetConnection();
// Define the SQL statement and execute the command for retrieving the package information.
// At most a single row will be returned.
string selectStatement = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, " +
"PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages " +
"WHERE PackageId = @PackageID";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@PackageId", pkgId);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);
// TO DO -- should any of these fields allow for null values?
// If a package is found, populate the Package object
if (reader.Read())
{
pkg = new Package();
pkg.PackageId = Convert.ToInt32(reader["PackageId"]);
pkg.PackageName = reader["PkgName"].ToString();
pkg.PkgStartDate = (DateTime)reader["PkgStartDate"];
pkg.PkgEndDate = (DateTime)reader["PkgEndDate"];
pkg.PkgDesc = reader["PkgDesc"].ToString();
pkg.PkgBasePrice = Convert.ToDecimal(reader["PkgBasePrice"]);
pkg.PkgAgencyCommission = Convert.ToDecimal(reader["PkgAgencyCommission"]);
}
}
// If unsuccessful, throw the exception (it will be an SqlException object)
catch (SqlException ex)
{
throw ex;
}
// Close the database connection
finally
{
con.Close();
}
return(pkg);
}
/// <summary>
/// The GetAllPackages() method returns a list of all packages from the Packages table.
/// </summary>
/// <returns>List of Package objects</returns>
public static List <Package> GetAllPackages()
{
List <Package> pkgList = new List <Package>(); // Empty list for storing the packages
Package pkg = 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 package information.
string selectStatement = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, " +
"PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages";
SqlCommand cmd = new SqlCommand(selectStatement, con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
// TO DO -- should any of these fields allow for null values?
// If a package is found, populate the Package object
while (reader.Read())
{
pkg = new Package();
pkg.PackageId = Convert.ToInt32(reader["PackageId"]);
pkg.PackageName = reader["PkgName"].ToString();
pkg.PkgStartDate = (DateTime)reader["PkgStartDate"];
pkg.PkgEndDate = (DateTime)reader["PkgEndDate"];
pkg.PkgDesc = reader["PkgDesc"].ToString();
pkg.PkgBasePrice = Convert.ToDecimal(reader["PkgBasePrice"]);
pkg.PkgAgencyCommission = Convert.ToDecimal(reader["PkgAgencyCommission"]);
pkgList.Add(pkg);
}
}
// If unsuccessful, throw the exception (it will be an SqlException object)
catch (SqlException ex)
{
throw ex;
}
// Close the database connection
finally
{
con.Close();
}
return(pkgList);
}
/// <summary>
/// The AddPackage() method adds a new row to the Packages table, using the data in the
/// package object pkg passed to it.
/// </summary>
/// <param name="pkg"></param>
/// <returns>pkgId of the new record</returns>
public static int AddPackage(Package pkg)
{
// Open a connection to the database
SqlConnection con = TravelExpertsDB.GetConnection();
// Define the SQL statement and execute the command for inserting the new package.
string selectStatement = "INSERT INTO Packages " +
"(PkgName, PkgStartDate, PkgEndDate, " +
"PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"VALUES (@PkgName, @PkgStartDate, @PkgEndDate," +
"@PkgDesc, @PkgBasePrice, @PkgAgencyCommission)";
SqlCommand cmd = new SqlCommand(selectStatement, con);
cmd.Parameters.AddWithValue("@PkgName", pkg.PackageName);
cmd.Parameters.AddWithValue("@PkgStartDate", pkg.PkgStartDate);
cmd.Parameters.AddWithValue("@PkgEndDate", pkg.PkgEndDate);
cmd.Parameters.AddWithValue("@PkgDesc", pkg.PkgDesc);
cmd.Parameters.AddWithValue("@PkgBasePrice", pkg.PkgBasePrice);
cmd.Parameters.AddWithValue("@PkgAgencyCommission", pkg.PkgAgencyCommission);
try
{
con.Open();
cmd.ExecuteNonQuery();
// Obtain the packageId of the newly created record
string selectPkgId = "SELECT IDENT_CURRENT('Packages') FROM Packages";
SqlCommand selectCmd = new SqlCommand(selectPkgId, con);
int pkgId = Convert.ToInt32(selectCmd.ExecuteScalar());
return(pkgId);
}
// If unsuccessful, throw the exception (it will be an SqlException object)
catch (SqlException ex)
{
throw ex;
}
// Close the database connection
finally
{
con.Close();
}
}
/// <summary>
/// Adds a new supplier to Travel Expers DB
/// </summary>
/// <param name="supplier"> Supplier object that contains data for the new record</param>
/// <returns>new supplier id</returns>
public static int AddSupplier(Supplier supplier)
{
//Get the connection
SqlConnection con = TravelExpertsDB.GetConnection();
//Prepare the insert statement
string insertStatement = "INSERT INTO Suppliers (SupplierId, SupName) " +
"VALUES(@SupplierId, @Name)";
//Prepare the statement to get the highest SupplierID in the DB
string highestIDStatement = "SELECT MAX(SupplierId) from Suppliers";
SqlCommand readCmd = new SqlCommand(highestIDStatement, con);
try
{
con.Open();
int currentHighestSupplierID = Convert.ToInt32(readCmd.ExecuteScalar()); // single value
supplier.SupplierID = ++currentHighestSupplierID; //Increament the supplier ID and assign to new supplier
//Prepare to insert the new supplier
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@SupplierId", supplier.SupplierID);
cmd.Parameters.AddWithValue("@Name", supplier.SupplierName);
cmd.ExecuteNonQuery(); // run the insert command
return(currentHighestSupplierID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <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>
/// Returns all the suppliers from the Travel experts DB
/// </summary>
/// <returns>a list of suppliers</returns>
public static List <Supplier> GetAllSuppliers()
{
List <Supplier> suppliers = new List <Supplier>();
Supplier currentSupplier = null;
SqlConnection con = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT * "
+ "From Suppliers " +
"ORDER BY SupName";
SqlCommand cmd = new SqlCommand(selectStatement, con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //While there are orders
{
currentSupplier = new Supplier();
currentSupplier.SupplierID = Convert.ToInt32(reader["SupplierId"]);
currentSupplier.SupplierName = Convert.ToString(reader["SupName"]);
suppliers.Add(currentSupplier);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(suppliers);
}
/// <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();
}
}
