/// <summary> /// Jorge: List of product-supplier ids for combobox /// </summary> /// <returns></returns> public static List <int> GetProductSupplierIds() { List <int> productsupplierIds = new List <int>(); // Empty Prod Supplier int prodSupId; using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT ProductSupplierId " + "FROM Products_Suppliers " + "ORDER BY ProductSupplierId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { // run command and process data connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) // while there is data { prodSupId = Convert.ToInt32(dr["ProductSupplierId"]); productsupplierIds.Add(prodSupId); } } } } return(productsupplierIds); }
/// <summary> /// Katrina Spencer: Retrieves list of products-suppliers /// </summary> /// <returns>products-suppliers</returns> public static List <ProductSupplier> GetAllProductsSuppliers() { List <ProductSupplier> prodsSups = new List <ProductSupplier>(); // empty ProductSupplier prodSup; // connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT ProductSupplierId, ProductId, SupplierId " + "FROM Products_Suppliers " + "ORDER BY ProductSupplierId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { // run command and process data connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) // while there is data { prodSup = new ProductSupplier(); prodSup.ProductSupplierId = (int)dr["ProductSupplierId"]; prodSup.ProductId = (int)dr["ProductId"]; prodSup.SupplierId = (int)dr["ProdName"]; prodsSups.Add(prodSup); } } } } return(prodsSups); }
/// <summary> /// Katrina Spencer: Updates supplier info /// </summary> /// <param name="oldSup">old supplier info</param> /// <param name="newSup">new supplier info</param> /// <returns>success indicator</returns> public static bool UpdateSupplier(Supplier oldSup, Supplier newSup) { bool result = false; // no success yet using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string updateStatement = "UPDATE Suppliers " + "SET SupName = @NewSupName " + "WHERE SupplierId = @OldSupplierId " + // identifies supplier "AND (SupName = @OldSupName " + "OR SupName IS NULL " + "AND @OldSupName IS NULL)"; using (SqlCommand cmd = new SqlCommand(updateStatement, connection)) { if (newSup.SupName == null) // if new SupName is null { cmd.Parameters.AddWithValue("@NewSupName", DBNull.Value); // set DBNull } else { cmd.Parameters.AddWithValue("NewSupName", (string)newSup.SupName); } cmd.Parameters.AddWithValue("@OldSupplierId", oldSup.SupplierId); if (oldSup.SupName == null) // if old SupName is null { cmd.Parameters.AddWithValue("@OldSupName", DBNull.Value); // set DBNull } else { cmd.Parameters.AddWithValue("@OldSupName", (string)oldSup.SupName); } // open connection connection.Open(); // execute UPDATE command int count = cmd.ExecuteNonQuery(); if (count > 0) // if row(s) affected { result = true; } } } return(result); }
/// <summary> /// Katrina: Returns a list of new product suppliers that are not yet within the selected package /// </summary> /// <param name="pkgId"></param> /// <returns>list of new prod sups</returns> public static List <PackageProductSupplier> GetNewProductsSuppliers(int pkgId) { List <PackageProductSupplier> prodsSups = new List <PackageProductSupplier>(); // empty PackageProductSupplier prodSup; // connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT DISTINCT ps.ProductSupplierId, ps.ProductId, ps.SupplierId, ProdName, SupName " + "FROM Packages_Products_Suppliers AS pps " + "RIGHT JOIN Products_Suppliers AS ps " + "ON pps.ProductSupplierId = ps.ProductSupplierId " + "JOIN Products ON ps.ProductId = Products.ProductId " + "JOIN Suppliers ON ps.SupplierId = Suppliers.SupplierId " + "WHERE ps.ProductSupplierId NOT IN " + "(SELECT ps.ProductSupplierId " + "FROM Products_Suppliers AS ps " + "JOIN Packages_Products_Suppliers AS pps " + "ON ps.ProductSupplierId = pps.ProductSupplierId " + "AND PackageId = @PackageId) " + "ORDER BY ps.ProductSupplierId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.AddWithValue("@PackageId", pkgId); // run command and process data connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) // while there is data { prodSup = new PackageProductSupplier(); prodSup.ProductSupplierId = (int)dr["ProductSupplierId"]; prodSup.ProductId = (int)dr["ProductId"]; prodSup.ProdName = (string)dr["ProdName"]; prodSup.SupplierId = (int)dr["SupplierId"]; prodSup.SupName = (string)dr["SupName"]; prodsSups.Add(prodSup); } } } } return(prodsSups); }
/// <summary> /// Katrina Spencer: Adds a new product /// </summary> /// <param name="prod">new product info</param> /// <returns>new product id</returns> public static int AddProduct(Product prod) { int prodId = 0; using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string insertStatement = "INSERT INTO Products (ProdName) " + "OUTPUT INSERTED.ProductId " + // returns single value "VALUES(@ProdName)"; using (SqlCommand cmd = new SqlCommand(insertStatement, connection)) { cmd.Parameters.AddWithValue("@ProdName", prod.ProdName); // open connection connection.Open(); prodId = (int)cmd.ExecuteScalar(); // returns one value } } return(prodId); }
/// <summary> /// Katrina Spencer: Retrieves supplier info with given id /// </summary> /// <param name="supId">id of supplier to get</param> /// <returns>supplier object</returns> public static Supplier GetSupplier(int supId) { Supplier sup = null; // connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT SupplierId, SupName " + "FROM Suppliers " + "WHERE SupplierId = @SupplierId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.AddWithValue("@SupplierId", supId); // run command and process data connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { if (dr.Read()) // if data { sup = new Supplier(); sup.SupplierId = (int)dr["SupplierId"]; // determine if it is DBNull and set int col = dr.GetOrdinal("SupName"); // column number of SupName if (dr.IsDBNull(col)) // if reader contains DBNull in this column { sup.SupName = null; // make null in the object } else // if not null { sup.SupName = (string)(dr["SupName"]); } } } } } return(sup); }
/// <summary> /// Katrina Spencer: Retrieves list of products-suppliers with given packageid /// </summary> /// <param name="pkgId">id of package to get</param> /// <returns>products-suppliers of a package</returns> public static List <PackageProductSupplier> GetProductsSuppliersByPackageId(int pkgId) { List <PackageProductSupplier> ppsList = new List <PackageProductSupplier>(); // empty PackageProductSupplier pps; // connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT pps.ProductSupplierID, " + "ProdName + ', ' + SupName AS ProductSupplierName " + "FROM Packages_Products_Suppliers AS pps " + "JOIN Products_Suppliers AS ps " + "ON pps.ProductSupplierId = ps.ProductSupplierId " + "JOIN Products AS p " + "ON ps.ProductId = p.ProductId " + "JOIN Suppliers AS s " + "ON ps.SupplierId = s.SupplierId " + "WHERE PackageId = @PackageId " + "ORDER BY ProductSupplierId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.AddWithValue("@PackageId", pkgId); // run command and process data connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) // while there is data { pps = new PackageProductSupplier(); pps.ProductSupplierId = (int)dr["ProductSupplierId"]; pps.ProductSupplierName = (string)dr["ProductSupplierName"]; ppsList.Add(pps); } } } } return(ppsList); }
/// <summary> /// Returns a list of new product supplier ids that are not yet a part of the selected package /// </summary> /// <param name="pkgId"></param> /// <returns>list of prod sup ids</returns> public static List <int> GetNewProductSupplierIds(int pkgId) { List <int> prodSupIds = new List <int>(); // empty prod sup ids int prodSupId; using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT DISTINCT ps.ProductSupplierId " + "FROM Packages_Products_Suppliers AS pps " + "RIGHT JOIN Products_Suppliers AS ps " + "ON pps.ProductSupplierId = ps.ProductSupplierId " + "WHERE ps.ProductSupplierId NOT IN " + "(SELECT ps.ProductSupplierId " + "FROM Products_Suppliers AS ps " + "JOIN Packages_Products_Suppliers AS pps " + "ON ps.ProductSupplierId = pps.ProductSupplierId " + "AND PackageId = @PackageId) " + "ORDER BY ps.ProductSupplierId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.AddWithValue("@PackageId", (int)pkgId); // run command and process data connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) // while there is data { prodSupId = Convert.ToInt32(dr["ProductSupplierId"]); prodSupIds.Add(prodSupId); } } } } return(prodSupIds); }
/// <summary> /// Katrina Spencer: Adds new product-supplier /// </summary> /// <param name="prodSup">new product-supplier info</param> /// <returns>new product-supplier id</returns> public static int AddProductSupplier(ProductSupplier prodSup) { int prodSupId = 0; using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierId) " + "OUTPUT INSERTED.ProductSupplierId " + // returns single value "VALUES(@ProductId, @SupplierId)"; using (SqlCommand cmd = new SqlCommand(insertStatement, connection)) { if (prodSup.ProductId == null) { cmd.Parameters.AddWithValue("@ProductId", DBNull.Value); } else { cmd.Parameters.AddWithValue("@ProductId", (int)prodSup.ProductId); } if (prodSup.SupplierId == null) { cmd.Parameters.AddWithValue("@SupplierId", DBNull.Value); } else { cmd.Parameters.AddWithValue("@SupplierId", (int)prodSup.SupplierId); } // open connection connection.Open(); prodSupId = (int)cmd.ExecuteScalar(); // returns one value } } return(prodSupId); }
/// <summary> /// Katrina Spencer: Retrieves package info with given id /// </summary> /// <param name="pkgId">id of package to get</param> /// <returns>package object</returns> public static Package GetPackage(int pkgId) { Package pkg = null; // connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " + "FROM Packages " + "WHERE PackageId = @PackageId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.AddWithValue("@PackageId", pkgId); // run command and process data connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { if (dr.Read()) // if data { pkg = new Package(); pkg.PackageId = (int)dr["PackageId"]; pkg.PkgName = (string)dr["PkgName"]; pkg.PkgBasePrice = Convert.ToDecimal(dr["PkgBasePrice"]); // need to determine if it is DBNull and set int colSD = dr.GetOrdinal("PkgStartDate"); // column number of PkgStartDate if (dr.IsDBNull(colSD)) // if reader contains DBNull in this column { pkg.PkgStartDate = null; // make null in the object } else // if not null { pkg.PkgStartDate = (DateTime)dr["PkgStartDate"]; } int colED = dr.GetOrdinal("PkgEndDate"); // column number of PkgEndDate if (dr.IsDBNull(colED)) // if reader contains DBNull in this column { pkg.PkgEndDate = null; // make null in the object } else // if not null { pkg.PkgEndDate = (DateTime)dr["PkgEndDate"]; } int colDesc = dr.GetOrdinal("PkgDesc"); // column number of PkgDesc if (dr.IsDBNull(colDesc)) // if reader contains DBNull in this column { pkg.PkgDesc = null; // make null in the object } else // if not null { pkg.PkgDesc = (string)dr["PkgDesc"]; } int colAC = dr.GetOrdinal("PkgAgencyCommission"); // column number of PkgAgencyCommission if (dr.IsDBNull(colAC)) // if reader contains DBNull in this column { pkg.PkgAgencyCommission = null; // make null in the object } else // if not null { pkg.PkgAgencyCommission = Convert.ToDecimal(dr["PkgAgencyCommission"]); } } } } } return(pkg); }
/// <summary> /// Angelito: Updates package info /// </summary> /// <param name="oldPkg">old package info</param> /// <param name="newPkg">new package info</param> /// <returns>success indicator</returns> public static bool UpdatePackage(Package oldPkg, Package newPkg) { // added consideration for Null values -Katrina bool result = false; // no success yet using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string updateStatement = "UPDATE Packages SET " + "PkgName = @NewPkgName, " + "PkgStartDate = @NewPkgStartDate, " + "PkgEndDate = @NewPkgEndDate, " + "PkgDesc = @NewPkgDesc, " + "PkgBasePrice = @NewPkgBasePrice, " + "PkgAgencyCommission = @NewPkgAgencyCommission " + "WHERE PackageId = @OldPackageId " + // identifies package "AND PkgName = @OldPkgName " + // PkgStartDate Null option -Katrina "AND (PkgStartDate = @OldPkgStartDate " + "OR PkgStartDate IS NULL " + "AND @OldPkgStartDate IS NULL) " + // PkgEndDate Null option -Katrina "AND (PkgEndDate = @OldPkgEndDate " + "OR PkgEndDate IS NULL " + "AND @OldPkgEndDate IS NULL) " + // PkgDesc Null option -Katrina "AND (PkgDesc = @OldPkgDesc " + "OR PkgDesc IS NULL " + "AND @OldPkgDesc IS NULL) " + "AND PkgBasePrice = @OldPkgBasePrice " + // PkgAgencyCommission Null option -Katrina "AND (PkgAgencyCommission = @OldPkgAgencyCommission " + "OR PkgAgencyCommission IS NULL " + "AND @OldPkgAgencyCommission IS NULL)"; using (SqlCommand cmd = new SqlCommand(updateStatement, connection)) { // added consideration for Null values -Katrina cmd.Parameters.AddWithValue("@NewPkgName", newPkg.PkgName); if (newPkg.PkgStartDate == null) // if new PkgStartDate is null { cmd.Parameters.AddWithValue("@NewPkgStartDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@NewPkgStartDate", (DateTime)newPkg.PkgStartDate); } if (newPkg.PkgEndDate == null) // if new PkgEndDate is null { cmd.Parameters.AddWithValue("@NewPkgEndDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@NewPkgEndDate", (DateTime)newPkg.PkgEndDate); } // PkgDesc and PkgBasePrice can't be null when modifying cmd.Parameters.AddWithValue("@NewPkgDesc", newPkg.PkgDesc); cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPkg.PkgBasePrice); if (newPkg.PkgAgencyCommission == null) // if new PkgAgencyCommission is null { cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", DBNull.Value); } else { cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", (decimal)newPkg.PkgAgencyCommission); } cmd.Parameters.AddWithValue("@OldPackageId", oldPkg.PackageId); cmd.Parameters.AddWithValue("@OldPkgName", oldPkg.PkgName); if (oldPkg.PkgStartDate == null) // if old PkgStartDate is null { cmd.Parameters.AddWithValue("@OldPkgStartDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldPkgStartDate", (DateTime)oldPkg.PkgStartDate); } if (oldPkg.PkgEndDate == null) // if old PkgEndDate is null { cmd.Parameters.AddWithValue("@OldPkgEndDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldPkgEndDate", (DateTime)oldPkg.PkgEndDate); } if (oldPkg.PkgDesc == null) // if old PkgDesc is null { cmd.Parameters.AddWithValue("@OldPkgDesc", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldPkgDesc", (string)oldPkg.PkgDesc); } cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPkg.PkgBasePrice); if (oldPkg.PkgAgencyCommission == null) // if old PkgAgencyCommission is null { cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", (decimal)oldPkg.PkgAgencyCommission); } // open connection connection.Open(); // execute UPDATE command int count = cmd.ExecuteNonQuery(); if (count > 0) // if row(s) affected { result = true; } } } return(result); }
/// <summary> /// Angelito: Adds a new package /// </summary> /// <param name="pkg">new package info</param> /// <returns>new package id</returns> public static int AddPackage(Package pkg) { int pkgId = 0; using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string insertStatement = "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " + "OUTPUT INSERTED.PackageId " + "VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)"; using (SqlCommand cmd = new SqlCommand(insertStatement, connection)) { // Added consideration for Null values -Katrina cmd.Parameters.AddWithValue("@PkgName", pkg.PkgName); Console.WriteLine("Package name in databse to be inserted " + pkg.PkgName); if (pkg.PkgStartDate == null) { cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@PkgStartDate", (DateTime)pkg.PkgStartDate); } if (pkg.PkgEndDate == null) { cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@PkgEndDate", (DateTime)pkg.PkgEndDate); } if (pkg.PkgDesc == null) { cmd.Parameters.AddWithValue("@PkgDesc", DBNull.Value); } else { cmd.Parameters.AddWithValue("@PkgDesc", (string)pkg.PkgDesc); } cmd.Parameters.AddWithValue("@PkgBasePrice", pkg.PkgBasePrice); if (pkg.PkgAgencyCommission == null) { cmd.Parameters.AddWithValue("@PkgAgencyCommission", DBNull.Value); } else { cmd.Parameters.AddWithValue("@PkgAgencyCommission", (decimal)pkg.PkgAgencyCommission); } // open connection connection.Open(); pkgId = (int)cmd.ExecuteScalar(); // returns one value } } return(pkgId); }
/// <summary> /// Katrina Spencer: Retrieves product-supplier info with given id /// </summary> /// <param name="prodSupId">id of product-supplier to get</param> /// <returns>product-supplier object</returns> public static ProductSupplier GetProductSupplier(int prodSupId) { ProductSupplier prodSup = null; // connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT ProductSupplierId, ps.ProductId, ProdName, ps.SupplierId, SupName " + "FROM Products_Suppliers AS ps " + "JOIN Products ON ps.ProductId = Products.ProductId " + "JOIN Suppliers ON ps.SupplierId = Suppliers.SupplierId " + "WHERE ProductSupplierId = @ProductSupplierId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.AddWithValue("@ProductSupplierId", prodSupId); // run command and process data connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { if (dr.Read()) // if data { prodSup = new ProductSupplier(); prodSup.ProductSupplierId = (int)dr["ProductSupplierId"]; // determine if it is DBNull and set int colProd = dr.GetOrdinal("ProductId"); // column number of ProductId if (dr.IsDBNull(colProd)) // if reader contains DBNull in this column { prodSup.ProductId = null; // make null in the object } else // if not null { prodSup.ProductId = (int)(dr["ProductId"]); } int colSup = dr.GetOrdinal("SupplierId"); // column number of SupplierId if (dr.IsDBNull(colSup)) // if reader contains DBNull in this column { prodSup.SupplierId = null; // make null in the object } else // if not null { prodSup.SupplierId = (int)(dr["SupplierId"]); } int colProdName = dr.GetOrdinal("ProdName"); // column number of ProdName if (dr.IsDBNull(colProdName)) // if reader contains DBNull in this column { prodSup.ProdName = null; // make null in the object } else // if not null { prodSup.ProdName = (string)(dr["ProdName"]); } int colSupName = dr.GetOrdinal("SupName"); // column number of SupName if (dr.IsDBNull(colSupName)) // if reader contains DBNull in this column { prodSup.SupName = null; // make null in the object } else // if not null { prodSup.SupName = (string)(dr["SupName"]); } } } } } return(prodSup); }