Ejemplo n.º 1
0
        /// <summary>
        /// Inserts a new packaage product supplier
        /// from an existing package and product supplier
        /// </summary>
        /// <param name="pack"></param>
        /// <param name="prod_supp"></param>
        /// <returns>Indicator of success</returns>
        public static bool AddPackages_Products_Supplier(Package pack, Products_Supplier prod_supp)
        {
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        insertStatement = "INSERT INTO Packages_Products_Suppliers(PackageId, ProductSupplierId) " +
                                            "VALUES(@PackageId, @ProductSupplierId)";
            SqlCommand cmd = new SqlCommand(insertStatement, connection);

            cmd.Parameters.AddWithValue("@PackageId", pack.PackageId);
            cmd.Parameters.AddWithValue("@ProductSupplierId", prod_supp.ProductSupplierId);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Gets unique product based on passed product ID
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public static List <Products> GetProducts(int productId)
        {
            List <Products> products        = new List <Products>();
            Products        prod            = null;;
            SqlConnection   connection      = TravelExpertConnect.GetConnection();
            string          selectStatement = "SELECT ProductID, ProdName " +
                                              "From Products " +
                                              "WHERE ProductId = @ProductId " +
                                              "ORDER By ProductID";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

            selectCommand.Parameters.AddWithValue("@ProductId", productId);

            try
            {
                connection.Open();
                SqlDataReader dr = selectCommand.ExecuteReader();
                while (dr.Read())
                {
                    prod           = new Products();
                    prod.ProductId = (int)dr["ProductID"];
                    prod.ProdName  = (string)dr["ProdName"];
                    products.Add(prod);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(products);
        }
Ejemplo n.º 3
0
        public static List <Suppliers> GetAllSuppliers()
        {
            List <Suppliers> suppliers       = new List <Suppliers>();
            Suppliers        supp            = null;;
            SqlConnection    connection      = TravelExpertConnect.GetConnection();
            string           selectStatement = "SELECT SupplierID, SupName " +
                                               "From Suppliers ORDER By SupplierID";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

            try
            {
                connection.Open();
                SqlDataReader dr = selectCommand.ExecuteReader();
                while (dr.Read())
                {
                    supp            = new Suppliers();
                    supp.SupplierId = (int)dr["SupplierID"];
                    supp.SupName    = (string)dr["SupName"];
                    suppliers.Add(supp);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(suppliers);
        }
Ejemplo n.º 4
0
        /// <summary>
        /// Add passed  supplier to DB
        /// </summary>
        /// <param name="supp"></param>
        /// <returns></returns>
        public static bool AddSuppliers(Suppliers supp)
        {
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        insertStatement = "INSERT INTO Suppliers (SupplierId, SupName) " +
                                            "VALUES(@SupplierId, @SupName)";
            SqlCommand cmd = new SqlCommand(insertStatement, connection);

            cmd.Parameters.AddWithValue("@SupplierId", supp.SupplierId);
            cmd.Parameters.AddWithValue("@SupName", supp.SupName);


            try
            {
                connection.Open();

                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 5
0
        /// <summary>
        /// Deletes a package product supplier
        /// </summary>
        /// <param name="pack_prod_supp"></param>
        /// <returns>Indicator of success</returns>
        public static bool DeletePackages_Products_Supplier(Packages_Products_Supplier pack_prod_supp)
        {
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        deleteStatement = "DELETE FROM Packages_Products_Suppliers " +
                                            "WHERE PackageId = @PackageId " + // to identify the product to be  deleted
                                            "AND ProductSupplierId = @ProductSupplierId";


            SqlCommand cmd = new SqlCommand(deleteStatement, connection);

            cmd.Parameters.AddWithValue("@ProductSupplierId", pack_prod_supp.ProductSupplierId);
            cmd.Parameters.AddWithValue("@PackageId", pack_prod_supp.PackageId);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 6
0
        /// <summary>
        /// Selects all package products suppliers
        /// </summary>
        /// <returns>A list of existing package products suppliers</returns>
        public static List <Packages_Products_Supplier> GetAllPackages_Product_Suppliers()
        {
            List <Packages_Products_Supplier> packages_Products_Suppliers = new List <Packages_Products_Supplier>();
            Packages_Products_Supplier        pack_prod_supp = null;;
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        selectStatement = "SELECT PkgName, Packages_Products_Suppliers.PackageId, ProductSupplierId " +
                                            "From Packages_Products_Suppliers, Packages " +
                                            "where Packages_Products_Suppliers.PackageId = Packages.PackageId " +
                                            "ORDER BY PkgName";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

            try
            {
                connection.Open();
                SqlDataReader dr = selectCommand.ExecuteReader();
                while (dr.Read())
                {
                    pack_prod_supp                   = new Packages_Products_Supplier();
                    pack_prod_supp.PackageId         = (int)dr["PackageId"];
                    pack_prod_supp.ProductSupplierId = (int)dr["ProductSupplierId"];
                    packages_Products_Suppliers.Add(pack_prod_supp);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(packages_Products_Suppliers);
        }
Ejemplo n.º 7
0
        /// <summary>
        /// Add product Suppliier by linking a passed products and supplier ID
        /// </summary>
        /// <param name="prod"></param>
        /// <param name="supp"></param>
        /// <returns></returns>
        public static int AddProduct_Supplier(Products prod, Suppliers supp)
        {
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierId) " +
                                            "VALUES(@ProductId, @SupplierId)";
            SqlCommand cmd = new SqlCommand(insertStatement, connection);

            cmd.Parameters.AddWithValue("@ProductId", prod.ProductId);
            cmd.Parameters.AddWithValue("@SupplierId", supp.SupplierId);

            try
            {
                connection.Open();
                cmd.ExecuteNonQuery(); // run the insert command
                                       // get the generated ID - current identity value for  Products_Supplier table
                string     selectQuery       = "SELECT IDENT_CURRENT('Products_Suppliers') FROM Products_Suppliers";
                SqlCommand selectCmd         = new SqlCommand(selectQuery, connection);
                int        ProductSupplierId = Convert.ToInt32(selectCmd.ExecuteScalar()); // single value
                // typecase (int) does NOT work!
                return(ProductSupplierId);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 8
0
        public static List <Products> GetAllProducts()
        {
            List <Products> products        = new List <Products>();
            Products        prod            = null;;
            SqlConnection   connection      = TravelExpertConnect.GetConnection();
            string          selectStatement = "SELECT ProductID, ProdName " +
                                              "From Products ORDER By ProductID";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

            try
            {
                connection.Open();
                SqlDataReader dr = selectCommand.ExecuteReader();
                while (dr.Read())
                {
                    prod           = new Products();
                    prod.ProductId = (int)dr["ProductID"];
                    prod.ProdName  = (string)dr["ProdName"];
                    prod.Suppliers = Products_SupplierDB.GetUniqueProduct_Suppliers(prod.ProductId);
                    products.Add(prod);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(products);
        }
Ejemplo n.º 9
0
        /// <summary>
        /// Updates existing product
        /// </summary>
        /// <param name="oldProd">data before update</param>
        /// <param name="newrod">new data for the update</param>
        /// <returns>indicator of success</returns>
        public static bool UpdateProduct(Products oldProd, Products newProd)
        {
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        updateStatement = "UPDATE Products " +
                                            "SET ProdName = @NewProdName " +
                                            "WHERE ProductID = @OldProdID " +
                                            "AND ProdName = @OldProdName"; // remaining conditions - to ensure optimistic concurrency
            SqlCommand cmd = new SqlCommand(updateStatement, connection);

            cmd.Parameters.AddWithValue("@NewProdName", newProd.ProdName);
            cmd.Parameters.AddWithValue("@OldProdID", oldProd.ProductId);
            cmd.Parameters.AddWithValue("@OldProdName", oldProd.ProdName);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 10
0
        /// <summary>
        /// Adds a new packages to the Packages Table in Travel Experts Database
        /// </summary>
        /// <param name="pack"> Package object that cotaing data for the new record</param>
        /// <returns>generated PackageId</returns>
        public static int AddPackage(Package pack)
        {
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        insertStatement = "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
                                            "VALUES(@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission)";
            SqlCommand cmd = new SqlCommand(insertStatement, connection);

            cmd.Parameters.AddWithValue("@PkgName", pack.Name);
            cmd.Parameters.AddWithValue("@PkgDesc", pack.Desc);
            cmd.Parameters.AddWithValue("@PkgBasePrice", pack.BasePrice);

            //This block of code specifies passing a nullable type as a parameter to SQL for StartDate
            SqlParameter startDateParam = new SqlParameter("@PkgStartDate", pack.StartDate == null ?
                                                           (Object)DBNull.Value : pack.StartDate);

            startDateParam.IsNullable = true;
            startDateParam.Direction  = ParameterDirection.Input;
            startDateParam.SqlDbType  = SqlDbType.DateTime;
            cmd.Parameters.Add(startDateParam);

            //This block of code specifies passing a nullable type as a parameter to SQL for endDate
            SqlParameter endDateParam = new SqlParameter("@PkgEndDate", pack.EndDate == null ?
                                                         (Object)DBNull.Value : pack.EndDate);

            endDateParam.IsNullable = true;
            endDateParam.Direction  = ParameterDirection.Input;
            endDateParam.SqlDbType  = SqlDbType.DateTime;
            cmd.Parameters.Add(endDateParam);

            //This block of code specifies passing a nullable type as a parameter to SQL for Agencycommision
            SqlParameter pkgAgencyParam = new SqlParameter("@PkgAgencyCommission", pack.AgencyCommission == null ?
                                                           (Object)DBNull.Value : pack.AgencyCommission);

            pkgAgencyParam.IsNullable = true;
            pkgAgencyParam.Direction  = ParameterDirection.Input;
            pkgAgencyParam.SqlDbType  = SqlDbType.Money;
            cmd.Parameters.Add(pkgAgencyParam);

            try
            {
                connection.Open();
                cmd.ExecuteNonQuery(); // run the insert command
                                       // get the generated Id - current Identity value for  Packages table
                string     selectQuery = "SELECT IdENT_CURRENT('Packages') FROM Packages";
                SqlCommand selectCmd   = new SqlCommand(selectQuery, connection);
                int        PackageId   = Convert.ToInt32(selectCmd.ExecuteScalar()); // single value
                                                                                     // typecase (int) does NOT work!
                return(PackageId);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 11
0
        /// <summary>
        /// Delete PAssed Supplier from DB
        /// </summary>
        /// <param name="supp"></param>
        /// <returns></returns>
        public static bool DeleteSupplier(Suppliers supp)
        {
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        deleteStatement = "delete Products_Suppliers from Products_Suppliers " +
                                            "inner join Suppliers on Products_Suppliers.SupplierId = Suppliers.SupplierId " +
                                            "inner join Products on Products_Suppliers.ProductId = Products.ProductId " +
                                            "where Suppliers.SupplierId = @SupplierId " +
                                                                                //first part deletes the link to product supplier table if it exists
                                                                                //Second part deletes the actual supplier
                                            "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", supp.SupplierId);
            cmd.Parameters.AddWithValue("@SupName", supp.SupName);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 12
0
/// <summary>
/// Delete passed products from datatabse
/// </summary>
/// <param name="prod"></param>
/// <returns></returns>
        public static bool DeleteProduct(Products prod)
        {
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        deleteStatement = "delete Products_Suppliers from Products_Suppliers " +
                                            "inner join Products on Products_Suppliers.ProductId = Products.ProductId " +
                                            "inner join Suppliers on Products_Suppliers.SupplierId = Suppliers.SupplierId " +
                                            "where Products.ProductId = @ProductId " +
                                            //first part deletes the link to product supplier table if it exists
                                            //second part deltes the actual product
                                            "delete from Products " +
                                            "where ProductId = @ProductId " +
                                            "and ProdName = @ProdName";


            SqlCommand cmd = new SqlCommand(deleteStatement, connection);

            cmd.Parameters.AddWithValue("@ProductID", prod.ProductId);
            cmd.Parameters.AddWithValue("@ProdName", prod.ProdName);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 13
0
/// <summary>
/// Gets packages product suppliers with a specific package ID
/// </summary>
/// <param name="packageId"></param>
/// <returns></returns>
        public static List <Packages_Products_Supplier> GetPackages_Product_Suppliers(int packageId)
        {
            List <Packages_Products_Supplier> packages_Products_Suppliers = new List <Packages_Products_Supplier>();
            Packages_Products_Supplier        pack_prod_supp = null;;
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        selectStatement = "SELECT Packages_Products_Suppliers.PackageId, Products_Suppliers.ProductSupplierId, ProdName, SupName " +
                                            "From Packages_Products_Suppliers, Products_Suppliers, Products, Suppliers " +
                                            "where Packages_Products_Suppliers.PackageId = @PackageId " +
                                            "And Packages_Products_Suppliers.ProductSupplierId = Products_Suppliers.ProductSupplierId " +
                                            "and Products_Suppliers.ProductId = Products.ProductId " +
                                            "and Products_Suppliers.SupplierId = Suppliers.SupplierId";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

            selectCommand.Parameters.AddWithValue("@PackageId", packageId);

            try
            {
                connection.Open();
                SqlDataReader dr = selectCommand.ExecuteReader();
                while (dr.Read())
                {
                    pack_prod_supp                   = new Packages_Products_Supplier();
                    pack_prod_supp.PackageId         = (int)dr["PackageId"];
                    pack_prod_supp.ProductSupplierId = (int)dr["ProductSupplierId"];
                    pack_prod_supp.ProdName          = dr["ProdName"].ToString();
                    pack_prod_supp.SupName           = dr["SupName"].ToString();
                    packages_Products_Suppliers.Add(pack_prod_supp);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(packages_Products_Suppliers);
        }
Ejemplo n.º 14
0
        /// <summary>
        /// Get unique products suppliers with passed product supplier ID
        /// </summary>
        /// <param name="prodId"></param>
        /// <returns></returns>
        public static List <Products_Supplier> GetUniqueProduct_Suppliers(int prodId)
        {
            List <Products_Supplier> products_Suppliers = new List <Products_Supplier>();
            Products_Supplier        prod_supp          = null;;
            SqlConnection            connection         = TravelExpertConnect.GetConnection();
            string selectStatement = "select SupName, Products_Suppliers.SupplierId, ProductSupplierId " +
                                     "from Products_Suppliers, Suppliers " +
                                     "where ProductId = @ProdId " +
                                     "and Suppliers.SupplierId = Products_Suppliers.SupplierId " +
                                     "order by SupName";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

            selectCommand.Parameters.AddWithValue("@ProdId", prodId);

            try
            {
                connection.Open();
                SqlDataReader dr = selectCommand.ExecuteReader();
                while (dr.Read())
                {
                    prod_supp = new Products_Supplier();
                    prod_supp.ProductSupplierId = (int)dr["ProductSupplierId"];
                    prod_supp.SupplierId        = (int)dr["SupplierId"];
                    prod_supp.SupName           = dr["SupName"].ToString();
                    products_Suppliers.Add(prod_supp);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(products_Suppliers);
        }
Ejemplo n.º 15
0
        /// <summary>
        /// Delete Product Supplier
        /// </summary>
        /// <param name="prod_supp"></param>
        /// <returns></returns>
        public static bool DeleteProdSupplier(Products_Supplier prod_supp)
        {
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        deleteStatement = "DELETE FROM Products_Suppliers " +
                                            "WHERE ProductSupplierId = @ProductSupplierId " + // to identify the product to be  deleted
                                            "AND SupplierId = @SupplierId " +
                                            "AND ProductId = @ProductId ";                    // remaining conditions - to ensure optimistic concurrency


            SqlCommand cmd = new SqlCommand(deleteStatement, connection);

            cmd.Parameters.AddWithValue("@ProductSupplierId", prod_supp.ProductSupplierId);
            cmd.Parameters.AddWithValue("@SupplierId", prod_supp.SupplierId);
            cmd.Parameters.AddWithValue("@ProductId", prod_supp.ProductId);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 16
0
        /// <summary>
        /// Get package with a specific id
        /// </summary>
        /// <param name="packageId"></param>
        /// <returns></returns>
        public static Package GetPackage(int packageId)
        {
            Package       pack            = null;
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        selectStatement = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
                                            "FROM Packages " +
                                            "WHERE PackageId = @PackageId";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

            selectCommand.Parameters.AddWithValue("@PackageId", packageId);

            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
                if (reader.Read()) // found a package
                {
                    pack                  = new Package();
                    pack.PackageId        = (int)reader["PackageId"];
                    pack.Name             = reader["PkgName"].ToString();
                    pack.StartDate        = reader["PkgStartDate"] as DateTime?;
                    pack.EndDate          = reader["PkgEndDate"] as DateTime?;
                    pack.Desc             = reader["PkgDesc"].ToString();
                    pack.BasePrice        = (Decimal)reader["PkgBasePrice"];
                    pack.AgencyCommission = reader["PkgAgencyCommission"] as Decimal?;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(pack);
        }
Ejemplo n.º 17
0
        /// <summary>
        /// Updates existing package_product_supplier
        /// </summary>
        /// <param name="oldPackProdSupp">data before update</param>
        /// <param name="newPackProdSupp">new data for the update</param>
        /// <returns>indicator of success</returns>
        public static bool UpdateSupplier(Packages_Products_Supplier oldPackProdSupp, Packages_Products_Supplier newPackProdSupp)
        {
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        updateStatement = "UPDATE Packages_Products_Suppliers " +
                                            "SET PackageId = @NewPackageId, " +
                                            "ProductSupplierId = @NewProductSupplierId " +
                                            "WHERE PackageId = @OldPackageId " +
                                            "AND ProductSupplierId = @OldProductSupplierId";
            SqlCommand cmd = new SqlCommand(updateStatement, connection);

            cmd.Parameters.AddWithValue("@NewProductSupplierId", newPackProdSupp.ProductSupplierId);
            cmd.Parameters.AddWithValue("@NewPackageId", newPackProdSupp.PackageId);
            cmd.Parameters.AddWithValue("@OldProductSupplierId", oldPackProdSupp.ProductSupplierId);
            cmd.Parameters.AddWithValue("@OldPackageId", oldPackProdSupp.PackageId);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 18
0
        /// <summary>
        /// Get all packages
        /// </summary>
        /// <param name="packageId"></param>
        /// <returns>List of Packages</returns>
        public static List <Package> GetAllPackages()
        {
            List <Package> packages        = new List <Package>();
            Package        pack            = null;
            SqlConnection  connection      = TravelExpertConnect.GetConnection();
            string         selectStatement = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
                                             "FROM Packages ORDER BY PackageId";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader();
                while (reader.Read()) // found a package
                {
                    pack                  = new Package();
                    pack.PackageId        = (int)reader["PackageId"];
                    pack.Name             = reader["PkgName"].ToString();
                    pack.StartDate        = reader["PkgStartDate"] as DateTime?;
                    pack.EndDate          = reader["PkgEndDate"] as DateTime?;
                    pack.Desc             = reader["PkgDesc"].ToString();
                    pack.BasePrice        = (Decimal)reader["PkgBasePrice"];
                    pack.AgencyCommission = reader["PkgAgencyCommission"] as Decimal?;
                    pack.packProd         = Packages_Products_SuppliersDB.GetPackages_Product_Suppliers(pack.PackageId);
                    packages.Add(pack);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(packages);
        }
Ejemplo n.º 19
0
        /// <summary>
        /// Gets all products suppliers and returns a list
        /// </summary>
        /// <returns></returns>
        public static List <Products_Supplier> GetAllProduct_Suppliers()
        {
            List <Products_Supplier> products_Suppliers = new List <Products_Supplier>();
            Products_Supplier        prod_supp          = null;;
            SqlConnection            connection         = TravelExpertConnect.GetConnection();
            string selectStatement = "SELECT ProductSupplierId, Products_Suppliers.SupplierId, Products_Suppliers.ProductId, SupName, ProdName " +
                                     "From Products_Suppliers, Suppliers, Products " +
                                     "Where Products_Suppliers.ProductId = Products.ProductId " +
                                     "AND Products_Suppliers.SupplierId = Suppliers.SupplierId";
            SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

            try
            {
                connection.Open();
                SqlDataReader dr = selectCommand.ExecuteReader();
                while (dr.Read())
                {
                    prod_supp = new Products_Supplier();
                    prod_supp.ProductSupplierId = (int)dr["ProductSupplierId"];
                    prod_supp.SupplierId        = (int)dr["SupplierId"];
                    prod_supp.ProductId         = (int)dr["ProductId"];
                    prod_supp.ProdName          = dr["ProdName"].ToString();
                    prod_supp.SupName           = dr["SupName"].ToString();
                    products_Suppliers.Add(prod_supp);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(products_Suppliers);
        }
Ejemplo n.º 20
0
        /// <summary>
        /// Updates existing package
        /// </summary>
        /// <param name="oldPack">data before update</param>
        /// <param name="newPack">new data for the update</param>
        /// <returns>indicator of success</returns>
        public static bool UpdatePackage(Package oldPack, Package newPack)
        {
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        updateStatement = "UPDATE Packages " +
                                            "SET PkgName = @NewName, " +
                                            "    PkgStartDate = @NewPkgStartDate, " +
                                            "    PkgEndDate = @NewPkgEndDate, " +
                                            "    PkgDesc = @NewPkgDesc, " +
                                            "    PkgBasePrice = @NewPkgBasePrice, " +
                                            "    PkgAgencyCommission = @NewPkgAgencyCommission " +
                                            "WHERE PackageId = @OldPackageId " +
                                            "AND PkgName = @OldPkgName " + // remaining conditions - to ensure optimistic concurrency
                                            "AND (PkgStartDate = @OldPkgStartDate " +
                                            "OR PkgStartDate IS NULL) " +  //Database null values can only be tested with IS NULL
                                            "AND (PkgEndDate = @OldPkgEndDate " +
                                            "OR PkgEndDate IS NULL) " +    //Database null values can only be tested with IS NULL
                                            "AND PkgDesc = @OldPkgDesc " +
                                            "AND PkgBasePrice = @OldPkgBasePrice " +
                                            "AND (PkgAgencyCommission = @OldPkgAgencyCommission " +
                                            "OR PkgAgencyCommission IS NULL)"; //Database null values can only be tested with IS NULL
            SqlCommand cmd = new SqlCommand(updateStatement, connection);

            cmd.Parameters.AddWithValue("@NewName", newPack.Name);
            cmd.Parameters.AddWithValue("@NewPkgDesc", newPack.Desc);
            cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPack.BasePrice);
            cmd.Parameters.AddWithValue("@OldPackageId", oldPack.PackageId);
            cmd.Parameters.AddWithValue("@OldPkgName", oldPack.Name);
            cmd.Parameters.AddWithValue("@OldPkgDesc", oldPack.Desc);
            cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPack.BasePrice);

            //This block of code specifies passing a nullable type as a parameter to SQL for StartDate
            SqlParameter newStartDateParam = new SqlParameter("@NewPkgStartDate", newPack.StartDate == null ?
                                                              (Object)DBNull.Value : newPack.StartDate);

            newStartDateParam.IsNullable = true;
            newStartDateParam.Direction  = ParameterDirection.Input;
            newStartDateParam.SqlDbType  = SqlDbType.DateTime;
            cmd.Parameters.Add(newStartDateParam);

            //This block of code specifies passing a nullable type as a parameter to SQL for endDate
            SqlParameter newEndDateParam = new SqlParameter("@NewPkgEndDate", newPack.EndDate == null ?
                                                            (Object)DBNull.Value : newPack.EndDate);

            newEndDateParam.IsNullable = true;
            newEndDateParam.Direction  = ParameterDirection.Input;
            newEndDateParam.SqlDbType  = SqlDbType.DateTime;
            cmd.Parameters.Add(newEndDateParam);

            //This block of code specifies passing a nullable type as a parameter to SQL for Agencycommision
            SqlParameter newPkgAgencyParam = new SqlParameter("@NewPkgAgencyCommission", newPack.AgencyCommission == null ?
                                                              (Object)DBNull.Value : newPack.AgencyCommission);

            newPkgAgencyParam.IsNullable = true;
            newPkgAgencyParam.Direction  = ParameterDirection.Input;
            newPkgAgencyParam.SqlDbType  = SqlDbType.Money;
            cmd.Parameters.Add(newPkgAgencyParam);

            //This block of code specifies passing a nullable type as a parameter to SQL for StartDate
            SqlParameter oldStartDateParam = new SqlParameter("@OldPkgStartDate", oldPack.StartDate == null ?
                                                              (Object)DBNull.Value : oldPack.StartDate);

            oldStartDateParam.IsNullable = true;
            oldStartDateParam.Direction  = ParameterDirection.Input;
            oldStartDateParam.SqlDbType  = SqlDbType.DateTime;
            cmd.Parameters.Add(oldStartDateParam);

            //This block of code specifies passing a nullable type as a parameter to SQL for endDate
            SqlParameter oldEndDateParam = new SqlParameter("@OldPkgEndDate", oldPack.EndDate == null ?
                                                            (Object)DBNull.Value : oldPack.EndDate);

            oldEndDateParam.IsNullable = true;
            oldEndDateParam.Direction  = ParameterDirection.Input;
            oldEndDateParam.SqlDbType  = SqlDbType.DateTime;
            cmd.Parameters.Add(oldEndDateParam);

            //This block of code specifies passing a nullable type as a parameter to SQL for Agencycommision
            SqlParameter oldPkgAgencyParam = new SqlParameter("@OldPkgAgencyCommission", oldPack.AgencyCommission == null ?
                                                              (Object)DBNull.Value : oldPack.AgencyCommission);

            oldPkgAgencyParam.IsNullable = true;
            oldPkgAgencyParam.Direction  = ParameterDirection.Input;
            oldPkgAgencyParam.SqlDbType  = SqlDbType.Money;
            cmd.Parameters.Add(oldPkgAgencyParam);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
Ejemplo n.º 21
0
        /// <summary>
        /// Deletes the passed package from the database
        /// </summary>
        /// <param name="pack"></param>
        /// <returns></returns>
        public static bool DeletePackage(Package pack)
        {
            SqlConnection connection      = TravelExpertConnect.GetConnection();
            string        deleteStatement =
                "delete Packages_Products_Suppliers from Packages_Products_Suppliers " +
                "inner join Products_Suppliers on Products_Suppliers.ProductSupplierId = Packages_Products_Suppliers.ProductSupplierId " +
                "inner join Suppliers on Products_Suppliers.SupplierId = Suppliers.SupplierId " +
                "where Packages_Products_Suppliers.PackageId = @PackageId " +
                //first part deletes the link to package product supplier table if it exists
                //second part deltes the actual package
                "DELETE FROM Packages " +
                "WHERE PackageId = @PackageId " +                // to Identify the package to be  deleted
                "AND PkgName = @PkgName " +                      // remaining conditions - to ensure optimistic concurrency
                "AND (PkgStartDate = @PkgStartDate " +
                "OR PkgStartDate IS NULL) " +                    //Database null values can only be tested with IS NULL
                "AND (PkgStartDate = @PkgStartDate " +
                "OR PkgStartDate IS NULL) " +                    //Database null values can only be tested with IS NULL
                "AND PkgDesc = @PkgDesc " +
                "AND PkgBasePrice = @PkgBasePrice " +
                "AND (PkgAgencyCommission = @PkgAgencyCommission " +
                "OR PkgAgencyCommission IS NULL)";                      //Database null values can only be tested with IS NULL

            SqlCommand cmd = new SqlCommand(deleteStatement, connection);

            cmd.Parameters.AddWithValue("@PackageId", pack.PackageId);
            cmd.Parameters.AddWithValue("@PkgName", pack.Name);
            cmd.Parameters.AddWithValue("@PkgDesc", pack.Desc);
            cmd.Parameters.AddWithValue("@PkgBasePrice", pack.BasePrice);

            //This block of code specifies passing a nullable type as a parameter to SQL for StartDate
            SqlParameter startDateParam = new SqlParameter("@PkgStartDate", pack.StartDate == null ?
                                                           (Object)DBNull.Value : pack.StartDate);

            startDateParam.IsNullable = true;
            startDateParam.Direction  = ParameterDirection.Input;
            startDateParam.SqlDbType  = SqlDbType.DateTime;
            cmd.Parameters.Add(startDateParam);

            //This block of code specifies passing a nullable type as a parameter to SQL for endDate
            SqlParameter endDateParam = new SqlParameter("@PkgEndDate", pack.EndDate == null ?
                                                         (Object)DBNull.Value : pack.EndDate);

            endDateParam.IsNullable = true;
            endDateParam.Direction  = ParameterDirection.Input;
            endDateParam.SqlDbType  = SqlDbType.DateTime;
            cmd.Parameters.Add(endDateParam);

            //This block of code specifies passing a nullable type as a parameter to SQL for Agencycommision
            SqlParameter pkgAgencyParam = new SqlParameter("@PkgAgencyCommission", pack.AgencyCommission == null ?
                                                           (Object)DBNull.Value : pack.AgencyCommission);

            pkgAgencyParam.IsNullable = true;
            pkgAgencyParam.Direction  = ParameterDirection.Input;
            pkgAgencyParam.SqlDbType  = SqlDbType.Decimal;
            cmd.Parameters.Add(pkgAgencyParam);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }