public static int AddProduct(Product product) { int productID; //hold productID value (might not need this) SqlConnection con = TravelExpertsDB.GetConnection(); //connect to database //SQL string to insert new product, dont need ProductID as it is Auto increment string insertStatement = "INSERT INTO Products (ProdName) " + "VALUES (@ProdName)"; SqlCommand cmd = new SqlCommand(insertStatement, con); cmd.Parameters.AddWithValue("@ProdName", product.ProdName); //add value to parameter in sql string try { con.Open(); cmd.ExecuteNonQuery(); //execute the insert sql string string selectQuery = "SELECT IDENT_CURRENT ('Products')"; //might not need this code below as i might not need to return Prodcut ID SqlCommand selectCmd = new SqlCommand(selectQuery, con); productID = Convert.ToInt32(selectCmd.ExecuteScalar()); } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(productID); }
/// <summary> /// this is used to deleted a selected product by the user from the travel experts database /// </summary> /// <param name="product"></param> /// <returns> returns a true or false value, true if the count of the rows affected is greater than 1 meaning the row was deleted </returns> public static bool DeleteProduct(Product product) { SqlConnection con = TravelExpertsDB.GetConnection(); string deleteStatement = "DELETE FROM Products " + "WHERE ProductID = @ProductID " + "AND ProdName = @ProdName"; SqlCommand cmd = new SqlCommand(deleteStatement, con); cmd.Parameters.AddWithValue("@ProductID", product.ProductID); //product id of the user chosen value to be deleted cmd.Parameters.AddWithValue("@ProdName", product.ProdName); //product name of the user chosen value to be deleted try { con.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
/// <summary> /// user picks a product to be updated this methods is called and updates that product in the database /// </summary> /// <param name="oldProduct"></param> /// <param name="newProduct"></param> /// <returns> true or false value, true if count is greater than 1 meaning more than 1 row was affected</returns> public static bool UpdateProduct(Product oldProduct, Product newProduct) { 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", newProduct.ProdName); //new value entered in database //start adding values of old product cmd.Parameters.AddWithValue("@OldProductID", oldProduct.ProductID); cmd.Parameters.AddWithValue("@OldProdName", oldProduct.ProdName); //old name to be changed try { con.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
/// <summary> /// Delete a new supplier to the Suppliers table in TravelExperts database /// </summary> /// <param name="s"> supplier object that cotaing data for the new record</param> /// <returns>generated SupplierId</returns> public static bool DeleteSupplier(Supplier sup) { SqlConnection connection = TravelExpertsDB.GetConnection(); string deleteStatement = "DELETE FROM Suppliers " + "WHERE SupplierId = @SupplierId " + // to identify the supplier to be deleted "AND SupName = @SupName"; // remaining conditions - to ensure optimistic concurrency SqlCommand cmd = new SqlCommand(deleteStatement, connection); cmd.Parameters.AddWithValue("@SupplierId", sup.SupplierId); cmd.Parameters.AddWithValue("@SupName", sup.SupName); try { connection.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
/// <summary> /// Adds a new supplier to the Suppliers table in TravelExperts database /// </summary> /// <param name="s"> supplier object that cotaing data for the new record</param> /// <returns>generated SupplierId</returns> public static bool AddSupplier(Supplier supplier) { SqlConnection connection = TravelExpertsDB.GetConnection(); string insertStatement = "INSERT INTO Suppliers (SupplierId, SupName) " + "VALUES(@SupplierId, @SupName)"; SqlCommand cmd = new SqlCommand(insertStatement, connection); cmd.Parameters.AddWithValue("@SupplierId", supplier.SupplierId); cmd.Parameters.AddWithValue("@SupName", supplier.SupName); try { connection.Open(); int count = cmd.ExecuteNonQuery(); // run the insert command if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static List <Supplier> GetAllSuppliers() { List <Supplier> suppliers = new List <Supplier>(); //empty list Supplier s = null; SqlConnection connection = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT SupplierId, SupName " + "FROM Suppliers ORDER BY SupplierId"; SqlCommand cmd = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) // while there is another record { s = new Supplier(); s.SupplierId = (int)reader["SupplierId"]; s.SupName = reader["SupName"].ToString(); suppliers.Add(s); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(suppliers); }
//end roberts works /// <summary> /// Updates existing supplier record /// </summary> /// <param name="oldS">data before update</param> /// <param name="newS">new data for the update</param> /// <returns>indicator of success</returns> public static bool UpdateSupplier(Supplier oldSup, Supplier newSup) { SqlConnection connection = TravelExpertsDB.GetConnection(); string updateStatement = "UPDATE Suppliers " + "SET SupName = @NewSupName " + "WHERE SupplierId = @OldSupplierId " + "AND SupName = @OldSupName"; SqlCommand cmd = new SqlCommand(updateStatement, connection); cmd.Parameters.AddWithValue("NewSupName", newSup.SupName); cmd.Parameters.AddWithValue("@OldSupplierId", newSup.SupplierId); cmd.Parameters.AddWithValue("OldSupName", oldSup.SupName); try { connection.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool Delete(Packages_Products_Suppliers packProdSup, Package package) { SqlConnection con = TravelExpertsDB.GetConnection(); string insertStatement = "DELETE FROM Packages_Products_Suppliers " + "WHERE PackageID = @PackageID " + "AND ProductSupplierID = @ProductSupplierID"; SqlCommand cmd = new SqlCommand(insertStatement, con); cmd.Parameters.AddWithValue("@PackageID", package.PackageId); cmd.Parameters.AddWithValue("@ProductSupplierID", packProdSup.ProductSupplerID); try { con.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
/// <summary> /// function is used to add data to Product_Supplier table /// </summary> /// <param name="PS"></param> /// <returns> product supplier id</returns> public static int AddProduct_Supplier(Product_Supplier productSupplier) { int productSupplierID;//holds value of product supplier id SqlConnection con = TravelExpertsDB.GetConnection(); string insertStatement = "INSERT INTO Products_Suppliers (ProductID, SupplierID) " + "VALUES (@ProductID, @SupplierID)"; SqlCommand cmd = new SqlCommand(insertStatement, con); //values to be added cmd.Parameters.AddWithValue("@ProductID", productSupplier.ProductID); cmd.Parameters.AddWithValue("@SupplierID", productSupplier.SupplierID); try { con.Open(); cmd.ExecuteNonQuery(); //TODO: Might not need this code below string selectQuery = "SELECT IDENT_CURRENT ('Products_Suppliers')"; SqlCommand selectCmd = new SqlCommand(selectQuery, con); productSupplierID = Convert.ToInt32(selectCmd.ExecuteScalar()); // } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(productSupplierID); }
/// <summary> /// function is used to delete the data from Product_supplier table /// </summary> /// <param name="PS"></param> /// <returns> true or false value, true if count is greater than 1 menaing more than 1 row was affected </returns> public static bool DeleteProduct_Supplier(Product_Supplier productSupplier) { SqlConnection con = TravelExpertsDB.GetConnection(); string deleteStatement = "DELETE FROM Products_Suppliers WHERE ProductSupplierID = @ProductSupplierID " + "AND ProductID=@ProductID " + "AND SupplierID=@SupplierID"; SqlCommand cmd = new SqlCommand(deleteStatement, con); //values to be deleted cmd.Parameters.AddWithValue("@ProductSupplierID", productSupplier.ProductSupplierID); cmd.Parameters.AddWithValue("@ProductID", productSupplier.ProductID); cmd.Parameters.AddWithValue("@SupplierID", productSupplier.SupplierID); try { con.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
/// <summary> /// updates a package with new info user inputs /// </summary> /// <param name="oldPackage"></param> /// <param name="newPackage"></param> /// <returns> true or false, true if number of rows affected are greated than 0 menaing update was succesful</returns> public static bool UpdatePackages(Package oldPackage, Package newPackage) { SqlConnection con = TravelExpertsDB.GetConnection(); string updateStatement = "UPDATE Packages " + "SET PkgName = @NewPkgName, " + "PkgStartDate = @NewPkgStartDate, " + "PkgEndDate = @NewPkgEndDate, " + "PkgDesc = @NewPkgDesc, " + "PkgBasePrice = @NewPkgBasePrice, " + "PkgAgencyCommission = @NewPkgAgencyCommission " + "WHERE PackageID = @OldPackageID " + "AND PkgName = @OldPkgName " + "AND PkgStartDate = @OldPkgStartDate " + "AND PkgEndDate = @OldPkgEndDate " + "AND PkgDesc = @OldPkgDesc " + "AND PkgBasePrice = @OldPkgBasePrice " + "AND PkgAgencyCommission = @OldPkgAgencyCommission"; SqlCommand cmd = new SqlCommand(updateStatement, con); 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); //start adding values of old package 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); try { con.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
/// <summary> /// used to deleted a user selected packagefrom the database /// </summary> /// <param name="package"></param> /// <returns> true or false, true if count is greater than 1 menaing more than one row in the database was affected</returns> public static bool DeletePackage(Package package) { SqlConnection con = TravelExpertsDB.GetConnection(); string deleteStatement = "Delete FROM Packages " + "WHERE PackageId = @PackageId " + "AND PkgName = @PkgName " + "AND PkgStartDate = @PkgStartDate " + "AND PkgEndDate = @PkgEndDate " + "AND PkgDesc = @PkgDesc " + "AND PkgBasePrice = @PkgBasePrice " + "AND PkgAgencyCommission = @PkgAgencyCommission"; SqlCommand cmd = new SqlCommand(deleteStatement, con); //add values to use to find the right value to delete 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); try { con.Open(); int count = cmd.ExecuteNonQuery();// returns int for rows affected if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
/// <summary> /// function update the product supplier table /// </summary> /// <param name="oldProduct_Supplier"></param> /// <param name="newProduct_Supplier"></param> /// <returns> true or false value, true if count is greater than 1 menaing more than 1 row was affected</returns> public static bool UpdateProduct_Supplier(Product_Supplier oldProduct_Supplier, Product_Supplier newProduct_Supplier) { SqlConnection con = TravelExpertsDB.GetConnection(); string updateStatement = "UPDATE Products_Suppliers " + "SET ProductID = @newProductID, " + "SupplierID = @newSupplierID " + "WHERE ProductSupplierID = @oldProductSupplierID " + "AND ProductID = @oldProductID " + "AND SupplierID = @oldSupplierID "; SqlCommand cmd = new SqlCommand(updateStatement, con); //new value to be entered cmd.Parameters.AddWithValue("@newProductID", newProduct_Supplier.ProductID); cmd.Parameters.AddWithValue("@newSupplierID", newProduct_Supplier.SupplierID); //start adding old product supplier values cmd.Parameters.AddWithValue("@oldProductSupplierID", oldProduct_Supplier.ProductSupplierID); cmd.Parameters.AddWithValue("@oldProductID", oldProduct_Supplier.ProductID); cmd.Parameters.AddWithValue("@oldSupplierID", oldProduct_Supplier.SupplierID); 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 int GetNextSupplierID() { int supplierID; //holds the supplier id value SqlConnection con = TravelExpertsDB.GetConnection(); //calls connection method string selectQuery = "SELECT MAX(SupplierId)+1 FROM Suppliers"; //gets max current supplier id and adds 1 SqlCommand selectCmd = new SqlCommand(selectQuery, con); try { con.Open(); // open connection supplierID = Convert.ToInt32(selectCmd.ExecuteScalar()); return(supplierID); } catch (SqlException ex) { throw ex; } finally { con.Close(); // close connection } }
/// <summary> /// gets the value of the new auto generated package id /// </summary> /// <returns> new supplier id</returns> public static int GetNextPackageID() { int supplierID;//supplier id auto generated SqlConnection con = TravelExpertsDB.GetConnection(); string selectQuery = "SELECT IDENT_CURRENT('Packages') + IDENT_INCR('Packages')"; SqlCommand selectCmd = new SqlCommand(selectQuery, con); try { con.Open(); supplierID = Convert.ToInt32(selectCmd.ExecuteScalar()); return(supplierID); } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
/// <summary> /// gets the next product id from the product id column /// </summary> /// <returns>returns the value of the product id</returns> public static int GetNextProductID() { int productId; //holds value of product id from database SqlConnection con = TravelExpertsDB.GetConnection(); string selectQuery = "SELECT IDENT_CURRENT('Products') + IDENT_INCR('Products')"; //gets the current identity for the products table and then adds the identity increment value SqlCommand selectCmd = new SqlCommand(selectQuery, con); try { con.Open(); productId = Convert.ToInt32(selectCmd.ExecuteScalar()); return(productId); } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
/// <summary> /// gets product name and supplier name for certain package id /// joins products suppliuer and pakcages product suppliers to match the product and supplier id to a package id /// joins the package supplier to the supplier and product table to match a package id and supplier id with a package name and supplier name /// </summary> /// <param name="package"></param> /// <returns> list of produt name and supplier name</returns> public static List <Packages_Products_Suppliers> GetAllPackagesProductsSuppliers(Package package) { List <Packages_Products_Suppliers> packProdSups = new List <Packages_Products_Suppliers>(); Packages_Products_Suppliers packProdSup = null; SqlConnection con = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT p.ProdName, s.SupName, pps.ProductSupplierID, ps.ProductID, ps.SupplierID " + "FROM Packages_Products_Suppliers pps " + "INNER JOIN Products_Suppliers ps ON pps.ProductSupplierID = ps.ProductSupplierID " + "INNER JOIN Products p ON ps.ProductID = p.ProductID " + "INNER JOIN Suppliers s ON ps.SupplierID = s.SupplierID " + "WHERE PackageID = @PackageID"; SqlCommand cmd = new SqlCommand(selectStatement, con); cmd.Parameters.AddWithValue("@PackageID", package.PackageId); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) //while there is still data to be read { packProdSup = new Packages_Products_Suppliers(); packProdSup.ProductSupplerID = (int)reader["ProductSupplierID"]; packProdSup.ProductID = (int)reader["ProductID"]; packProdSup.SupplierID = (int)reader["SupplierID"]; packProdSup.ProdName = reader["ProdName"].ToString(); packProdSup.SupName = reader["SupName"].ToString(); packProdSups.Add(packProdSup); // add package products supplier object to the list } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(packProdSups); //list to be returned }
/// <summary> /// gets the next product supplier id when user is adding a new supplier /// </summary> /// <returns> product supplier id to dispaly for the user to see what the id is for the new info they are entering</returns> public static int GetNextProductSupplierID() { int productSupplier; SqlConnection con = TravelExpertsDB.GetConnection(); //selects the current indentity and then adds the identity increment for the table string selectQuery = "SELECT IDENT_CURRENT('Products_Suppliers') + IDENT_INCR('Products_Suppliers')"; SqlCommand selectCmd = new SqlCommand(selectQuery, con); try { con.Open(); productSupplier = Convert.ToInt32(selectCmd.ExecuteScalar()); return(productSupplier); } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
/// <summary> /// function to get data from Product_Supplier table /// </summary> /// <param name="ProdcutSupplierID"></param> /// <returns> list of product_supplier </returns> public static List <Product_Supplier> GetProduct_Supplier() { List <Product_Supplier> productSuppliers = new List <Product_Supplier>(); //holds list of product_supplier objects Product_Supplier ps; SqlConnection con = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT ProductSupplierID, ProdName, SupName, ps.ProductID, ps.SupplierID " + "FROM Products_Suppliers ps " + "INNER JOIN Products p ON ps.ProductID = p.ProductID " + "INNER JOIN Suppliers s ON ps.SupplierID = s.SupplierID " + "ORDER BY ProductSupplierID"; SqlCommand cmd = new SqlCommand(selectStatement, con); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) //reads until nothing left to read { ps = new Product_Supplier(); ps.ProductSupplierID = (int)reader["ProductSupplierID"]; ps.ProductID = (int)reader["ProductID"]; ps.SupplierID = (int)reader["SupplierID"]; ps.ProdName = reader["ProdName"].ToString(); ps.SupName = reader["SupName"].ToString(); productSuppliers.Add(ps); //adds product_supplier to list } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(productSuppliers); }
/// <summary> /// Gets list of all products from the database /// </summary> /// <returns>list of product objects </returns> public static List <Product> GetAllProduct() { //list to hold all of product objects List <Product> products = new List <Product>(); Product prod = null; //create connection SqlConnection con = TravelExpertsDB.GetConnection(); //SQL string to make selection string selectStatement = "SELECT ProductID, ProdName " + "FROM Products ORDER BY ProductID"; SqlCommand cmd = new SqlCommand(selectStatement, con); try { //open connection con.Open(); // create SQL reader SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) // keep reading while values in table { prod = new Product(); prod.ProductID = (int)reader["ProductID"]; prod.ProdName = reader["ProdName"].ToString(); products.Add(prod); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } //return list of products return(products); }
public static List <Supplier> GetSuppliersForProducts(Product product) { List <Supplier> suppliers = new List <Supplier>(); //empty list Supplier s = null; SqlConnection connection = TravelExpertsDB.GetConnection(); // DB connection string selectStatement = "SELECT ps.SupplierID, SupName " + "FROM Suppliers s " + "INNER JOIN Products_Suppliers ps ON s.SupplierID = ps.SupplierID " + "INNER JOIN Products p ON ps.ProductID = p.ProductID " + "WHERE p.ProductID = @ProductID " + "ORDER BY SupName"; SqlCommand cmd = new SqlCommand(selectStatement, connection); cmd.Parameters.AddWithValue("@ProductID", product.ProductID); try { connection.Open(); // open connection SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) // while there is another record { s = new Supplier(); s.SupplierId = (int)reader["SupplierId"]; s.SupName = reader["SupName"].ToString(); suppliers.Add(s); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); //close connection } return(suppliers); }
/// <summary> /// inserts a new package into the database /// </summary> /// <param name="package"></param> /// <returns> package id for the new item</returns> public static int AddPackage(Package package) { int packageID;//new package id SqlConnection con = TravelExpertsDB.GetConnection(); string insertStatement = "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " + "VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)"; SqlCommand cmd = new SqlCommand(insertStatement, con); //adding values to be inserted 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); try { con.Open(); cmd.ExecuteNonQuery(); //TODO: might not need this code below String selectQuery = "SELECT IDENT_CURRENT('Packages')"; SqlCommand selectCmd = new SqlCommand(selectQuery, con); packageID = Convert.ToInt32(selectCmd.ExecuteScalar()); // } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(packageID); }
/// <summary> /// adds row to package prodcut supplier connecting product and supplier info for a certain package /// takes both a product supplier object and package object /// </summary> /// <param name="productSupplier"></param> /// <param name="package"></param> public static void Add(Product_Supplier productSupplier, Package package) { SqlConnection con = TravelExpertsDB.GetConnection(); string insertStatement = "INSERT INTO Packages_Products_Suppliers (PackageID, ProductSupplierID) " + "VALUES (@PackageID, @ProductSupplierID)"; SqlCommand cmd = new SqlCommand(insertStatement, con); cmd.Parameters.AddWithValue("@PackageID", package.PackageId); cmd.Parameters.AddWithValue("@ProductSupplierID", productSupplier.ProductSupplierID); try { con.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
/// <summary> /// gets all the information from the package table /// </summary> /// <returns> list of package objects </returns> public static List <Package> GetPackage() { List <Package> packages = new List <Package>(); Package pack = null; SqlConnection con = TravelExpertsDB.GetConnection(); string selectStatement = "SELECT PackageID, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " + "FROM Packages"; SqlCommand cmd = new SqlCommand(selectStatement, con); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) // reads while still info in database { pack = new Package(); pack.PackageId = (int)reader["PackageID"]; pack.PkgName = reader["PkgName"].ToString(); pack.PkgStartDate = (DateTime)reader["PkgStartDate"]; pack.PkgEndDate = (DateTime)reader["PkgEndDate"]; pack.PkgDesc = reader["PkgDesc"].ToString(); pack.PkgBasePrice = Convert.ToDouble(reader["PkgBasePrice"]); pack.PkgAgencyCommission = Convert.ToDouble(reader["PkgAgencyCommission"]); packages.Add(pack); //adds package object to packages list } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(packages); //list of package objects }