Example #1
0
        /// <summary>
        /// Angelito: Updates package info
        /// </summary>
        /// <param name="oldPkg">old package info</param>
        /// <param name="newPkg">new package info</param>
        /// <returns>success indicator</returns>
        public static bool UpdatePackage(Package oldPkg, Package newPkg)
        {
            // added consideration for Null values -Katrina
            bool result = false; // no success yet

            using (SqlConnection connection = TravelExpertsDB.GetConnection())
            {
                string updateStatement = "UPDATE Packages SET " +
                                         "PkgName = @NewPkgName, " +
                                         "PkgStartDate = @NewPkgStartDate, " +
                                         "PkgEndDate = @NewPkgEndDate, " +
                                         "PkgDesc = @NewPkgDesc, " +
                                         "PkgBasePrice = @NewPkgBasePrice, " +
                                         "PkgAgencyCommission = @NewPkgAgencyCommission " +
                                         "WHERE PackageId = @OldPackageId " + // identifies package
                                         "AND PkgName = @OldPkgName " +
                                                                              // PkgStartDate Null option -Katrina
                                         "AND (PkgStartDate = @OldPkgStartDate " +
                                         "OR PkgStartDate IS NULL " +
                                         "AND @OldPkgStartDate IS NULL) " +
                                         // PkgEndDate Null option -Katrina
                                         "AND (PkgEndDate = @OldPkgEndDate " +
                                         "OR PkgEndDate IS NULL " +
                                         "AND @OldPkgEndDate IS NULL) " +
                                         // PkgDesc Null option -Katrina
                                         "AND (PkgDesc = @OldPkgDesc " +
                                         "OR PkgDesc IS NULL " +
                                         "AND @OldPkgDesc IS NULL) " +
                                         "AND PkgBasePrice = @OldPkgBasePrice " +
                                         // PkgAgencyCommission Null option -Katrina
                                         "AND (PkgAgencyCommission = @OldPkgAgencyCommission " +
                                         "OR PkgAgencyCommission IS NULL " +
                                         "AND @OldPkgAgencyCommission IS NULL)";
                using (SqlCommand cmd = new SqlCommand(updateStatement, connection))
                {
                    // added consideration for Null values -Katrina
                    cmd.Parameters.AddWithValue("@NewPkgName", newPkg.PkgName);

                    if (newPkg.PkgStartDate == null) // if new PkgStartDate is null
                    {
                        cmd.Parameters.AddWithValue("@NewPkgStartDate", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@NewPkgStartDate", (DateTime)newPkg.PkgStartDate);
                    }

                    if (newPkg.PkgEndDate == null) // if new PkgEndDate is null
                    {
                        cmd.Parameters.AddWithValue("@NewPkgEndDate", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@NewPkgEndDate", (DateTime)newPkg.PkgEndDate);
                    }

                    // PkgDesc and PkgBasePrice can't be null when modifying
                    cmd.Parameters.AddWithValue("@NewPkgDesc", newPkg.PkgDesc);
                    cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPkg.PkgBasePrice);

                    if (newPkg.PkgAgencyCommission == null) // if new PkgAgencyCommission is null
                    {
                        cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", (decimal)newPkg.PkgAgencyCommission);
                    }

                    cmd.Parameters.AddWithValue("@OldPackageId", oldPkg.PackageId);
                    cmd.Parameters.AddWithValue("@OldPkgName", oldPkg.PkgName);

                    if (oldPkg.PkgStartDate == null) // if old PkgStartDate is null
                    {
                        cmd.Parameters.AddWithValue("@OldPkgStartDate", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@OldPkgStartDate", (DateTime)oldPkg.PkgStartDate);
                    }

                    if (oldPkg.PkgEndDate == null) // if old PkgEndDate is null
                    {
                        cmd.Parameters.AddWithValue("@OldPkgEndDate", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@OldPkgEndDate", (DateTime)oldPkg.PkgEndDate);
                    }

                    if (oldPkg.PkgDesc == null) // if old PkgDesc is null
                    {
                        cmd.Parameters.AddWithValue("@OldPkgDesc", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@OldPkgDesc", (string)oldPkg.PkgDesc);
                    }

                    cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPkg.PkgBasePrice);

                    if (oldPkg.PkgAgencyCommission == null) // if old PkgAgencyCommission is null
                    {
                        cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", (decimal)oldPkg.PkgAgencyCommission);
                    }

                    // open connection
                    connection.Open();
                    // execute UPDATE command
                    int count = cmd.ExecuteNonQuery();
                    if (count > 0) // if row(s) affected
                    {
                        result = true;
                    }
                }
            }
            return(result);
        }
Example #2
0
        /// <summary>
        /// Katrina Spencer: Retrieves product-supplier info with given id
        /// </summary>
        /// <param name="prodSupId">id of product-supplier to get</param>
        /// <returns>product-supplier object</returns>
        public static ProductSupplier GetProductSupplier(int prodSupId)
        {
            ProductSupplier prodSup = null;

            // connection
            using (SqlConnection connection = TravelExpertsDB.GetConnection())
            {
                string query = "SELECT ProductSupplierId, ps.ProductId, ProdName, ps.SupplierId, SupName " +
                               "FROM Products_Suppliers AS ps " +
                               "JOIN Products ON ps.ProductId = Products.ProductId " +
                               "JOIN Suppliers ON ps.SupplierId = Suppliers.SupplierId " +
                               "WHERE ProductSupplierId = @ProductSupplierId";
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@ProductSupplierId", prodSupId);
                    // run command and process data
                    connection.Open();
                    using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
                    {
                        if (dr.Read()) // if data
                        {
                            prodSup = new ProductSupplier();
                            prodSup.ProductSupplierId = (int)dr["ProductSupplierId"];

                            // determine if it is DBNull and set
                            int colProd = dr.GetOrdinal("ProductId"); // column number of ProductId
                            if (dr.IsDBNull(colProd))                 // if reader contains DBNull in this column
                            {
                                prodSup.ProductId = null;             // make null in the object
                            }
                            else // if not null
                            {
                                prodSup.ProductId = (int)(dr["ProductId"]);
                            }

                            int colSup = dr.GetOrdinal("SupplierId"); // column number of SupplierId
                            if (dr.IsDBNull(colSup))                  // if reader contains DBNull in this column
                            {
                                prodSup.SupplierId = null;            // make null in the object
                            }
                            else // if not null
                            {
                                prodSup.SupplierId = (int)(dr["SupplierId"]);
                            }

                            int colProdName = dr.GetOrdinal("ProdName"); // column number of ProdName
                            if (dr.IsDBNull(colProdName))                // if reader contains DBNull in this column
                            {
                                prodSup.ProdName = null;                 // make null in the object
                            }
                            else // if not null
                            {
                                prodSup.ProdName = (string)(dr["ProdName"]);
                            }

                            int colSupName = dr.GetOrdinal("SupName"); // column number of SupName
                            if (dr.IsDBNull(colSupName))               // if reader contains DBNull in this column
                            {
                                prodSup.SupName = null;                // make null in the object
                            }
                            else // if not null
                            {
                                prodSup.SupName = (string)(dr["SupName"]);
                            }
                        }
                    }
                }
            }
            return(prodSup);
        }
Example #3
0
        /// <summary>
        /// Angelito: Adds a new package
        /// </summary>
        /// <param name="pkg">new package info</param>
        /// <returns>new package id</returns>
        public static int AddPackage(Package pkg)
        {
            int pkgId = 0;

            using (SqlConnection connection = TravelExpertsDB.GetConnection())
            {
                string insertStatement =
                    "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
                    "OUTPUT INSERTED.PackageId " +
                    "VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)";
                using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
                {
                    // Added consideration for Null values -Katrina
                    cmd.Parameters.AddWithValue("@PkgName", pkg.PkgName);
                    Console.WriteLine("Package name in databse to be inserted " + pkg.PkgName);

                    if (pkg.PkgStartDate == null)
                    {
                        cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@PkgStartDate", (DateTime)pkg.PkgStartDate);
                    }

                    if (pkg.PkgEndDate == null)
                    {
                        cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@PkgEndDate", (DateTime)pkg.PkgEndDate);
                    }

                    if (pkg.PkgDesc == null)
                    {
                        cmd.Parameters.AddWithValue("@PkgDesc", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@PkgDesc", (string)pkg.PkgDesc);
                    }

                    cmd.Parameters.AddWithValue("@PkgBasePrice", pkg.PkgBasePrice);

                    if (pkg.PkgAgencyCommission == null)
                    {
                        cmd.Parameters.AddWithValue("@PkgAgencyCommission", DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@PkgAgencyCommission", (decimal)pkg.PkgAgencyCommission);
                    }

                    // open connection
                    connection.Open();
                    pkgId = (int)cmd.ExecuteScalar(); // returns one value
                }
            }
            return(pkgId);
        }
Example #4
0
        /// <summary>
        /// Katrina Spencer: Updates product-supplier info
        /// </summary>
        /// <param name="oldProdSup">old product-supplier info</param>
        /// <param name="newProdSup">new product-supplier info</param>
        /// <returns>success indicator</returns>
        public static bool UpdateProductSupplier(ProductSupplier oldProdSup, ProductSupplier newProdSup)
        {
            bool result = false; // no success yet

            using (SqlConnection connection = TravelExpertsDB.GetConnection())
            {
                string updateStatement = "UPDATE Products_Suppliers " +
                                         "SET ProductId = @NewProductId, " +
                                         "SupplierId = @NewSupplierId " +
                                         "WHERE ProductSupplierId = @OldProductSupplierId " + // identifies product-supplier
                                         "AND (ProductId = @OldProductId " +
                                         "OR ProductId IS NULL " +
                                         "AND @OldProductId IS NULL) " +
                                         "AND (SupplierId = @OldSupplierId " +
                                         "OR SupplierId IS NULL " +
                                         "AND @OldSupplierId IS NULL)";
                using (SqlCommand cmd = new SqlCommand(updateStatement, connection))
                {
                    if (newProdSup.ProductId == null)                               // if new ProductId is null
                    {
                        cmd.Parameters.AddWithValue("@NewProductId", DBNull.Value); // set DBNull
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@NewProductId", (int)newProdSup.ProductId);
                    }

                    if (newProdSup.SupplierId == null)                               // if new SupplierId is null
                    {
                        cmd.Parameters.AddWithValue("@NewSupplierId", DBNull.Value); // set DBNull
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@NewSupplierId", (int)newProdSup.SupplierId);
                    }

                    cmd.Parameters.AddWithValue("@OldProductSupplierId", oldProdSup.ProductSupplierId);

                    if (oldProdSup.ProductId == null)                               // if old ProductId is null
                    {
                        cmd.Parameters.AddWithValue("@OldProductId", DBNull.Value); // set DBNull
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@OldProductId", (int)oldProdSup.ProductId);
                    }

                    if (oldProdSup.SupplierId == null)                               // if old SupplierId is null
                    {
                        cmd.Parameters.AddWithValue("@OldSupplierId", DBNull.Value); // set DBNull
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@OldSupplierId", (int)oldProdSup.SupplierId);
                    }

                    // open connection
                    connection.Open();
                    // execute UPDATE command
                    int count = cmd.ExecuteNonQuery();
                    if (count > 0) // if row(s) affected
                    {
                        result = true;
                    }
                }
            }
            return(result);
        }