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(); } }