Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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();
            }
        }
Exemplo n.º 3
0
        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);
        }
Exemplo n.º 4
0
 public static List <NamedPackageProductSupplier> GetPackageProductSuppliersByPackage(TravelPackage pkg)
 {
     return(GetPackageProductSuppliersByPackage(pkg.ID));
 }