public Product_Supplier selectedProductSupplier { get; set; } //the product suppliers selected from the list of active package product suppliers //form constructor, sets active package, fills the product suppliers public frmEditProductSuppliers(Package package) { InitializeComponent(); activePackage = package; Text = activePackage.PkgName; activePackage.fillSuppliers(); }
//The form constructor for edit mode public frmAddEdit(string message, Package package) { InitializeComponent(); this.Text = message; this.message = message; this.activePackage = package; PkgImg = package.PkgImg; displayPackage(); btnConfirm.Text = "Confirm"; }
public static List<Package> GetAllPackages() { // SELECT * FROM Packages (Table) // Get the List of all Packages from the Packages Table // returns List<Package> // throws SqlException and Exception // checked jan 13 DS List<Package> ListPkg = new List<Package>(); string selectAll = "SELECT * FROM Packages"; SqlConnection connection = MMATravelExperts.GetConnection(); SqlCommand selectAllCmd = new SqlCommand(selectAll, connection); try { connection.Open(); SqlDataReader reader = selectAllCmd.ExecuteReader(); while(reader.Read()) { Package pkg = new Package(); pkg.PackageId = (int)reader["PackageId"]; pkg.PkgName = reader["PkgName"].ToString(); pkg.PkgStartDate = (DateTime)reader["PkgStartDate"]; pkg.PkgEndDate = (DateTime)reader["PkgEndDate"]; pkg.PkgDesc = reader["PkgDesc"].ToString(); pkg.PkgBasePrice = (decimal)reader["PkgBasePrice"]; pkg.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"]; if (!reader.IsDBNull(7)) pkg.PkgImg = (byte[])reader["PkgImg"]; ListPkg.Add(pkg); } } catch (SqlException SqlEx) { throw SqlEx; } catch (Exception Ex) { throw Ex; } finally { connection.Close(); } return ListPkg; }
/// <summary> /// Gets the package object from the package table /// </summary> /// <param name="PackageId"></param> /// <returns>package</returns> public static Package GetPackage(int PackageID) // Get the Package for a given PackageID from table Packages // PackageId is an int and the PK for Table Package // returns type Package containing 1 of package object, !!!! null if no package (only 4 packages) !!!! // throws SqlException and Exception // checked checked jan 13 DS { SqlConnection connection = MMATravelExperts.GetConnection(); string selectStatement = "SELECT * FROM Packages WHERE PackageId=@PackageId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@PackageId", PackageID); try { connection.Open(); SqlDataReader pkgReader= selectCommand.ExecuteReader(CommandBehavior.SingleRow); if(pkgReader.Read()) { Package pkg = new Package(); pkg.PackageId = (int)pkgReader["PackageId"]; pkg.PkgName = pkgReader["PkgName"].ToString(); pkg.PkgStartDate= (DateTime)pkgReader["PkgStartDate"]; pkg.PkgEndDate=(DateTime)pkgReader["PkgEndDate"]; pkg.PkgDesc=pkgReader["PkgDesc"].ToString(); pkg.PkgBasePrice=(Decimal)pkgReader["PkgBasePrice"]; pkg.PkgAgencyCommission=(Decimal)pkgReader["PkgAgencyCommission"]; return pkg; } else { return null; // no package returns null } } catch (SqlException SqlEx) { throw SqlEx; } catch (Exception Ex) { throw Ex; } finally { connection.Close(); } }
//<summary> //Updates a package in the Packages Table //</summary> //<param name="oldPkg">old package</param> //<param name="newPkg">new package</param> //<returns></returns> public static bool UpdatePackage(Package oldPkg, Package newPkg) { // Updates the Packages Table // parameter oldPkg ... the old row as an instance of Package class // parameter newPkg ... the new row as an instance of Package class // returns true row updated, false row not updated // throws SqlException and Exception SqlConnection connection = MMATravelExperts.GetConnection(); string updateStatement="UPDATE Packages SET PkgName=@newPkgName, "+ "PkgStartDate=@newPkgStartDate, PkgEndDate=@newPkgEndDate, "+ "PkgDesc=@newPkgDesc, PkgBasePrice=@newPkgBasePrice, PkgAgencyCommission=@newPkgAgencyCommission, PkgImg=@newPkgImg " + "WHERE PackageId=@oldPackageId and PkgName=@oldPkgName and PkgStartDate=@oldPkgStartDate and "+ "PkgEndDate=@oldPkgEndDate and PkgDesc=@oldPkgDesc and PkgBasePrice=@oldPkgBasePrice and "+ "PkgAgencyCommission=@oldPkgAgencyCommission"; SqlCommand updateCommand = new SqlCommand(updateStatement,connection); // new package listing updateCommand.Parameters.AddWithValue("@newPackageId",newPkg.PackageId); updateCommand.Parameters.AddWithValue("@newPkgName",newPkg.PkgName); updateCommand.Parameters.AddWithValue("@newPkgStartDate",newPkg.PkgStartDate); updateCommand.Parameters.AddWithValue("@newPkgEndDate",newPkg.PkgEndDate); updateCommand.Parameters.AddWithValue("@newPkgDesc",newPkg.PkgDesc); updateCommand.Parameters.AddWithValue("@newPkgBasePrice",newPkg.PkgBasePrice); updateCommand.Parameters.AddWithValue("@newPkgAgencyCommission",newPkg.PkgAgencyCommission); if (newPkg.PkgImg == null) updateCommand.Parameters.AddWithValue("@newPkgImg", SqlDbType.VarBinary); else updateCommand.Parameters.AddWithValue("@newPkgImg", newPkg.PkgImg); // old package listing updateCommand.Parameters.AddWithValue("@oldPackageId",oldPkg.PackageId); updateCommand.Parameters.AddWithValue("@oldPkgName",oldPkg.PkgName); updateCommand.Parameters.AddWithValue("@oldPkgStartDate",oldPkg.PkgStartDate); updateCommand.Parameters.AddWithValue("@oldPkgEndDate",oldPkg.PkgEndDate); updateCommand.Parameters.AddWithValue("@oldPkgDesc",oldPkg.PkgDesc); updateCommand.Parameters.AddWithValue("@oldPkgBasePrice",oldPkg.PkgBasePrice); updateCommand.Parameters.AddWithValue("@oldPkgAgencyCommission",oldPkg.PkgAgencyCommission); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count>0) { return true; // rows updated } else { return false; //rows not updated } } catch (SqlException SqlEx) { throw SqlEx; } catch (Exception Ex) { throw Ex; } finally { connection.Close(); } }
//<summary> //Add a package object to the Package Table //</summary> //<param name="pkg">package</param> //<returns>PackageId (PK)</returns> public static int AddPackage(Package pkg) { // Add a Package to the Packages Table // package is the instance of Package class // returns the PackageId of the row inserted or -1 if not added to table // throws SqlException and Exception SqlConnection connection = MMATravelExperts.GetConnection(); String insertStatement = "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission, PkgImg) " + "VALUES (@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission, @PkgImg)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@PkgName", pkg.PkgName); insertCommand.Parameters.AddWithValue("@PkgStartDate", pkg.PkgStartDate); insertCommand.Parameters.AddWithValue("@PkgEndDate", pkg.PkgEndDate); insertCommand.Parameters.AddWithValue("@PkgDesc", pkg.PkgDesc); insertCommand.Parameters.AddWithValue("@PkgBasePrice", pkg.PkgBasePrice); insertCommand.Parameters.AddWithValue("@PkgAgencyCommission", pkg.PkgAgencyCommission); if (pkg.PkgImg == null) insertCommand.Parameters.AddWithValue("@PkgImg", SqlDbType.VarBinary); else insertCommand.Parameters.AddWithValue("@PkgImg", pkg.PkgImg); try { connection.Open(); int numRows = insertCommand.ExecuteNonQuery(); if (numRows > 0) { int prodIDcheck=0; string selectStatement = "SELECT PackageId FROM Packages where PkgName=@PkgName and PkgStartDate=@PkgStartDate and PkgEndDate=@PkgEndDate "+ "and PkgDesc=@PkgDesc and PkgBasePrice=@PkgBasePrice and PkgAgencyCommission=@PkgAgencyCommission"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@PkgName", pkg.PkgName); selectCommand.Parameters.AddWithValue("@PkgStartDate", pkg.PkgStartDate); selectCommand.Parameters.AddWithValue("@PkgEndDate", pkg.PkgEndDate); selectCommand.Parameters.AddWithValue("@PkgDesc", pkg.PkgDesc); selectCommand.Parameters.AddWithValue("@PkgBasePrice", pkg.PkgBasePrice); selectCommand.Parameters.AddWithValue("@PkgAgencyCommission", pkg.PkgAgencyCommission); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { prodIDcheck = Convert.ToInt32(reader["PackageId"]); } return prodIDcheck; } else { return -1; } } catch (SqlException SqlEx) { throw SqlEx; } finally { connection.Close(); } }
/// <summary> /// Adds the input product supplier to the input package with a new row inserted into the packages_products_suppliers table /// </summary> /// <param name="ps">the product-supplier to add</param> /// <param name="pack">the package to add to</param> /// <returns>true if successful, false if fail</returns> public static bool addProductSupplierToPackage(Product_Supplier ps, Package pack) { SqlConnection connection = MMATravelExperts.GetConnection(); string insertStatement = "Insert into Packages_Products_Suppliers values (@packageId,@productSupplierId)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@PackageId", pack.PackageId); insertCommand.Parameters.AddWithValue("@productSupplierId", ps.ProductSupplierId); try { connection.Open(); int result = insertCommand.ExecuteNonQuery(); if (result == 1) return true; else return false; } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
//set the active package based on the combo box selection private void cbPackages_SelectedIndexChanged(object sender, EventArgs e) { ClearControls(); activePackage = (Package)cbPackages.SelectedItem; activePackage.fillSuppliers(); DisplayPackage(); }
private void createPackage() { //handle the ToString currency format string newBasePrice = txtPrice.Text.Replace(",", "").Replace("$", ""); string newCommission = txtCommission.Text.Replace(",", "").Replace("$", ""); //create a new package from the form controls newPackage = new Package(); newPackage.PkgName = txtName.Text; newPackage.PkgStartDate = dtpStart.Value; newPackage.PkgEndDate = dtpEnd.Value; newPackage.PkgDesc = txtDescription.Text; newPackage.PkgBasePrice = Convert.ToDecimal(newBasePrice); if (txtCommission.Text == "") newPackage.PkgAgencyCommission = 0m; else newPackage.PkgAgencyCommission = Convert.ToDecimal(newCommission); if (PkgImg != null) newPackage.PkgImg = PkgImg; }