//Author: Joel--------------------------------------------------------------------- public static ProductSupplier GetProductSupplier(int productID, int supplierID) { ProductSupplier prodSup = null; SqlConnection con = TravelExpertsDB.GetConnection(); string SelectSmt = "SELECT ProductSupplierId, ProductId, SupplierId " + "FROM Products_Suppliers " + "WHERE ProductId = @pID AND SupplierId = @sID " + "ORDER BY ProductSupplierId"; SqlCommand selectCmd = new SqlCommand(SelectSmt, con); selectCmd.Parameters.AddWithValue("@pID", productID); selectCmd.Parameters.AddWithValue("@sID", supplierID); try { con.Open(); SqlDataReader dr = selectCmd.ExecuteReader(System.Data.CommandBehavior.SingleResult); if (dr.Read()) { prodSup = new ProductSupplier(); prodSup.ProductSupplierId = (int)dr["ProductSupplierId"]; prodSup.ProductId = (int)dr["ProductId"]; prodSup.SupplierId = (int)dr["SupplierId"]; } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(prodSup); }
//Author: Lindsay--------------------------------------------------------------------- /// <summary> /// Adds a record to the Products_Suppliers table in Travel Experts database /// </summary> /// <param name="supProd"> ProductSupplier object that containg data for the new record</param> /// <returns>generated productSupplierId</returns> public static int AddSupProd(ProductSupplier supProd) { SqlConnection con = TravelExpertsDB.GetConnection(); string insertSmt = "INSERT INTO Products_Suppliers (SupplierId, ProductId) " + "VALUES(@SupplierId, @ProductId)"; SqlCommand cmd = new SqlCommand(insertSmt, con); cmd.Parameters.AddWithValue("@SupplierId", supProd.SupplierId); cmd.Parameters.AddWithValue("@ProductId", supProd.ProductId); try { con.Open(); cmd.ExecuteNonQuery(); // run the insert command //get the generated ID - current identity value of Products_Suppliers table string selectQuery = "SELECT IDENT_CURRENT('Products_Suppliers') From Products_Suppliers"; SqlCommand selectCmd = new SqlCommand(selectQuery, con); int productSupplierId = Convert.ToInt32(selectCmd.ExecuteScalar()); return(productSupplierId); } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
//Author: Lindsay----------------------------------------------------- //the method of getting all the suppliers data from the database public static List <ProductSupplier> GetProductSuppliers() { List <ProductSupplier> prodSups = new List <ProductSupplier>(); ProductSupplier prodSup; SqlConnection con = TravelExpertsDB.GetConnection(); string SelectSmt = "SELECT ProductSupplierId, ProductId, SupplierId " + "FROM Products_Suppliers " + "ORDER BY ProductSupplierId"; SqlCommand selectCmd = new SqlCommand(SelectSmt, con); try { con.Open(); SqlDataReader dr = selectCmd.ExecuteReader(); while (dr.Read()) { prodSup = new ProductSupplier(); prodSup.ProductSupplierId = (int)dr["ProductSupplierId"]; prodSup.ProductId = (int)dr["ProductId"]; prodSup.SupplierId = (int)dr["SupplierId"]; prodSups.Add(prodSup); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(prodSups); }
//Author: Lindsay----------------------------------------------------------------------------- /// <summary> /// Delete a record to the Products_Suppliers table in Travel Experts database /// </summary> /// <param name="supProd">ProductSupplier object that containg data for the new record</param> /// <returns>generated productSupplierId</returns> public static bool DeleteSupProd(ProductSupplier supProd) { SqlConnection con = TravelExpertsDB.GetConnection(); String deleteSmt = "DELETE FROM Products_Suppliers " + "WHERE SupplierId = @SupplierId " + "And ProductId = @ProductId"; SqlCommand cmd = new SqlCommand(deleteSmt, con); cmd.Parameters.AddWithValue("@SupplierId", supProd.SupplierId); cmd.Parameters.AddWithValue("@ProductId", supProd.ProductId); try { con.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
public static List <NamedPackageProductSupplier> GetPackageProductSuppliersByPackage(int pkgID) { List <NamedPackageProductSupplier> productSuppliers = new List <NamedPackageProductSupplier>(); SqlConnection conn = TravelExpertsDB.GetConnection(); string sqlQuery = "SELECT pkgps.PackageId as pkgID, ps.ProductSupplierId as psID, ProdName, SupName " + "FROM Products p " + "INNER JOIN Products_Suppliers ps ON p.ProductId = ps.ProductId " + "INNER JOIN Suppliers s ON s.SupplierId = ps.SupplierId " + "INNER JOIN Packages_Products_Suppliers pkgps ON pkgps.ProductSupplierId = ps.ProductSupplierId " + "WHERE pkgps.PackageID = @PackageID " + "ORDER BY pkgps.ProductSupplierId"; //string sqlQuery = "SELECT PackageID, ProductSupplierID FROM Packages_Products_Suppliers "+ // "WHERE PackageID = @PackageID ORDER BY ProductSupplierID"; SqlCommand cmd = new SqlCommand(sqlQuery, conn); cmd.Parameters.AddWithValue("@PackageId", pkgID); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { NamedPackageProductSupplier prodSup = new NamedPackageProductSupplier { PackageID = (int)reader["pkgID"], ProdSuppID = (int)reader["psID"], ProductName = reader["ProdName"].ToString(), SupplierName = reader["SupName"].ToString() }; productSuppliers.Add(prodSup); } } catch (Exception) { throw; } finally { conn.Close(); } return(productSuppliers); }
public static int Delete(TravelPackage package) { //Delete all related Package_Product_Suppliers foreach (PackageProdSupplier pps in package.ProductsAndSuppliers) { if (PackageProdSuppDB.Delete(pps) < 1) { throw new Exception("Error deleting Package Product/Supplier"); } } int deletedPkgs = 0; SqlConnection conn = TravelExpertsDB.GetConnection(); string sqlQuery = "DELETE FROM Packages " + "WHERE PackageId = @pkgID AND PkgName = @Name " + "AND PkgStartDate = @start AND PkgEndDate = @end " + "AND PkgDesc = @desc AND PkgBasePrice = @price " + "AND PkgAgencyCommission = @commission"; SqlCommand cmd = new SqlCommand(sqlQuery, conn); //TODO: Account for null values cmd.Parameters.AddWithValue("@pkgID", package.ID); cmd.Parameters.AddWithValue("@name", package.Name); // cmd.Parameters.AddWithValue("@start", package.StartDate); //Nullable cmd.Parameters.AddWithValue("@start", package.StartDate == null ? (object)DBNull.Value : package.StartDate); cmd.Parameters.AddWithValue("@end", package.EndDate); //Nullable cmd.Parameters.AddWithValue("@desc", package.Description ?? (object)DBNull.Value); //Nullable cmd.Parameters.AddWithValue("@price", package.BasePrice); cmd.Parameters.AddWithValue("@commission", package.Commission ?? (object)DBNull.Value); //Nullable try { conn.Open(); deletedPkgs += cmd.ExecuteNonQuery(); } catch (Exception) { throw; } finally { conn.Close(); } return(deletedPkgs); }
public static void Update(TravelPackage oldPkg, TravelPackage newPkg) { if (oldPkg.ID != newPkg.ID) { throw new ArgumentException("ID mismatch between old package and new package"); } SqlConnection conn = TravelExpertsDB.GetConnection(); string updStmt = "UPDATE Packages " + "SET PkgName = @name, " + "PkgStartDate = @start, " + "PkgEndDate = @end, " + "PkgDesc = @desc, " + "PkgBasePrice = @price, " + "PkgAgencyCommission = @commiss " + "WHERE PackageId = @id"; SqlCommand cmd = new SqlCommand(updStmt, conn); cmd.Parameters.AddWithValue("@name", newPkg.Name); cmd.Parameters.AddWithValue("@desc", newPkg.Description ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@start", newPkg.StartDate); cmd.Parameters.AddWithValue("@end", newPkg.EndDate); cmd.Parameters.AddWithValue("@price", newPkg.BasePrice); cmd.Parameters.AddWithValue("@commiss", newPkg.Commission ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@id", oldPkg.ID); try { conn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("Error while updating database: " + ex.Message); } finally { conn.Close(); } }
public static List <Product> GetProducts() { List <Product> products = new List <Product>(); Product product; SqlConnection con = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT ProductId, ProdName " + "FROM Products ORDER BY ProductId"; SqlCommand selectCmd = new SqlCommand(selectStatement, con); try { con.Open(); SqlDataReader dr = selectCmd.ExecuteReader(); while (dr.Read()) { product = new Product(); product.ProductId = (int)dr["ProductId"]; product.ProdName = dr["ProdName"].ToString(); products.Add(product); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { con.Close(); } return(products); }
/// <summary> /// Adds a new supplier to the Suppliers table in Travel Experts database /// </summary> /// <param name="sup"> Supplier object that containg data for the new record</param> /// <returns>generated SupplierID</returns> public static void AddSupplier(Supplier sup) { SqlConnection con = TravelExpertsDB.GetConnection(); string insertSmt = "INSERT INTO Suppliers (Supplierid, SupName) " + "VALUES(@SupplierId, @SupName)"; SqlCommand cmd = new SqlCommand(insertSmt, con); cmd.Parameters.AddWithValue("@SupplierId", sup.SupplierId); cmd.Parameters.AddWithValue("@SupName", sup.SupName); try { con.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
/// <summary> /// Updates existing product record /// </summary> /// <param name="oldProd">data before update</param> /// <param name="newProd">new data for the update</param> /// <returns>indicator of success</returns> public static bool UpdateProduct(Product oldProd, Product newProd) { SqlConnection con = TravelExpertsDB.GetConnection(); string updateStatement = "UPDATE Products " + "SET ProdName = @NewProdName " + "WHERE ProductId = @OldProductId " + "AND ProdName = @OldProdName"; SqlCommand cmd = new SqlCommand(updateStatement, con); cmd.Parameters.AddWithValue("@NewProdName", newProd.ProdName); cmd.Parameters.AddWithValue("@OldProductId", oldProd.ProductId); cmd.Parameters.AddWithValue("@OldProdName", oldProd.ProdName); try { con.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { MessageBox.Show("Product Name updated successfully !"); return(true); } else { MessageBox.Show("Product Name is not updated successfully !"); return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
public static TravelPackage Insert(TravelPackage newPkg) { SqlConnection conn = TravelExpertsDB.GetConnection(); string columns = "PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission"; string values = "@name, @start, @end, @desc, @price, @commiss"; string insStmt = "INSERT INTO Packages (" + columns + ") VALUES (" + values + ")"; SqlCommand cmd = new SqlCommand(insStmt, conn); cmd.Parameters.AddWithValue("@name", newPkg.Name); cmd.Parameters.AddWithValue("@start", newPkg.StartDate); cmd.Parameters.AddWithValue("@end", newPkg.EndDate); cmd.Parameters.AddWithValue("@desc", newPkg.Description ?? (object)DBNull.Value); cmd.Parameters.AddWithValue("@price", newPkg.BasePrice); cmd.Parameters.AddWithValue("@commiss", newPkg.Commission ?? (object)DBNull.Value); try { conn.Open(); cmd.ExecuteNonQuery(); //Retreive package ID string selStmt = "SELECT IDENT_CURRENT('Packages') FROM Packages"; SqlCommand selCmd = new SqlCommand(selStmt, conn); newPkg.ID = Convert.ToInt32(selCmd.ExecuteScalar()); } catch (Exception ex) { MessageBox.Show("Error while inserting into database: " + ex.Message); newPkg.ID = -1; } finally { conn.Close(); } return(newPkg); }
//getting all the products not supplieried by the supplier public static List <Product> GetProdsUnsuppliedBySup(int supplierId) { List <Product> unsuppliedProds = new List <Product>(); Product unsupplied; SqlConnection con = TravelExpertsDB.GetConnection(); string SelectSmt = "SELECT ProductId, ProdName FROM Products " + "WHERE ProductId NOT IN " + "(SELECT ProductId FROM Products_Suppliers " + "WHERE SupplierId = @SupplierId) " + "ORDER BY ProductId"; SqlCommand selectCmd = new SqlCommand(SelectSmt, con); selectCmd.Parameters.AddWithValue("@SupplierId", supplierId); try { con.Open(); SqlDataReader dr = selectCmd.ExecuteReader(); while (dr.Read()) { unsupplied = new Product(); unsupplied.ProductId = (int)dr["ProductId"]; unsupplied.ProdName = dr["prodName"].ToString(); unsuppliedProds.Add(unsupplied); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(unsuppliedProds); }
//getting all the products supplieried by the supplier public static List <Product> GetProductsBySupplier(int supplierId) { List <Product> SuppliedProds = new List <Product>(); Product suppliedProd; SqlConnection con = TravelExpertsDB.GetConnection(); string SelectSmt = "SELECT SupplierId, p.ProductId, p.ProdName " + "FROM Products_Suppliers ps JOIN Products p " + "ON ps.ProductId = p.ProductId " + "WHERE SupplierId = @SupplierId " + "ORDER BY p.ProductId"; SqlCommand selectCmd = new SqlCommand(SelectSmt, con); selectCmd.Parameters.AddWithValue("@SupplierId", supplierId); try { con.Open(); SqlDataReader dr = selectCmd.ExecuteReader(); while (dr.Read())//while there is a product { suppliedProd = new Product(); suppliedProd.ProductId = (int)dr["ProductId"]; suppliedProd.ProdName = dr["prodName"].ToString(); SuppliedProds.Add(suppliedProd); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(SuppliedProds); }