/// <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 }
/// <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 }