Exemple #1
0
        /// <summary>
        /// Retrieves all the packages
        /// </summary>
        /// <returns> the package listcreated using retrieved order information</returns>
        public static List <Package> GetPackage()
        {
            //List to store all the packages
            List <Package> packages = new List <Package>();

            //Package object
            Package pkg = null;

            //initialize db connection.
            //Environment.MachineName provides name of the machine this application running on.
            //Works only if the database is installed on the same machine
            using (SqlConnection connection = TravelExpertsDB.GetConnection(Environment.MachineName))
            {
                //define select query
                string selectQuery = "SELECT PackageId, PkgName, PkgDesc, PkgStartDate, " +
                                     "PkgEndDate, PkgBasePrice, PkgAgencyCommission " +
                                     "FROM Packages";
                using (SqlCommand cmd = new SqlCommand(selectQuery, connection))                //initialize sqlcommand
                {
                    connection.Open();                                                          //open connection
                    using (SqlDataReader reader = cmd.ExecuteReader())                          //initialize reader
                    {
                        while (reader.Read())                                                   //if there is data to be read
                        {
                            pkg             = new Package();                                    //create package object
                            pkg.PackageID   = (int)reader["PackageId"];                         //get the package id
                            pkg.PackageName = reader["PkgName"].ToString();                     //get the package name
                            pkg.BasePrice   = (Decimal)reader["PkgBasePrice"];                  //get the package base price

                            // for the columns that can be null, determine if returns null from data base and set the variables accordingly
                            pkg.PackageDesc      = Convert.IsDBNull(reader["PkgDesc"]) ? "" : reader["PkgDesc"].ToString();
                            pkg.StartDate        = Convert.IsDBNull(reader["PkgStartDate"]) ? (DateTime?)null : (DateTime)reader["PkgStartDate"];
                            pkg.EndDate          = Convert.IsDBNull(reader["PkgEndDate"]) ? (DateTime?)null : (DateTime)reader["PkgEndDate"];
                            pkg.AgencyCommission = Convert.IsDBNull(reader["PkgAgencyCommission"]) ? (decimal?)null : (decimal)reader["PkgAgencyCommission"];
                            packages.Add(pkg);
                        }
                    }
                }
            }
            return(packages);             //return list of package objects
        }
        public static List <Customer> GetCustomers()
        {
            List <Customer> customers = new List <Customer>();
            Customer        customer  = null;

            //initialize db connection.
            //Environment.MachineName provides name of the machine this application running on.
            //Works only if the database is installed on the same machine
            using (SqlConnection connection = TravelExpertsDB.GetConnection(Environment.MachineName))
            {
                //define select query
                string selectQuery = "SELECT CustomerId, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, " +
                                     "CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, AgentId " +
                                     "FROM Customers";
                using (SqlCommand cmd = new SqlCommand(selectQuery, connection))              //initialize sqlcommand
                {
                    connection.Open();                                                        //open connection
                    using (SqlDataReader reader = cmd.ExecuteReader())                        //initialize reader
                    {
                        while (reader.Read())                                                 //if there is data to be read
                        {
                            customer               = new Customer();                          //create customer object
                            customer.CustomerId    = (int)reader["CustomerId"];
                            customer.CustFirstName = reader["CustFirstName"].ToString();
                            customer.CustLastName  = reader["CustLastName"].ToString();
                            customer.CustAddress   = reader["CustAddress"].ToString();
                            customer.CustCity      = reader["CustCity"].ToString();
                            customer.CustProv      = reader["CustProv"].ToString();
                            customer.CustPostal    = reader["CustPostal"].ToString();
                            customer.CustCountry   = reader["CustCountry"].ToString();
                            customer.CustHomePhone = reader["CustHomePhone"].ToString();
                            customer.CustBusPhone  = reader["CustBusPhone"].ToString();
                            customer.CustEmail     = reader["CustEmail"].ToString();
                            customer.AgentId       = (int)reader["AgentId"];
                            customers.Add(customer);
                        }
                    }
                }
            }
            return(customers);             //return list of customers
        }
Exemple #3
0
        /// <summary>
        /// Function to retrieve products included in the user selected package
        /// </summary>
        /// <param name="packageID">Package id</param>
        /// <returns>list of products as an array list</returns>
        public static List <Product> GetProducts(int packageID)
        {
            //List to store products
            List <Product> products = new List <Product>();

            Product product = null;

            //initialize db connection.
            //Environment.MachineName provides name of the machien the application running on.
            //Works only if the database is installed on the same machine
            using (SqlConnection connection = TravelExpertsDB.GetConnection(Environment.MachineName))
            {
                //define select query
                string selectQuery = "SELECT p.ProdName, p.ProductId FROM Packages_Products_Suppliers as pps " +
                                     "inner join Products_Suppliers as ps " +
                                     "on pps.ProductSupplierId = ps.ProductSupplierId " +
                                     "inner join Products as p on ps.ProductId = p.ProductId " +
                                     "where PackageId = @PackageId";
                using (SqlCommand cmd = new SqlCommand(selectQuery, connection))                //initialize sqlcommand
                {
                    cmd.Parameters.AddWithValue("@PackageId", packageID);                       //data bind package id
                    connection.Open();                                                          //open DB connection

                    using (SqlDataReader reader = cmd.ExecuteReader())                          //initialize reader
                    {
                        while (reader.Read())                                                   //if there is data to be read, create product objects and add them to list
                        {
                            product           = new Product();
                            product.ProductId = (int)reader["ProductId"];
                            product.ProdName  = reader["ProdName"].ToString();
                            products.Add(product);
                        }
                    }
                }
            }
            return(products);  //return list of products
        }