//add new package to db public static void AddPackage(TravelPackage tp) { SqlConnection dbConnect = TravelExpertsDB.GetConnection(); string insertPackage = "INSERT INTO [Packages] ([PkgName], [PkgStartDate], [PkgEndDate], [PkgDesc], [PkgBasePrice], [PkgAgencyCommission]) " + "VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)"; SqlCommand cmd = new SqlCommand(insertPackage, dbConnect); cmd.Parameters.AddWithValue("@PkgName", tp.PkgName); cmd.Parameters.AddWithValue("@PkgStartDate", tp.PkgStartDate); cmd.Parameters.AddWithValue("@PkgEndDate", tp.PkgEndDate); cmd.Parameters.AddWithValue("@PkgDesc", tp.PkgDesc); cmd.Parameters.AddWithValue("@PkgBasePrice", tp.PkgBasePrice); cmd.Parameters.AddWithValue("@PkgAgencyCommission", tp.PkgAgencyCommission); try { dbConnect.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw ex; } finally { dbConnect.Close(); } }
// update package from the db public static bool UpdatePackage(TravelPackage oldPkg, TravelPackage newPkg) { bool success = false; SqlConnection dbConnect = TravelExpertsDB.GetConnection(); string updatePackage = "UPDATE Packages " + "SET PkgName = @PkgName, " + "PkgDesc = @PkgDesc, " + "PkgStartDate = @PkgStartDate, " + "PkgEndDate = @PkgEndDate, " + "PkgBasePrice = @PkgBasePrice, " + "PkgAgencyCommission = @PkgAgencyCommission " + "WHERE PackageId = @oldPkgID "; SqlCommand cmd = new SqlCommand(updatePackage, dbConnect); cmd.Parameters.AddWithValue("@PkgName", newPkg.PkgName); cmd.Parameters.AddWithValue("@PkgStartDate", newPkg.PkgStartDate); cmd.Parameters.AddWithValue("@PkgEndDate", newPkg.PkgEndDate); cmd.Parameters.AddWithValue("@PkgDesc", newPkg.PkgDesc); cmd.Parameters.AddWithValue("@PkgBasePrice", newPkg.PkgBasePrice); cmd.Parameters.AddWithValue("@PkgAgencyCommission", newPkg.PkgAgencyCommission); cmd.Parameters.AddWithValue("@oldPkgID", oldPkg.PkgID); try { dbConnect.Open(); cmd.ExecuteNonQuery(); success = true; } catch (SqlException ex) { throw ex; } finally { dbConnect.Close(); } return(success); }
// get package from db public static List <TravelPackage> GetTavelPackage() { List <TravelPackage> packages = new List <TravelPackage>(); // empty list of Packages TravelPackage travelPackage; SqlConnection dbConnect = TravelExpertsDB.GetConnection(); string selectQuery = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " + "FROM Packages"; SqlCommand cmd = new SqlCommand(selectQuery, dbConnect); try { dbConnect.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { travelPackage = new TravelPackage(); travelPackage.PkgID = (int)reader["PackageId"]; travelPackage.PkgName = (string)reader["PkgName"]; travelPackage.PkgStartDate = (DateTime)reader["PkgStartDate"]; travelPackage.PkgEndDate = (DateTime)reader["PkgEndDate"]; travelPackage.PkgDesc = (string)reader["PkgDesc"]; travelPackage.PkgBasePrice = (decimal)reader["PkgBasePrice"]; travelPackage.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"]; packages.Add(travelPackage); } } catch (SqlException ex) { throw ex; } finally { dbConnect.Close(); } return(packages); }