public static int AddProduct(Product product)
{
int productID; //hold productID value (might not need this)
SqlConnection con = TravelExpertsDB.GetConnection(); //connect to database
//SQL string to insert new product, dont need ProductID as it is Auto increment
string insertStatement = "INSERT INTO Products (ProdName) " +
"VALUES (@ProdName)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@ProdName", product.ProdName); //add value to parameter in sql string
try
{
con.Open();
cmd.ExecuteNonQuery(); //execute the insert sql string
string selectQuery = "SELECT IDENT_CURRENT ('Products')"; //might not need this code below as i might not need to return Prodcut ID
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
productID = Convert.ToInt32(selectCmd.ExecuteScalar());
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(productID);
}
/// <summary>
/// this is used to deleted a selected product by the user from the travel experts database
/// </summary>
/// <param name="product"></param>
/// <returns> returns a true or false value, true if the count of the rows affected is greater than 1 meaning the row was deleted </returns>
public static bool DeleteProduct(Product product)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string deleteStatement = "DELETE FROM Products " +
"WHERE ProductID = @ProductID " +
"AND ProdName = @ProdName";
SqlCommand cmd = new SqlCommand(deleteStatement, con);
cmd.Parameters.AddWithValue("@ProductID", product.ProductID); //product id of the user chosen value to be deleted
cmd.Parameters.AddWithValue("@ProdName", product.ProdName); //product name of the user chosen value to be deleted
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// user picks a product to be updated this methods is called and updates that product in the database
/// </summary>
/// <param name="oldProduct"></param>
/// <param name="newProduct"></param>
/// <returns> true or false value, true if count is greater than 1 meaning more than 1 row was affected</returns>
public static bool UpdateProduct(Product oldProduct, Product newProduct)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string updateStatement = "UPDATE Products " +
"SET ProdName = @NewProdName " +
"WHERE ProductID = @OldProductID " +
"AND ProdName = @OldProdName";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@NewProdName", newProduct.ProdName); //new value entered in database
//start adding values of old product
cmd.Parameters.AddWithValue("@OldProductID", oldProduct.ProductID);
cmd.Parameters.AddWithValue("@OldProdName", oldProduct.ProdName); //old name to be changed
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// Delete a new supplier to the Suppliers table in TravelExperts database
/// </summary>
/// <param name="s"> supplier object that cotaing data for the new record</param>
/// <returns>generated SupplierId</returns>
public static bool DeleteSupplier(Supplier sup)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string deleteStatement = "DELETE FROM Suppliers " +
"WHERE SupplierId = @SupplierId " + // to identify the supplier to be deleted
"AND SupName = @SupName"; // remaining conditions - to ensure optimistic concurrency
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@SupplierId", sup.SupplierId);
cmd.Parameters.AddWithValue("@SupName", sup.SupName);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Adds a new supplier to the Suppliers table in TravelExperts database
/// </summary>
/// <param name="s"> supplier object that cotaing data for the new record</param>
/// <returns>generated SupplierId</returns>
public static bool AddSupplier(Supplier supplier)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Suppliers (SupplierId, SupName) " +
"VALUES(@SupplierId, @SupName)";
SqlCommand cmd = new SqlCommand(insertStatement, connection);
cmd.Parameters.AddWithValue("@SupplierId", supplier.SupplierId);
cmd.Parameters.AddWithValue("@SupName", supplier.SupName);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery(); // run the insert command
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <Supplier> GetAllSuppliers()
{
List <Supplier> suppliers = new List <Supplier>(); //empty list
Supplier s = null;
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT SupplierId, SupName " +
"FROM Suppliers ORDER BY SupplierId";
SqlCommand cmd = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) // while there is another record
{
s = new Supplier();
s.SupplierId = (int)reader["SupplierId"];
s.SupName = reader["SupName"].ToString();
suppliers.Add(s);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(suppliers);
}
//end roberts works
/// <summary>
/// Updates existing supplier record
/// </summary>
/// <param name="oldS">data before update</param>
/// <param name="newS">new data for the update</param>
/// <returns>indicator of success</returns>
public static bool UpdateSupplier(Supplier oldSup, Supplier newSup)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string updateStatement = "UPDATE Suppliers " +
"SET SupName = @NewSupName " +
"WHERE SupplierId = @OldSupplierId " +
"AND SupName = @OldSupName";
SqlCommand cmd = new SqlCommand(updateStatement, connection);
cmd.Parameters.AddWithValue("NewSupName", newSup.SupName);
cmd.Parameters.AddWithValue("@OldSupplierId", newSup.SupplierId);
cmd.Parameters.AddWithValue("OldSupName", oldSup.SupName);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
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();
}
}
/// <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>
/// updates a package with new info user inputs
/// </summary>
/// <param name="oldPackage"></param>
/// <param name="newPackage"></param>
/// <returns> true or false, true if number of rows affected are greated than 0 menaing update was succesful</returns>
public static bool UpdatePackages(Package oldPackage, Package newPackage)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string updateStatement = "UPDATE Packages " +
"SET PkgName = @NewPkgName, " +
"PkgStartDate = @NewPkgStartDate, " +
"PkgEndDate = @NewPkgEndDate, " +
"PkgDesc = @NewPkgDesc, " +
"PkgBasePrice = @NewPkgBasePrice, " +
"PkgAgencyCommission = @NewPkgAgencyCommission " +
"WHERE PackageID = @OldPackageID " +
"AND PkgName = @OldPkgName " +
"AND PkgStartDate = @OldPkgStartDate " +
"AND PkgEndDate = @OldPkgEndDate " +
"AND PkgDesc = @OldPkgDesc " +
"AND PkgBasePrice = @OldPkgBasePrice " +
"AND PkgAgencyCommission = @OldPkgAgencyCommission";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@NewPkgName", newPackage.PkgName);
cmd.Parameters.AddWithValue("@NewPkgStartDate", newPackage.PkgStartDate);
cmd.Parameters.AddWithValue("@NewPkgEndDate", newPackage.PkgEndDate);
cmd.Parameters.AddWithValue("@NewPkgDesc", newPackage.PkgDesc);
cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPackage.PkgBasePrice);
cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", newPackage.PkgAgencyCommission);
//start adding values of old package
cmd.Parameters.AddWithValue("@OldPackageId", oldPackage.PackageId);
cmd.Parameters.AddWithValue("@OldPkgName", oldPackage.PkgName);
cmd.Parameters.AddWithValue("@OldPkgStartDate", oldPackage.PkgStartDate);
cmd.Parameters.AddWithValue("@OldPkgEndDate", oldPackage.PkgEndDate);
cmd.Parameters.AddWithValue("@OldPkgDesc", oldPackage.PkgDesc);
cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPackage.PkgBasePrice);
cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", oldPackage.PkgAgencyCommission);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// used to deleted a user selected packagefrom the database
/// </summary>
/// <param name="package"></param>
/// <returns> true or false, true if count is greater than 1 menaing more than one row in the database was affected</returns>
public static bool DeletePackage(Package package)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string deleteStatement = "Delete FROM Packages " +
"WHERE PackageId = @PackageId " +
"AND PkgName = @PkgName " +
"AND PkgStartDate = @PkgStartDate " +
"AND PkgEndDate = @PkgEndDate " +
"AND PkgDesc = @PkgDesc " +
"AND PkgBasePrice = @PkgBasePrice " +
"AND PkgAgencyCommission = @PkgAgencyCommission";
SqlCommand cmd = new SqlCommand(deleteStatement, con);
//add values to use to find the right value to delete
cmd.Parameters.AddWithValue("@PackageId", package.PackageId);
cmd.Parameters.AddWithValue("@PkgName", package.PkgName);
cmd.Parameters.AddWithValue("@PkgStartDate", package.PkgStartDate);
cmd.Parameters.AddWithValue("@PkgEndDate", package.PkgEndDate);
cmd.Parameters.AddWithValue("@PkgDesc", package.PkgDesc);
cmd.Parameters.AddWithValue("@PkgBasePrice", package.PkgBasePrice);
cmd.Parameters.AddWithValue("@PkgAgencyCommission", package.PkgAgencyCommission);
try
{
con.Open();
int count = cmd.ExecuteNonQuery();// returns int for rows affected
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();
}
}
public static int GetNextSupplierID()
{
int supplierID; //holds the supplier id value
SqlConnection con = TravelExpertsDB.GetConnection(); //calls connection method
string selectQuery = "SELECT MAX(SupplierId)+1 FROM Suppliers"; //gets max current supplier id and adds 1
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
try
{
con.Open(); // open connection
supplierID = Convert.ToInt32(selectCmd.ExecuteScalar());
return(supplierID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close(); // close connection
}
}
/// <summary>
/// gets the value of the new auto generated package id
/// </summary>
/// <returns> new supplier id</returns>
public static int GetNextPackageID()
{
int supplierID;//supplier id auto generated
SqlConnection con = TravelExpertsDB.GetConnection();
string selectQuery = "SELECT IDENT_CURRENT('Packages') + IDENT_INCR('Packages')";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
try
{
con.Open();
supplierID = Convert.ToInt32(selectCmd.ExecuteScalar());
return(supplierID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
/// <summary>
/// gets the next product id from the product id column
/// </summary>
/// <returns>returns the value of the product id</returns>
public static int GetNextProductID()
{
int productId; //holds value of product id from database
SqlConnection con = TravelExpertsDB.GetConnection();
string selectQuery = "SELECT IDENT_CURRENT('Products') + IDENT_INCR('Products')"; //gets the current identity for the products table and then adds the identity increment value
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
try
{
con.Open();
productId = Convert.ToInt32(selectCmd.ExecuteScalar());
return(productId);
}
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
}
/// <summary>
/// gets the next product supplier id when user is adding a new supplier
/// </summary>
/// <returns> product supplier id to dispaly for the user to see what the id is for the new info they are entering</returns>
public static int GetNextProductSupplierID()
{
int productSupplier;
SqlConnection con = TravelExpertsDB.GetConnection();
//selects the current indentity and then adds the identity increment for the table
string selectQuery = "SELECT IDENT_CURRENT('Products_Suppliers') + IDENT_INCR('Products_Suppliers')";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
try
{
con.Open();
productSupplier = Convert.ToInt32(selectCmd.ExecuteScalar());
return(productSupplier);
}
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>
/// Gets list of all products from the database
/// </summary>
/// <returns>list of product objects </returns>
public static List <Product> GetAllProduct()
{
//list to hold all of product objects
List <Product> products = new List <Product>();
Product prod = null;
//create connection
SqlConnection con = TravelExpertsDB.GetConnection();
//SQL string to make selection
string selectStatement = "SELECT ProductID, ProdName " +
"FROM Products ORDER BY ProductID";
SqlCommand cmd = new SqlCommand(selectStatement, con);
try
{
//open connection
con.Open();
// create SQL reader
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) // keep reading while values in table
{
prod = new Product();
prod.ProductID = (int)reader["ProductID"];
prod.ProdName = reader["ProdName"].ToString();
products.Add(prod);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
//return list of products
return(products);
}
public static List <Supplier> GetSuppliersForProducts(Product product)
{
List <Supplier> suppliers = new List <Supplier>(); //empty list
Supplier s = null;
SqlConnection connection = TravelExpertsDB.GetConnection(); // DB connection
string selectStatement = "SELECT ps.SupplierID, SupName " +
"FROM Suppliers s " +
"INNER JOIN Products_Suppliers ps ON s.SupplierID = ps.SupplierID " +
"INNER JOIN Products p ON ps.ProductID = p.ProductID " +
"WHERE p.ProductID = @ProductID " +
"ORDER BY SupName";
SqlCommand cmd = new SqlCommand(selectStatement, connection);
cmd.Parameters.AddWithValue("@ProductID", product.ProductID);
try
{
connection.Open(); // open connection
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) // while there is another record
{
s = new Supplier();
s.SupplierId = (int)reader["SupplierId"];
s.SupName = reader["SupName"].ToString();
suppliers.Add(s);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close(); //close connection
}
return(suppliers);
}
/// <summary>
/// inserts a new package into the database
/// </summary>
/// <param name="package"></param>
/// <returns> package id for the new item</returns>
public static int AddPackage(Package package)
{
int packageID;//new package id
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
//adding values to be inserted
cmd.Parameters.AddWithValue("@PkgName", package.PkgName);
cmd.Parameters.AddWithValue("@PkgStartDate", package.PkgStartDate);
cmd.Parameters.AddWithValue("@PkgEndDate", package.PkgEndDate);
cmd.Parameters.AddWithValue("@PkgDesc", package.PkgDesc);
cmd.Parameters.AddWithValue("@PkgBasePrice", package.PkgBasePrice);
cmd.Parameters.AddWithValue("@PkgAgencyCommission", package.PkgAgencyCommission);
try
{
con.Open();
cmd.ExecuteNonQuery();
//TODO: might not need this code below
String selectQuery = "SELECT IDENT_CURRENT('Packages')";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
packageID = Convert.ToInt32(selectCmd.ExecuteScalar());
//
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(packageID);
}
/// <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();
}
}
/// <summary>
/// gets all the information from the package table
/// </summary>
/// <returns> list of package objects </returns>
public static List <Package> GetPackage()
{
List <Package> packages = new List <Package>();
Package pack = null;
SqlConnection con = TravelExpertsDB.GetConnection();
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();
while (reader.Read()) // reads while still info in database
{
pack = new Package();
pack.PackageId = (int)reader["PackageID"];
pack.PkgName = reader["PkgName"].ToString();
pack.PkgStartDate = (DateTime)reader["PkgStartDate"];
pack.PkgEndDate = (DateTime)reader["PkgEndDate"];
pack.PkgDesc = reader["PkgDesc"].ToString();
pack.PkgBasePrice = Convert.ToDouble(reader["PkgBasePrice"]);
pack.PkgAgencyCommission = Convert.ToDouble(reader["PkgAgencyCommission"]);
packages.Add(pack); //adds package object to packages list
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(packages); //list of package objects
}
