//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); }