public static int UpdateProduct(Products oldProduct, Products newProduct)
{
int count = 0;
string updateStatement = "UPDATE Products SET " +
"ProdName = @NewProdName " +
"WHERE ProductId = @OldProductId " +
"AND ProdName = @OldProdName ";
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(updateStatement, con))
{
cmd.Parameters.AddWithValue("@NewProdName", newProduct.ProdName);
cmd.Parameters.AddWithValue("@OldProductId", oldProduct.ProductId); // PK is not null
cmd.Parameters.AddWithValue("@OldProdName", oldProduct.ProdName);
try
{
con.Open();
count = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
}
return(count);
}
public static int AddProduct(Products prod)
{
int prodId = 0;
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Products (ProdName) " +
"VALUES(@ProdName)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@ProdName", prod.ProdName);
try
{
con.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT IDENT_CURRENT('Products') FROM Products"; // Identity value
SqlCommand selectCommand = new SqlCommand(selectQuery, con);
prodId = Convert.ToInt32(selectCommand.ExecuteScalar()); // single value
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(prodId);
}
public static List <Products> GetProducts()
{
List <Products> products = new List <Products>();
Products prod;
string selectQuery = "SELECT ProductId, ProdName " +
"FROM Products " +
"ORDER BY ProductId";
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(selectQuery, con))
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
prod = new Products();
prod.ProductId = (int)dr["ProductId"];
prod.ProdName = (string)dr["ProdName"];
products.Add(prod);
}
}
}
return(products);
}
} // end add package
/// <summary>
/// Updates current record in the Packages table
/// </summary>
/// <param name="oldPackage">data before update</param>
/// <param name="newPackage">new data for the update</param>
/// <returns>indicator of success</returns>
public static int UpdatePackage(Package oldPackage, Package newPackage)
{
// connect
int count = 0;
SqlConnection connection = TravelExpertsDB.GetConnection();
string sqlUpdate = "UPDATE Packages SET " +
"PkgName = @NewPkgName, " +
"PkgStartDate = @NewPkgStartDate, " +
"PkgEndDate = @NewPkgEndDate, " +
"PkgDesc = @NewPkgDesc, " +
"PkgBasePrice = @NewPkgBasePrice, " +
"PkgAgencyCommission = @NewPkgAgencyCommission " +
"WHERE PackageID = @OldPackageID " +
"AND PkgStartDate = @OldPkgStartDate " +
"AND PkgEndDate = @OldPkgEndDate " +
"AND PkgDesc = @OldPkgDesc " +
"AND PkgBasePrice = @OldPkgBasePrice " +
"AND PkgAgencyCommission = @OldPkgAgencyCommission";
SqlCommand cmd = new SqlCommand(sqlUpdate, connection);
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);
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);
// check
try
{
// open connection
connection.Open();
count = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(count);
} // end update package
public static int UpdateSupplier(Suppliers oldSupplier, Suppliers newSupplier)
{
int count = 0;
string updateStatement = "UPDATE Suppliers SET " +
"SupName = @NewSupName " +
"WHERE SupplierId = @OldSupplierId " +
"AND (SupName = @OldSupName " +
" OR SupName IS NULL AND @OldSupName IS NULL)";
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(updateStatement, con))
{
if (newSupplier.SupName == null)
{
cmd.Parameters.AddWithValue("@NewSupName", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@NewSupName", newSupplier.SupName);
}
cmd.Parameters.AddWithValue("@OldSupplierId", oldSupplier.SupplierId); // PK is not null
if (oldSupplier.SupName == null)
{
cmd.Parameters.AddWithValue("@OldSupName", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@OldSupName", oldSupplier.SupName);
}
try
{
con.Open();
count = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}
}
return(count);
}
// select products that are included in the package
public static List <ProductsInPackage> GetProductsFromPackage(int packageID)
{
List <ProductsInPackage> products = new List <ProductsInPackage>();
ProductsInPackage prod = null;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectQuery = "SELECT q.ProductSupplierId, p.ProdName, s.SupName " +
"FROM Products p, Products_Suppliers q, Suppliers s, Packages t, " +
"Packages_Products_Suppliers u " +
"WHERE p.ProductId = q.ProductId and s.SupplierId = q.SupplierId " +
"and q.ProductSupplierId = u.ProductSupplierId and t.PackageId = u.PackageId " +
"and t.PackageId = @PackageId " +
"ORDER BY ProdName";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
cmd.Parameters.AddWithValue("@PackageID", packageID);
// check
try
{
// open the connection
connection.Open();
// execute the SELECT query
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read()) // we have a customer
{
// create new object
prod = new ProductsInPackage();
prod.ProductSupplierId = (int)dr["ProductSupplierId"];
prod.ProdName = (string)dr["ProdName"];
prod.SupName = (string)dr["SupName"];
products.Add(prod);
}
dr.Close();
return(products);
}
catch (SqlException ex)
{
throw ex;
}
}
} // end update package
/// <summary>
/// Delete package
/// </summary>
/// <param name="package">package to delete</param>
/// <returns>indicator of success</returns>
public static bool DeletePackage(Package package)
{
bool success = true;
// connect
SqlConnection connection = TravelExpertsDB.GetConnection();
string sqlDelete = "DELETE FROM Packages " +
"WHERE PackageId = @PackageId " + // to identify record
"AND PkgName = @PkgName " + // remaining: for optimistic concurrency
"AND PkgStartDate = @PkgStartDate " +
"AND PkgEndDate = @PkgEndDate " +
"AND PkgDesc = @PkgDesc " +
"AND PkgBasePrice = @PkgBasePrice " +
"AND PkgAgencyCommission = @PkgAgencyCommission";
SqlCommand cmd = new SqlCommand(sqlDelete, connection);
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);
// check
try
{
// open connection
connection.Open();
int count = cmd.ExecuteNonQuery();
// check if 0
if (count == 0)
{
success = false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
/*
* Author: Ibraheem
* Collaborator: DongMing Hu
*/
// ----- READ -----
public static List <Products_suppliers> GetProductsSuppliers()
{
List <Products_suppliers> prod_suppliers = new List <Products_suppliers>();
Products_suppliers prod_sup;
string selectQuery = "SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers " +
"ORDER BY ProductSupplierId";
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(selectQuery, con))
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
prod_sup = new Products_suppliers();
prod_sup.ProductSupplierId = (int)dr["ProductSupplierId"];
// Get the ProductId column number
int ProductIdColIndex = dr.GetOrdinal("ProductId");
if (dr.IsDBNull(ProductIdColIndex))
{
prod_sup.ProductId = null;
}
else
{
prod_sup.ProductId = Convert.ToInt32(dr["ProductId"]);
}
int SupplierIdColIndex = dr.GetOrdinal("SupplierId");
if (dr.IsDBNull(SupplierIdColIndex))
{
prod_sup.SupplierId = null;
}
else
{
prod_sup.SupplierId = Convert.ToInt32(dr["SupplierId"]);
}
prod_suppliers.Add(prod_sup);
}
}
}
return(prod_suppliers);
}
// ----- CREATE -----
public static int AddProductSupplier(Products_suppliers prod_sup)
{
int prodSupId = 0;
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierId) " +
"VALUES(@ProductId, @SupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
if (prod_sup.ProductId == null)
{
cmd.Parameters.AddWithValue("@ProductId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@ProductId", prod_sup.ProductId);
}
if (prod_sup.SupplierId == null)
{
cmd.Parameters.AddWithValue("@SupplierId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@SupplierId", prod_sup.SupplierId);
}
try
{
con.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT IDENT_CURRENT('Products_Suppliers') FROM Products_Suppliers"; // Identity value
SqlCommand selectCommand = new SqlCommand(selectQuery, con);
prodSupId = Convert.ToInt32(selectCommand.ExecuteScalar()); // single value
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(prodSupId);
}
} // end get package method
/// <summary>
/// Adds new package to the database (Packages table)
/// </summary>
/// <param name="package">object that contains data of the new package</param>
/// <returns>auto-generated PackageID</returns>
public static int AddPackage(Package package)
{
int packageID = 0;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
//create command object
string sqlInsert = "INSERT INTO Packages (PkgName, PkgStartDate, " +
"PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"OUTPUT inserted.[PackageId] " +
"VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, " +
"@PkgBasePrice, @PkgAgencyCommission)";
SqlCommand cmd = new SqlCommand(sqlInsert, connection);
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);
// check
try
{
// open connection
connection.Open();
// execute
packageID = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packageID);
} // end add package
public static int AddSupplier(Suppliers sup)
{
int supId = 0;
SqlConnection con = TravelExpertsDB.GetConnection();
string insertStatement = "INSERT INTO Suppliers(SupplierId,SupName) VALUES(@SupId,@SupName)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
// bind parameters
cmd.Parameters.AddWithValue("@SupId", sup.SupplierId);
if (sup.SupName == null)
{
cmd.Parameters.AddWithValue("@SupName", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@SupName", sup.SupName);
}
try
{
con.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT * FROM Suppliers WHERE SupplierId=@SupId"; // Identity value
SqlCommand selectCommand = new SqlCommand(selectQuery, con);
selectCommand.Parameters.AddWithValue("@SupId", sup.SupplierId);
supId = Convert.ToInt32(selectCommand.ExecuteScalar()); // single value
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(supId);
}
public static List <Suppliers> GetSuppliers()
{
List <Suppliers> suppliers = new List <Suppliers>();
Suppliers sup;
string selectQuery = "SELECT SupplierId, SupName " +
"FROM Suppliers " +
"ORDER BY SupplierId";
using (SqlConnection con = TravelExpertsDB.GetConnection())
{
using (SqlCommand cmd = new SqlCommand(selectQuery, con))
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
sup = new Suppliers();
sup.SupplierId = (int)dr["SupplierId"];
// Get the SupName column number
int supNameColIndex = dr.GetOrdinal("SupName");
if (dr.IsDBNull(supNameColIndex))
{
sup.SupName = null;
}
else
{
sup.SupName = dr["SupName"].ToString();
}
suppliers.Add(sup);
}
}
}
return(suppliers);
}
public static List <Product> GetProducts()
{
List <Product> products = new List <Product>();
Product product = null;
SqlConnection connection = TravelExpertsDB.GetConnection();
var sqlSelect = "SELECT ProductId, ProdName FROM Products";
//from Products p, Products_Suppliers s, Packages_Products_Suppliers r
//where p.ProductId = s.ProductId and s.ProductSupplierId = r.ProductSupplierId and PackageId = @PackageId
SqlCommand selectCmd = new SqlCommand(sqlSelect, connection);
// execute
try
{
// connect
connection.Open();
SqlDataReader dr = selectCmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
product = new Product();
product.ProductId = (int)dr["ProductId"];
product.ProdName = (string)dr["ProdName"];
products.Add(product);
}
dr.Close();
return(products);
}
catch (SqlException ex)
{
throw ex;
}
}
// add product-supplier to package product supplier
public static void AddProductsToNewPackage(int packageId, int productSupplierId)
{
packageId = 0;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
//create command object
string sqlInsert = "INSERT INTO Packages_Products_Suppliers (PackageId, ProductSupplierId) " +
"VALUES(@PackageId, @ProductSupplierId)";
SqlCommand cmd = new SqlCommand(sqlInsert, connection);
cmd.Parameters.AddWithValue("@PackageId", packageId);
cmd.Parameters.AddWithValue("@ProductSupplierId", productSupplierId);
// check
// check
try
{
// open connection
connection.Open();
// execute
cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Get Package List from Travel Experts database
/// </summary>
/// <returns>A list of all packages</returns>
public static List <Package> GetPackages(int packageId = 0, string search = "")
{
List <Package> packages = new List <Package>();
SqlConnection connection = TravelExpertsDB.GetConnection();
var sqlSelect = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, " +
"PkgBasePrice, PkgAgencyCommission " +
"FROM Packages";
// check package id
if (packageId != 0)
{
// update sql
sqlSelect += " WHERE PackageId=@PackageId";
}
// check search string
if (search != "")
{
// update query
sqlSelect += " WHERE (PkgName LIKE @search or PkgDesc LIKE @search)";
}
SqlCommand selectCmd = new SqlCommand(sqlSelect, connection);
// check package id
if (packageId != 0)
{
// bind
selectCmd.Parameters.AddWithValue("@PackageId", packageId);
}
// check search
if (search != "")
{
selectCmd.Parameters.AddWithValue("@search", "%" + search + "%");
}
// execute
try
{
// connect
connection.Open();
SqlDataReader dr = selectCmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
var package = new Package();
package.PackageId = (int)dr["PackageId"];
package.PkgName = (string)dr["PkgName"];
if (dr["PkgStartDate"] == DBNull.Value)
{
package.PkgStartDate = null;
}
else
{
package.PkgStartDate = (DateTime)dr["PkgStartDate"];
}
if (dr["PkgEndDate"] == DBNull.Value)
{
package.PkgEndDate = null;
}
else
{
package.PkgEndDate = (DateTime)dr["PkgEndDate"];
}
package.PkgDesc = (string)dr["PkgDesc"];
package.PkgBasePrice = (decimal)dr["PkgBasePrice"];
if (dr["PkgAgencyCommission"] == DBNull.Value)
{
package.PkgAgencyCommission = null;
}
else
{
package.PkgAgencyCommission = (decimal)dr["PkgAgencyCommission"];
}
packages.Add(package);
}
dr.Close();
return(packages);
}
catch (SqlException ex)
{
throw ex;
}
} // end get package method
// select products that are not yet included in packages
public static List <AvailableProducts> GetAvailableProducts(string search = "")
{
List <AvailableProducts> products = new List <AvailableProducts>();
AvailableProducts prod = null;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectQuery = "SELECT q.ProductSupplierId, p.ProdName, s.SupName " +
"FROM Products p, Products_Suppliers q, Suppliers s " +
"WHERE p.ProductId = q.ProductId and " +
"s.SupplierId = q.SupplierId and ProductSupplierId NOT IN " +
"(SELECT ProductSupplierId FROM Packages_Products_Suppliers) ";
// check search string
if (search != "")
{
// update query
selectQuery += " AND (p.ProdName LIKE @search or s.SupName LIKE @search)";
}
// add order to query
selectQuery += " ORDER BY ProdName";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
// check search
if (search != "")
{
// bind
cmd.Parameters.AddWithValue("@search", "%" + search + "%");
}
// check
try
{
// open the connection
connection.Open();
// execute the SELECT query
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read()) // we have a customer
{
// create new object
prod = new AvailableProducts();
prod.ProductSupplierId = (int)dr["ProductSupplierId"];
prod.ProdName = (string)dr["ProdName"];
prod.SupName = (string)dr["SupName"];
products.Add(prod);
}
dr.Close();
return(products);
}
catch (SqlException ex)
{
throw ex;
}
}
