public static List <Booking> GetAllBookings()
{
// get all products for the combo box
List <Booking> bookingcodes = new List <Booking>();
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement
= "SELECT BookingId FROM Bookings";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Booking b = new Booking();
b.BookingId = Convert.ToInt32(reader["BookingId"]);
bookingcodes.Add(b);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(bookingcodes);
}
public static int AddPackages(Packages package)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string insertStatement = "Insert package " +
"(PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission) " +
"VALUES (@pkgname, @pkgstartdate, @pkgenddate, @pkgDesc, @pkgbaseprice, @pkgagencycommission)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("@pkgname", package.PkgName);
insertCommand.Parameters.AddWithValue("@pkgstartdate", package.PkgStartDate);
insertCommand.Parameters.AddWithValue("@pkgenddate", package.PkgEndDate);
insertCommand.Parameters.AddWithValue("@pkgDesc", package.PkgDesc);
insertCommand.Parameters.AddWithValue("@pkgbaseprice", package.PkgBasePrice);
insertCommand.Parameters.AddWithValue("@pkgagencycommission", package.PkgAgencyCommission);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
//based on the assumption that the packages with auto increment when created
string selectStatement = "SELECT IDENT_CURRENT('Packages') FROM Packages";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
int packageId = Convert.ToInt32(selectCommand.ExecuteScalar());
return(packageId);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static Customer GetCustomerByEmail(string customerEmail)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement =
"SELECT CustomerID, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, " +
"CustHomePhone, CustBusPhone, CustEmail, AgentID FROM Customers WHERE CustEmail = @custEmail";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@custEmail", customerEmail);
try
{
connection.Open();
SqlDataReader custReader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (custReader.Read())
{
Customer customer = new Customer();
customer.CustomerID = (int)custReader["CustomerID"];
customer.CustFirstName = (string)custReader["CustFirstName"];
customer.CustLastName = (string)custReader["CustLastName"];
customer.CustAddress = (string)custReader["CustAddress"];
customer.CustCity = (string)custReader["CustCity"];
customer.CustProv = (string)custReader["CustProv"];
customer.CustPostal = (string)custReader["CustPostal"];
customer.CustCountry = (string)custReader["CustCountry"];
customer.CustHomePhone = (string)custReader["CustHomePhone"];
customer.CustBusPhone = (string)custReader["CustBusPhone"];
customer.CustEmail = (string)custReader["CustEmail"];
customer.AgentId = (int)custReader["AgentID"];
return(customer);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static int AddCustomer(Customer customer)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string insertStatement =
"INSERT Customers " +
"(CustFirstName,CustLastName, CustAddress, CustCity, @CustProv, @CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone) " +
"VALUES (@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProv, @CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone)";
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue(
"@CustFirstName", customer.CustFirstName);
insertCommand.Parameters.AddWithValue(
"@CustLastName", customer.CustLastName);
insertCommand.Parameters.AddWithValue(
"@CustAddress", customer.CustAddress);
insertCommand.Parameters.AddWithValue(
"@CustCity", customer.CustCity);
insertCommand.Parameters.AddWithValue(
"@CustProv", customer.CustProv);
insertCommand.Parameters.AddWithValue(
"@CustPostal", customer.CustPostal);
insertCommand.Parameters.AddWithValue(
"@CustCountry", customer.CustCountry);
insertCommand.Parameters.AddWithValue(
"@CustHomePhone", customer.CustHomePhone);
insertCommand.Parameters.AddWithValue(
"@CustBusPhone", customer.CustBusPhone);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement =
"SELECT IDENT_CURRENT('Customers') FROM Customers";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
int customerID = Convert.ToInt32(selectCommand.ExecuteScalar());
return(customerID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static Booking GetBooking(int BookingId)
{
//setting up sql connection
SqlConnection connection = TravelExpertsDB.GetConnection();
//sql code for selecting data
string selectStatement
= "SELECT BookingId, BookingDate, BookingNo, TravelerCount, CustomerId, TripTypeId, PackageId "
+ "FROM Bookings "
+ "WHERE BookingId = @BookingId";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("@BookingId", BookingId);
try
{
connection.Open();
SqlDataReader bookReader =
selectCommand.ExecuteReader(CommandBehavior.SingleRow);
// grab each columns and assigning to variables
if (bookReader.Read())
{
Booking booking = new Booking();
booking.BookingId = (int)bookReader["BookingId"];
booking.BookingDate = bookReader["BookingDate"].ToString();
booking.BookingNo = bookReader["BookingNo"].ToString();
booking.TravelerCount = (int)bookReader["TravelerCount"];
booking.CustomerId = (int)bookReader["CustomerId"];
booking.TripTypeId = bookReader["TripTypeId"].ToString();
booking.PackageId = (int)bookReader["PackageId"];
return(booking);
}
else
{
return(null);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
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);
}
public static List <Customer> GetAllCustomers()
{
List <Customer> customers = new List <Customer>(); // empty list
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement =
"SELECT CustomerID, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, " +
"CustHomePhone, CustBusPhone, CustEmail, AgentID FROM Customers ";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader custReader = selectCommand.ExecuteReader();
while (custReader.Read())
{
Customer customer = new Customer();
customer.CustomerID = (int)custReader["CustomerID"];
customer.CustFirstName = (string)custReader["CustFirstName"];
customer.CustLastName = (string)custReader["CustLastName"];
customer.CustAddress = (string)custReader["CustAddress"];
customer.CustCity = (string)custReader["CustCity"];
customer.CustProv = (string)custReader["CustProv"];
customer.CustPostal = (string)custReader["CustPostal"];
customer.CustCountry = (string)custReader["CustCountry"];
customer.CustHomePhone = (string)custReader["CustHomePhone"];
customer.CustBusPhone = (string)custReader["CustBusPhone"];
customer.CustEmail = (string)custReader["CustEmail"];
customer.AgentId = (int)custReader["AgentID"];
customers.Add(customer);
}
custReader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(customers);
}
public List <Packages> GetAllPackages()
{
List <Packages> packages = new List <Packages>();
SqlConnection connection = TravelExpertsDB.GetConnection();
string selectStatement = "SELECT PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " +
"FROM Packages";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
Packages p = new Packages();
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"]);
packages.Add(p);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(packages);
}
public static bool DeleteCustomer(Customer customer)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string deleteStatement =
"DELETE FROM Customers " +
"WHERE CustFirstName = @CustFirstName " +
"CustLastName = @CustLastName " +
"AND CustAddress = @CustAddress " +
"AND CityCity = @CityCity " +
"AND CustProv = @CustProv " +
"AND CustPostal = @CustPostal " +
"AND CustCountry = @CustCountry " +
"AND CustHomePhone = @CustHomePhone " +
"AND CustBusPhone = @CustBusPhone " +
"AND CustEmail = @CustEmail " +
"AND AgentId = @AgentId";
SqlCommand deleteCommand =
new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue(
"@CustFirstName", customer.CustFirstName);
deleteCommand.Parameters.AddWithValue(
"@CustLastName", customer.CustLastName);
deleteCommand.Parameters.AddWithValue(
"@CustAddress", customer.CustAddress);
deleteCommand.Parameters.AddWithValue(
"@CustCity", customer.CustCity);
deleteCommand.Parameters.AddWithValue(
"@CustProv", customer.CustProv);
deleteCommand.Parameters.AddWithValue(
"@CustPostal", customer.CustPostal);
deleteCommand.Parameters.AddWithValue(
"@CustCountry", customer.CustCountry);
deleteCommand.Parameters.AddWithValue(
"@CustHomePhone", customer.CustHomePhone);
deleteCommand.Parameters.AddWithValue(
"@CustBusPhone", customer.CustBusPhone);
deleteCommand.Parameters.AddWithValue(
"@CustEmail", customer.CustEmail);
deleteCommand.Parameters.AddWithValue(
"@AgentId", customer.AgentId);
try
{
connection.Open();
int count = deleteCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool UpdateCustomer(Customer original_customer,
Customer customer)
{
SqlConnection connection = TravelExpertsDB.GetConnection();
string updateStatement =
"UPDATE Customers SET " +
"CustFirstName = @CustFirstName, " +
"CustLastName = @CustLastName, " +
"CustAddress = @CustAddress, " +
"CustCity = @CustCity, " +
"CustProv = @CustProv, " +
"CustPostal = @CustPostal, " +
"CustCountry = @CustCountry, " +
"CustHomePhone = @CustHomePhone, " +
"CustBusPhone = @CustBusPhone, " +
"CustEmail = @CustEmail " +
//"AgentId = @AgentId " +
"WHERE CustFirstName = @original_CustFirstName " +
"AND CustLastName = @original_CustLastName " +
"AND CustAddress = @original_CustAddress " +
"AND CustCity = @original_CustCity " +
"AND CustProv = @original_CustProv " +
"AND CustPostal = @original_CustPostal " +
"AND CustCountry = @original_CustCountry " +
"AND CustHomePhone = @original_CustHomePhone " +
"AND CustBusPhone = @original_CustBusPhone " +
"AND CustEmail = @original_CustEmail ";
//"AND AgentId = @original_AgentId ";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@CustFirstName", customer.CustFirstName);
updateCommand.Parameters.AddWithValue(
"@CustLastName", customer.CustLastName);
updateCommand.Parameters.AddWithValue(
"@CustAddress", customer.CustAddress);
updateCommand.Parameters.AddWithValue(
"@CustCity", customer.CustCity);
updateCommand.Parameters.AddWithValue(
"@CustProv", customer.CustProv);
updateCommand.Parameters.AddWithValue(
"@CustPostal", customer.CustPostal);
updateCommand.Parameters.AddWithValue(
"@CustCountry", customer.CustCountry);
updateCommand.Parameters.AddWithValue(
"@CustHomePhone", customer.CustHomePhone);
updateCommand.Parameters.AddWithValue(
"@CustBusPhone", customer.CustBusPhone);
updateCommand.Parameters.AddWithValue(
"@CustEmail", customer.CustEmail);
//updateCommand.Parameters.AddWithValue(
// "@AgentId", newCustomer.AgentId);
updateCommand.Parameters.AddWithValue(
"@original_CustFirstName", original_customer.CustFirstName);
updateCommand.Parameters.AddWithValue(
"@original_CustLastName", original_customer.CustLastName);
updateCommand.Parameters.AddWithValue(
"@original_CustAddress", original_customer.CustAddress);
updateCommand.Parameters.AddWithValue(
"@original_CustCity", original_customer.CustCity);
updateCommand.Parameters.AddWithValue(
"@original_CustProv", original_customer.CustProv);
updateCommand.Parameters.AddWithValue(
"@original_CustPostal", original_customer.CustPostal);
updateCommand.Parameters.AddWithValue(
"@original_CustCountry", original_customer.CustCountry);
updateCommand.Parameters.AddWithValue(
"@original_CustHomePhone", original_customer.CustHomePhone);
updateCommand.Parameters.AddWithValue(
"@original_CustBusPhone", original_customer.CustBusPhone);
updateCommand.Parameters.AddWithValue(
"@original_CustEmail", original_customer.CustEmail);
//updateCommand.Parameters.AddWithValue(
// "@original_AgentId", original_customer.AgentId);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
