// add product to db
public static int AddProduct(Product product)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Products (ProdName) " +
"VALUES(@Name)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@Name", product.ProdName);
try
{
con.Open();
cmd.ExecuteNonQuery(); // run the insert command
// get the generated ID - current identity value for Customers table
string selectQuery = "SELECT IDENT_CURRENT('Products') FROM Products";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int productId = Convert.ToInt32(selectCmd.ExecuteScalar()); // single value
return(productId);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
// get products from db
public static List <Product> GetProducts()
{
List <Product> products = new List <Product>();
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT ProductId, ProdName "
+ "FROM Products "
+ "ORDER BY ProductId";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Product prod = new Product();
prod.ProductId = (int)reader["ProductId"];
prod.ProdName = reader["ProdName"].ToString();
products.Add(prod);
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(products);
}
// update product to db
public static bool UpdateProduct(string oldProd, string newProd)
{
bool success = false;
SqlConnection dbConnect = TravelExpertsDB.GetConnection();
string updateProduct = "UPDATE Products " +
"SET ProdName = @ProdName " +
"WHERE ProdName = @oldProdId ";
SqlCommand cmd = new SqlCommand(updateProduct, dbConnect);
cmd.Parameters.AddWithValue("@ProdName", newProd);
cmd.Parameters.AddWithValue("@oldProdId", oldProd);
try
{
dbConnect.Open();
cmd.ExecuteNonQuery();
success = true;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConnect.Close();
}
return(success);
}
// get package based on prod/supp from db
public static bool LinkPackageProductSuppliers(int ProSupID, int PackageID)
{
bool results = false;
SqlConnection con = TravelExpertsDB.GetConnection();
string insertQuery = "Insert into Packages_Products_Suppliers(PackageId, ProductSupplierId) Values (@PackageID, @ProSupID)";
SqlCommand cmd = new SqlCommand(insertQuery, con);
cmd.Parameters.AddWithValue("@PackageID", PackageID);
cmd.Parameters.AddWithValue("@ProSupID", ProSupID);
try
{
con.Open();
cmd.ExecuteNonQuery();
results = true;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(results);
}
// get prod/supp from packageID from db
public static List <PackageProductSupplier> GetProSup(int ID)
{
List <PackageProductSupplier> list = new List <PackageProductSupplier>();
PackageProductSupplier ProSup;
SqlConnection con = TravelExpertsDB.GetConnection();
string selectQuery = "select PackageId, ProductSupplierId from Packages_Products_Suppliers where PackageId = @ID";
SqlCommand cmd = new SqlCommand(selectQuery, con);
cmd.Parameters.AddWithValue("@ID", ID);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ProSup = new PackageProductSupplier();
ProSup.PackageID = (int)reader["PackageId"];
ProSup.ProductSupplierID = (int)reader["ProductSupplierId"];
list.Add(ProSup);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(list);
}
// delete package from the db
public static void DeletePackage(int tp)
{
SqlConnection dbConnect = TravelExpertsDB.GetConnection();
string deletePackage = "DELETE FROM [Packages] " +
"WHERE PackageId = @PkgID";
SqlCommand cmd = new SqlCommand(deletePackage, dbConnect);
cmd.Parameters.AddWithValue("@PkgID", tp);
try
{
dbConnect.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConnect.Close();
}
}
// get prodsupp from db
public static List <ProductSuppliers> GetProductSuppliers()
{
List <ProductSuppliers> ProSups = new List <ProductSuppliers>();
ProductSuppliers ProSup;
SqlConnection con = TravelExpertsDB.GetConnection();
string selectQuery = "Select SP.ProductSupplierId, SP.ProductId, P.ProdName, S.SupName, SP.SupplierId " +
"from Products P " +
"Join Products_Suppliers SP on P.ProductId = SP.ProductId " +
"Join Suppliers S on SP.SupplierId = S.SupplierId";
SqlCommand cmd = new SqlCommand(selectQuery, con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ProSup = new ProductSuppliers();
ProSup.ProdName = (string)reader["ProdName"];
ProSup.ProductSupplierId = (int)reader["ProductSupplierId"];
ProSup.SupName = (string)reader["SupName"];
ProSup.ProductId = (int)reader["ProductId"];
ProSup.SupplierId = (int)reader["SupplierId"];
ProSups.Add(ProSup);
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(ProSups);
}
public static void DeleteSupplier(string supplier)
{
SqlConnection dbConnect = TravelExpertsDB.GetConnection();
string deletePackage = "DELETE FROM [Suppliers] " +
"WHERE SupName = @SupName";
SqlCommand cmd = new SqlCommand(deletePackage, dbConnect);
cmd.Parameters.AddWithValue("@SupName", supplier);
try
{
dbConnect.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConnect.Close();
}
}
// update package from the db
public static bool UpdatePackage(TravelPackage oldPkg, TravelPackage newPkg)
{
bool success = false;
SqlConnection dbConnect = TravelExpertsDB.GetConnection();
string updatePackage = "UPDATE Packages " +
"SET PkgName = @PkgName, " +
"PkgDesc = @PkgDesc, " +
"PkgStartDate = @PkgStartDate, " +
"PkgEndDate = @PkgEndDate, " +
"PkgBasePrice = @PkgBasePrice, " +
"PkgAgencyCommission = @PkgAgencyCommission " +
"WHERE PackageId = @oldPkgID ";
SqlCommand cmd = new SqlCommand(updatePackage, dbConnect);
cmd.Parameters.AddWithValue("@PkgName", newPkg.PkgName);
cmd.Parameters.AddWithValue("@PkgStartDate", newPkg.PkgStartDate);
cmd.Parameters.AddWithValue("@PkgEndDate", newPkg.PkgEndDate);
cmd.Parameters.AddWithValue("@PkgDesc", newPkg.PkgDesc);
cmd.Parameters.AddWithValue("@PkgBasePrice", newPkg.PkgBasePrice);
cmd.Parameters.AddWithValue("@PkgAgencyCommission", newPkg.PkgAgencyCommission);
cmd.Parameters.AddWithValue("@oldPkgID", oldPkg.PkgID);
try
{
dbConnect.Open();
cmd.ExecuteNonQuery();
success = true;
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConnect.Close();
}
return(success);
}
// get package from db
public static List <TravelPackage> GetTavelPackage()
{
List <TravelPackage> packages = new List <TravelPackage>(); // empty list of Packages
TravelPackage travelPackage;
SqlConnection dbConnect = TravelExpertsDB.GetConnection();
string selectQuery = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages";
SqlCommand cmd = new SqlCommand(selectQuery, dbConnect);
try
{
dbConnect.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
travelPackage = new TravelPackage();
travelPackage.PkgID = (int)reader["PackageId"];
travelPackage.PkgName = (string)reader["PkgName"];
travelPackage.PkgStartDate = (DateTime)reader["PkgStartDate"];
travelPackage.PkgEndDate = (DateTime)reader["PkgEndDate"];
travelPackage.PkgDesc = (string)reader["PkgDesc"];
travelPackage.PkgBasePrice = (decimal)reader["PkgBasePrice"];
travelPackage.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"];
packages.Add(travelPackage);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConnect.Close();
}
return(packages);
}
public static List <Supplier> GetSuppliers()
{
List <Supplier> suppliers = new List <Supplier>(); // empty list
Supplier sup; // for reading
SqlConnection con = TravelExpertsDB.GetConnection();
string selectQuery = "SELECT SupplierId, SupName FROM dbo.Suppliers ORDER BY SupName";
SqlCommand cmd = new SqlCommand(selectQuery, con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
sup = new Supplier();
sup.SupplierId = (int)reader["SupplierId"];
sup.SupName = (string)reader["SupName"];
suppliers.Add(sup); // add the new order object to the list
}
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(suppliers);
}
