public List <PackagesWBooking> GetPackagesWithBookingDate(int bookingId)
{
//create a list using the packages with dates objects
List <PackagesWBooking> packagesWBookings = new List <PackagesWBooking>();
SqlConnection connection = TravelExpertsDB.GetConnection();
//select statement with inner join to get the booking date
string selectStatement = "SELECT PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission, BookingDate " +
"FROM Packages p INNER JOIN Bookings b on b.PackageId = p.PackageId Where BookingId = @bookingId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@bookingId", bookingId);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
//while reader is executing
while (reader.Read())
{
//create a new package with booking object
PackagesWBooking p = new PackagesWBooking();
//fill the object values with using the reader and converting to proper data types
p.PkgName = reader["PkgName"].ToString();
p.PkgStartDate = reader["PkgStartDate"].ToString();
p.PkgEndDate = reader["PkgEndDate"].ToString();
p.PkgDesc = reader["PkgDesc"].ToString();
p.PkgBasePrice = Convert.ToDouble(reader["PkgBasePrice"]);
p.PkgAgencyCommission = Convert.ToDouble(reader["PkgAgencyCommission"]);
p.BookingDate = reader["BookingDate"].ToString();
//add new object to the list created
packagesWBookings.Add(p);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
//return the list
return(packagesWBookings);
}
public List <PackagesWBooking> GetPackagesFromCustomers(int customerId)
{
List <PackagesWBooking> packagesWBooking = new List <PackagesWBooking>();
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT BookingNo, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission, BookingDate " +
"FROM Packages p INNER JOIN Bookings b on b.PackageId = p.PackageId " +
"INNER JOIN Customers c on c.CustomerId = b.CustomerId " +
"WHERE c.CustomerId = @customerId";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@customerId", customerId);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
PackagesWBooking p = new PackagesWBooking();
p.BookingNo = reader["BookingNo"].ToString();
p.PkgName = reader["PkgName"].ToString();
p.PkgStartDate = reader["PkgStartDate"].ToString();
p.PkgEndDate = reader["PkgEndDate"].ToString();
p.PkgDesc = reader["PkgDesc"].ToString();
p.PkgBasePrice = Convert.ToDouble(reader["PkgBasePrice"]);
p.PkgAgencyCommission = Convert.ToDouble(reader["PkgAgencyCommission"]);
p.BookingDate = reader["BookingDate"].ToString();
packagesWBooking.Add(p);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packagesWBooking);
}
