//receives supplier object, deletes record in pps table
public static void DeletePackageProductSuppliersBySupplier(Supplier s)
{
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"DELETE pps " +
"FROM " +
"[Packages_Products_Suppliers] pps " +
"inner join[Products_Suppliers] ps " +
"ON pps.ProductSupplierId = ps.ProductSupplierId " +
"INNER JOIN[Suppliers] s " +
"ON s.SupplierId = ps.SupplierId " +
"WHERE ps.SupplierId = @SupplierID";
SqlCommand cmdDelete = new SqlCommand(sql, dbConn);
cmdDelete.Parameters.AddWithValue("@SupplierID", s.SupplierId);
try
{
dbConn.Open();
cmdDelete.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
//search through suppliers given search string, returns list of suppliers
public static List <Supplier> GetSuppliersByName(string suppName)
{
List <Supplier> suppliers = new List <Supplier>();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"SELECT SupplierId, SupName " +
"FROM Suppliers " +
"WHERE SupName LIKE '%" + suppName + "%' ";
SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
Supplier supplier = new Supplier();
supplier.SupplierId = Convert.ToInt32(dbReader["SupplierId"]);
supplier.SupName = Convert.ToString(dbReader["SupName"]);
suppliers.Add(supplier);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
return(suppliers);
}
//receives supplier object and deletes supplier contact record
public static void DeleteProductSuppliersBySupplier(Supplier s)
{
DeletePackageProductSuppliersBySupplier(s);// Delete links first
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"DELETE FROM Products_Suppliers " +
"WHERE SupplierId = @SupplierID";
SqlCommand cmdDelete = new SqlCommand(sql, dbConn);
cmdDelete.Parameters.AddWithValue("@SupplierID", s.SupplierId);
try
{
dbConn.Open();
cmdDelete.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
//receives supplier object, deletes supplier record
public static void DeleteSupplier(Supplier ds)
{
DeleteSupplierContactsBySupplier(ds);
DeleteProductSuppliersBySupplier(ds); // Delete links first
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"DELETE FROM Suppliers " +
"WHERE SupplierID = @SupplierID " +
" AND SupName = @SupName"; //concurrency checking
SqlCommand cmdDelete = new SqlCommand(sql, dbConn);
cmdDelete.Parameters.AddWithValue("@SupplierID", ds.SupplierId);
cmdDelete.Parameters.AddWithValue("@SupName", ds.SupName);
try
{
dbConn.Open();
cmdDelete.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
//receives old and new supplier object, updates a supplier record
public static void UpdateSupplier(Supplier old_Supplier, Supplier new_Supplier)
{
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql = "UPDATE Suppliers SET " +
" SupName = @NewSupName " +
"WHERE SupplierID = @SupplierID " +
" AND SupName = @OldSupName"; //concurrency checking
SqlCommand cmdUpdate = new SqlCommand(sql, dbConn);
cmdUpdate.Parameters.AddWithValue("@NewSupName", new_Supplier.SupName);
cmdUpdate.Parameters.AddWithValue("@SupplierID", new_Supplier.SupplierId);
cmdUpdate.Parameters.AddWithValue("@OldSupName", old_Supplier.SupName);
try
{
dbConn.Open();
cmdUpdate.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
//find supplier by name returns supplier object
public static Supplier GetSupplierByName(string n)
{
Supplier supplier = new Supplier();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"SELECT SupplierId, SupName " +
"FROM Suppliers " +
"WHERE SupName = @SupName";
SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
cmdSelect.Parameters.AddWithValue("@SupName", n);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
supplier.SupplierId = Convert.ToInt32(dbReader["SupplierId"]);
supplier.SupName = Convert.ToString(dbReader["SupName"]);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
return(supplier);
}
//find and return a list of suppliers that is not in the Packages_Products_Suppliers table for the given package and the given product
public static List <Supplier> GetSuppliersByProductNotInDetails(Product p, Package pkg)
{
if (p == null)
{
//throw new Exception("null passed to supplier");
return(null);
}
else
{
List <Supplier> suppliers = new List <Supplier>();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"SELECT s.SupplierId, s.SupName " +
"FROM Suppliers s " +
"inner join Products_Suppliers ps " +
"on ps.SupplierId = s.SupplierId " +
"WHERE ps.ProductId = @ProductId " +
"and s.SupplierId " +
"not in " +
"(SELECT s.SupplierId " +
"FROM Packages pk " +
"inner join Packages_Products_Suppliers pps " +
"ON pps.PackageId = pk.PackageId " +
"inner join Products_Suppliers ps " +
"ON pps.ProductSupplierId = ps.ProductSupplierId " +
"inner join Products p " +
"ON p.ProductId = ps.ProductId " +
"inner join Suppliers s " +
"ON s.SupplierId = ps.SupplierId " +
"WHERE pk.PackageId = @PackageId " +
"and p.ProductId = @ProductId)";
SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
cmdSelect.Parameters.AddWithValue("@PackageId", pkg.PackageId);
cmdSelect.Parameters.AddWithValue("@ProductId", p.ProductId);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
Supplier supplier = new Supplier();
supplier.SupplierId = Convert.ToInt32(dbReader["SupplierId"]);
supplier.SupName = Convert.ToString(dbReader["SupName"]);
suppliers.Add(supplier);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
return(suppliers);
}
}
//return a list of PackageProductSupplier data for the given package
public static List <PackageProductSupplier> GetProductSuppliersByPackage(Package pkg)
{
if (pkg == null)
{
return(null);
}
else
{
List <PackageProductSupplier> ppss = new List <PackageProductSupplier>();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"SELECT pk.PackageId, pk.PkgName, pps.ProductSupplierId, p.ProdName, s.SupName " +
"FROM Packages pk " +
"inner join Packages_Products_Suppliers pps " +
"ON pps.PackageId = pk.PackageId " +
"inner join Products_Suppliers ps " +
"ON pps.ProductSupplierId = ps.ProductSupplierId " +
"inner join Products p " +
"ON p.ProductId = ps.ProductId " +
"inner join Suppliers s " +
"ON s.SupplierId = ps.SupplierId " +
"WHERE pk.PackageId = @Package " +
"ORDER BY p.ProdName";
SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
cmdSelect.Parameters.AddWithValue("@Package", pkg.PackageId);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
PackageProductSupplier pps = new PackageProductSupplier();
pps.PackageId = Convert.ToInt32(dbReader["PackageId"]);
pps.ProductSupplierId = Convert.ToInt32(dbReader["ProductSupplierId"]);
pps.PkgName = Convert.ToString(dbReader["PkgName"]);
pps.ProdName = Convert.ToString(dbReader["ProdName"]);
pps.SupName = Convert.ToString(dbReader["SupName"]);
ppss.Add(pps);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
return(ppss);
}
}
//find and return a list of prodcuts that is not in the Packages_Products_Suppliers table for the given package
public static List <Product> GetProductsNotInDetails(Package pkg)
{
List <Product> products = new List <Product>();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"SELECT[ProductId], [ProdName] " +
"FROM[Products] " +
"where ProductId " +
"not in " +
"(SELECT p.ProductId " +
"FROM Packages pk " +
"inner join Packages_Products_Suppliers pps " +
"ON pps.PackageId = pk.PackageId " +
"inner join Products_Suppliers ps " +
"ON pps.ProductSupplierId = ps.ProductSupplierId " +
"inner join Products p " +
"ON p.ProductId = ps.ProductId " +
"inner join Suppliers s " +
"ON s.SupplierId = ps.SupplierId " +
"WHERE pk.PackageId = @PackageId)";
SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
cmdSelect.Parameters.AddWithValue("@PackageId", pkg.PackageId);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
Product product = new Product();
product.ProductId = Convert.ToInt32(dbReader["ProductId"]);
product.ProdName = Convert.ToString(dbReader["ProdName"]);
products.Add(product);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
return(products);
}
//receives supplier object, returns corresponding list of products
public static List <Product> GetProductSuppliersBySupplier(Supplier s)
{
if (s == null)
{
//throw new Exception("null passed to supplier");
return(null);
}
else
{
List <Product> products = new List <Product>();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"SELECT p.ProductId, p.ProdName " +
"FROM Products p " +
"inner join Products_Suppliers ps " +
"ON p.ProductId = ps.ProductId " +
"inner join Suppliers s " +
"ON s.SupplierId = ps.SupplierId " +
"WHERE s.SupplierId = @SupplierID ";
SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
cmdSelect.Parameters.AddWithValue("@SupplierID", s.SupplierId);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
Product product = new Product();
product.ProductId = Convert.ToInt32(dbReader["ProductId"]);
product.ProdName = Convert.ToString(dbReader["ProdName"]);
products.Add(product);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
return(products);
}
}
public static List <Supplier> GetProductSuppliersByProduct(Product p)
{
if (p == null)
{
return(null);
}
else
{//call the suppliers list
List <Supplier> suppliers = new List <Supplier>();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"SELECT s.SupplierId, s.SupName " +
"FROM Suppliers s " +
"inner join Products_Suppliers ps " +
"ON s.SupplierId = ps.SupplierId " +
"inner join Products p " +
"ON p.ProductId = ps.ProductId " +
"WHERE p.ProductId = @ProductId";//get teh suppliers that carry the product by id
SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
cmdSelect.Parameters.AddWithValue("@ProductId", p.ProductId);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
Supplier supplier = new Supplier();
supplier.SupplierId = Convert.ToInt32(dbReader["SupplierId"]);
supplier.SupName = Convert.ToString(dbReader["SupName"]);
suppliers.Add(supplier);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
return(suppliers);
}
}
//receives product and returns a list of suppliers that are not in the current dgv
public static List <Supplier> GetSuppliersNotInList(Product p)
{
List <Supplier> suppliers = new List <Supplier>();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"SELECT [SupplierId], [SupName] " +
"FROM[Suppliers] s " +
"where s.SupplierId " +
"not in" +
"(SELECT s.SupplierId " +
"FROM Suppliers " +
"inner join Products_Suppliers ps " +
"ON s.SupplierId = ps.SupplierId " +
"inner join Products p " +
"on p.ProductId = ps.ProductId " +
"where p.ProductId = @ProductId)";
SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
cmdSelect.Parameters.AddWithValue("@ProductId", p.ProductId);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
Supplier supplier = new Supplier();
supplier.SupplierId = Convert.ToInt32(dbReader["SupplierId"]);
supplier.SupName = Convert.ToString(dbReader["SupName"]);
suppliers.Add(supplier);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
return(suppliers);
}
public static List <Product> GetProductsNotInList(Supplier s)
{//load products from database
List <Product> products = new List <Product>();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"SELECT [ProductId], [ProdName] " +
"FROM[Products] p " +
"where p.ProductId " +
"not in " +
"(SELECT p.ProductId " +
"FROM Products " +
"inner join Products_Suppliers ps " +
"ON p.ProductId = ps.ProductId " +
"inner join Suppliers s " +
"ON s.SupplierId = ps.SupplierId " +
"WHERE s.SupplierId = @SupplierId)";//find a product that doesn't have a supplier
SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
cmdSelect.Parameters.AddWithValue("@SupplierId", s.SupplierId);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
Product product = new Product();
product.ProductId = Convert.ToInt32(dbReader["ProductId"]);
product.ProdName = Convert.ToString(dbReader["ProdName"]);
products.Add(product);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
return(products);
}
//delete the package record from package table
public static void DeletePackage(Package dp)
{
DeletePackagesProductsSuppliersByPackage(dp); // Delete links first
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"DELETE FROM Packages " +
"WHERE PackageId = @PackageId " +
"AND PkgName = @oPkgName " + //check concurrency
"AND (PkgStartDate = @oPkgStartDate OR " +
"PkgStartDate IS NULL AND @oPkgStartDate IS NULL) " +
"AND (PkgEndDate = @oPkgEndDate OR " +
"PkgEndDate IS NULL AND @oPkgEndDate IS NULL) " +
"AND (PkgDesc = @oPkgDesc OR " +
"PkgDesc IS NULL AND @oPkgDesc IS NULL) " +
"AND PkgBasePrice = @oPkgBasePrice " +
"AND (PkgAgencyCommission = @oPkgAgencyCommission OR " +
"PkgAgencyCommission IS NULL AND @oPkgAgencyCommission IS NULL)";
SqlCommand cmdDelete = new SqlCommand(sql, dbConn);
cmdDelete.Parameters.AddWithValue("@PackageId", dp.PackageId);
cmdDelete.Parameters.AddWithValue("@oPkgName", dp.PkgName);
cmdDelete.Parameters.AddWithValue("@oPkgStartDate", (object)dp.PkgStartDate ?? DBNull.Value);
cmdDelete.Parameters.AddWithValue("@oPkgEndDate", (object)dp.PkgEndDate ?? DBNull.Value);
cmdDelete.Parameters.AddWithValue("@oPkgDesc", (object)dp.PkgDesc ?? DBNull.Value);
cmdDelete.Parameters.AddWithValue("@oPkgBasePrice", dp.PkgBasePrice);
cmdDelete.Parameters.AddWithValue("@oPkgAgencyCommission", (object)dp.PkgAgencyCommission ?? DBNull.Value);
try
{
dbConn.Open();
cmdDelete.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
//receives supplier object and inserts record into supplier table
public static void InsertSupplier(Supplier ns)
{
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql = "INSERT Suppliers (SupName) " +
"VALUES(@SupName)";
SqlCommand cmdInsert = new SqlCommand(sql, dbConn);
cmdInsert.Parameters.AddWithValue("@SupName", ns.SupName);
try
{
dbConn.Open();
cmdInsert.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
public static void InsertProduct(Product np)
{//load all products by name from database
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql = "INSERT Products (ProdName) " +
"VALUES (@ProdName)";
SqlCommand cmdInsert = new SqlCommand(sql, dbConn);//insert values
cmdInsert.Parameters.AddWithValue("@ProdName", np.ProdName);
try
{
dbConn.Open();
cmdInsert.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
//return a list of all package data from packages table in the database
public static List <Package> GetAllPackages()
{
List <Package> packages = new List <Package>();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages " +
"ORDER BY PackageId";
SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
Package package = new Package();
package.PackageId = Convert.ToInt32(dbReader["PackageId"]);
package.PkgName = Convert.ToString(dbReader["PkgName"]);
package.PkgStartDate = dbReader["PkgStartDate"] == DBNull.Value ? null : (DateTime?)dbReader["PkgStartDate"];
package.PkgEndDate = dbReader["PkgEndDate"] == DBNull.Value ? null : (DateTime?)(dbReader["PkgEndDate"]);
package.PkgDesc = Convert.ToString(dbReader["PkgDesc"] == DBNull.Value ? null : dbReader["PkgDesc"]);
package.PkgBasePrice = Convert.ToDecimal(dbReader["PkgBasePrice"]);
package.PkgAgencyCommission = dbReader["PkgAgencyCommission"] == DBNull.Value ? null : (decimal?)dbReader["PkgAgencyCommission"];
packages.Add(package);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
return(packages);
}
public static void InsertProductSupplier(ProductSupplier nps)
{
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql = "INSERT Products_Suppliers (ProductId,SupplierId) " +
"VALUES (@npsPid, @npsSid)";
SqlCommand cmdInsert = new SqlCommand(sql, dbConn); //get connection
cmdInsert.Parameters.AddWithValue("@npsPid", nps.ProductId); //assign value with parameter
cmdInsert.Parameters.AddWithValue("@npsSid", nps.SupplierId); //assign value with parameter
try
{
dbConn.Open(); //connection open
cmdInsert.ExecuteNonQuery(); //execute query
}
catch (SqlException ex)
{
throw ex;//throw exception
}
finally
{
dbConn.Close();//close connection
}
}
//delete data from Packages_Products_Suppliers for the given package
public static void DeletePackagesProductsSuppliersByPackage(Package p)
{
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"DELETE FROM Packages_Products_Suppliers " +
"WHERE PackageId = @PackageId";
SqlCommand cmdDelete = new SqlCommand(sql, dbConn);
cmdDelete.Parameters.AddWithValue("@PackageId", p.PackageId);
try
{
dbConn.Open();
cmdDelete.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
}
//find package by the given id
public static Package GetPackageById(int id)
{
Package package = new Package();
SqlConnection dbConn = TravelExpertsDB.GetConnection();
string sql =
"SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages " +
"WHERE PackageId = @ID ";
SqlCommand cmdSelect = new SqlCommand(sql, dbConn);
cmdSelect.Parameters.AddWithValue("@ID", id);
try
{
dbConn.Open();
SqlDataReader dbReader = cmdSelect.ExecuteReader();
while (dbReader.Read())
{
package.PackageId = Convert.ToInt32(dbReader["PackageId"]);
package.PkgName = Convert.ToString(dbReader["PkgName"]);
package.PkgStartDate = dbReader["PkgStartDate"] == DBNull.Value ? null : (DateTime?)dbReader["PkgStartDate"];
package.PkgEndDate = dbReader["PkgEndDate"] == DBNull.Value ? null : (DateTime?)(dbReader["PkgEndDate"]);
package.PkgDesc = Convert.ToString(dbReader["PkgDesc"] == DBNull.Value ? null : dbReader["PkgDesc"]);
package.PkgBasePrice = Convert.ToDecimal(dbReader["PkgBasePrice"]);
package.PkgAgencyCommission = dbReader["PkgAgencyCommission"] == DBNull.Value ? null : (decimal?)dbReader["PkgAgencyCommission"];
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dbConn.Close();
}
return(package);
}
