public static List <ItemsBought> GetItemsBought(int CustomerId)
{
List <ItemsBought> purchases = new List <ItemsBought>();
using (SqlConnection connection = TravelExpertsDB.GetConnection())
{
connection.Open();
string query = "SELECT Description,TravelerCount, Destination,TripStart, TripEnd, BasePrice " +
"FROM Bookings " +
"INNER JOIN BookingDetails on Bookings.BookingId = BookingDetails.BookingId " +
"WHERE Bookings.CustomerId = @CustomerID;"; // Placeholder
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@CustomerId", CustomerId);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ItemsBought purchase = new ItemsBought();
purchase.Description = reader["Description"].ToString();
purchase.TravelerCount = Convert.ToInt32(reader["TravelerCount"]);
purchase.Destination = reader["Destination"].ToString();
purchase.TripStart = Convert.ToDateTime(reader["TripStart"]);
purchase.TripEnd = Convert.ToDateTime(reader["TripEnd"]);
purchase.Price = Convert.ToDouble(reader["BasePrice"]);
purchases.Add(purchase);
}
}
}
}
return(purchases);
}
public static List <Packages> GetPackages()
{
// Return List
List <Packages> packageList = new List <Packages>();
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement
= "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission "
+ "FROM Packages;";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader packageReader = selectCommand.ExecuteReader();
while (packageReader.Read())
{
Packages p = new Packages(); //Creating a Packages object.
p.PackageId = (int)packageReader["PackageId"];
p.PkgName = (string)packageReader["PkgName"];
if (packageReader["PkgStartDate"] == DBNull.Value)
{
p.PkgStartDate = null;
}
else
{
p.PkgStartDate = (DateTime)(packageReader["PkgStartDate"]);
}
if (packageReader["PkgStartDate"] == DBNull.Value)
{
p.PkgEndDate = null;
}
else
{
p.PkgEndDate = (DateTime)packageReader["PkgEndDate"];
}
if (packageReader["PkgDesc"] == DBNull.Value)
{
p.PkgDesc = null;
}
else
{
p.PkgDesc = (string)packageReader["PkgDesc"];
}
p.PkgBasePrice = (decimal)packageReader["PkgBasePrice"];
if (packageReader["PkgAgencyCommission"] == DBNull.Value)
{
p.PkgAgencyCommission = null;
}
else
{
p.PkgAgencyCommission = (decimal)packageReader["PkgAgencyCommission"];
}
packageList.Add(p);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packageList);
}