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