/// <summary> /// Miguel Santana /// Created: 2015/02/12 /// Get a list of all HotelGuest Objects /// </summary> ///<remarks> /// Rose Steffensmeier /// Updated: 2015/03/12 /// Added try-catch blocks /// </remarks> /// <param name="hotelGuestID">Optional Parameter to specify a hotel guest to look up</param> /// <returns></returns> public static List <HotelGuest> HotelGuestGet(int?hotelGuestID = null) { SqlConnection conn = DatabaseConnection.GetDatabaseConnection(); var cmdText = "spSelectHotelGuest"; SqlCommand cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@hotelGuestID", hotelGuestID); List <HotelGuest> list = new List <HotelGuest>(); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { list.Add( new HotelGuest( reader.GetInt32(0), //HotelGuestID reader.GetString(1), //FirstName reader.GetString(2), //LastName reader.GetString(3), //Address1 !reader.IsDBNull(4) ? reader.GetString(4) : null, //Address2 new CityState( reader.GetString(5), //Zip reader.GetString(6), //City reader.GetString(7) //State ), !reader.IsDBNull(8) ? reader.GetString(8) : null, //PhoneNumber !reader.IsDBNull(9) ? reader.GetString(9) : null, //EmailAdddress !reader.IsDBNull(10) ? reader.GetString(10) : null, //Room !reader.IsDBNull(11) ? reader.GetString(11) : null, // PIN reader.GetBoolean(12) //Active ) ); } } else { throw new ApplicationException(hotelGuestID == null ? "Could not find any Hotel Guests" : "Hotel Guest ID number did not match any records."); } } catch (SqlException) { throw; } catch (Exception) { throw; } finally { conn.Close(); } return(list); }
/// <summary> /// Pat Banks /// Created: 2015/04/14 /// Verifies the hotel guest pin to sign up for an item listing and create a booking /// </summary> /// <param name="inPIN">The pin to cross reference against HotelGuests</param> /// <returns>Hotel guest that has that pin</returns> public static HotelGuest VerifyHotelGuestPin(string inPIN) { SqlConnection conn = DatabaseConnection.GetDatabaseConnection(); var cmdText = "spSelectHotelGuestByPin"; SqlCommand cmd = new SqlCommand(cmdText, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@guestPIN", inPIN); HotelGuest foundGuest = null; try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { foundGuest = new HotelGuest( reader.GetInt32(0), //HotelGuestID reader.GetString(1), //FirstName reader.GetString(2), //LastName reader.GetString(3), //Address1 !reader.IsDBNull(4) ? reader.GetString(4) : null, //Address2 new CityState( reader.GetString(5), //Zip reader.GetString(6), //City reader.GetString(7) //State ), !reader.IsDBNull(8) ? reader.GetString(8) : null, //PhoneNumber !reader.IsDBNull(9) ? reader.GetString(9) : null, //EmailAdddress !reader.IsDBNull(10) ? reader.GetString(10) : null, //Room !reader.IsDBNull(11) ? reader.GetString(11) : null, // PIN reader.GetBoolean(12) //Active ); } // end while } else { var ax = new ApplicationException("PIN not found"); throw ax; } } catch (SqlException) { var ax = new ApplicationException("PIN not found"); throw ax; } catch (Exception) { throw; } finally { conn.Close(); } return(foundGuest); }
/// <summary> /// Pat Banks /// Created: 2015/04/11 /// Adds a new supplier from an application with supply cost in the supplier table /// Adds User name to the supplier login table. /// </summary> /// <param name="oldApplication">The SupplierApplication object/record to be updated</param> /// <param name="newApplication">The SupplierApplication object with the updated information for the record</param> /// <param name="userName">The username for the supplier whose application is being updated</param> /// <param name="supplyCost">The updated supply cost</param> /// <returns>number of rows affected</returns> public static int UpdateSupplierApplication(SupplierApplication oldApplication, SupplierApplication newApplication, string userName, decimal supplyCost, string password) { var conn = DatabaseConnection.GetDatabaseConnection(); var cmdText = "spInsertSupplierFromApplication"; var cmd = new SqlCommand(cmdText, conn); var rowsAffected = 0; // set command type to stored procedure and add parameters cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserName", userName); cmd.Parameters.AddWithValue("@SupplyCost", supplyCost); cmd.Parameters.AddWithValue("@CompanyName", newApplication.CompanyName); cmd.Parameters.AddWithValue("@CompanyDescription", newApplication.CompanyDescription); cmd.Parameters.AddWithValue("@FirstName", newApplication.FirstName); cmd.Parameters.AddWithValue("@LastName", newApplication.LastName); cmd.Parameters.AddWithValue("@Address1", newApplication.Address1); cmd.Parameters.AddWithValue("@Address2", newApplication.Address2); cmd.Parameters.AddWithValue("@Zip", newApplication.Zip); cmd.Parameters.AddWithValue("@PhoneNumber", newApplication.PhoneNumber); cmd.Parameters.AddWithValue("@EmailAddress", newApplication.EmailAddress); cmd.Parameters.AddWithValue("@ApplicationDate", newApplication.ApplicationDate); cmd.Parameters.AddWithValue("@ApplicationStatus", newApplication.ApplicationStatus); cmd.Parameters.AddWithValue("@LastStatusDate", newApplication.LastStatusDate); cmd.Parameters.AddWithValue("@Remarks", newApplication.Remarks); cmd.Parameters.AddWithValue("@originalApplicationID", oldApplication.ApplicationID); cmd.Parameters.AddWithValue("@originalCompanyName", oldApplication.CompanyName); cmd.Parameters.AddWithValue("@originalCompanyDescription", oldApplication.CompanyDescription); cmd.Parameters.AddWithValue("@originalFirstName", oldApplication.FirstName); cmd.Parameters.AddWithValue("@originalLastName", oldApplication.LastName); cmd.Parameters.AddWithValue("@originalAddress1", oldApplication.Address1); cmd.Parameters.AddWithValue("@originalAddress2", oldApplication.Address2); cmd.Parameters.AddWithValue("@originalZip", oldApplication.Zip); cmd.Parameters.AddWithValue("@originalPhoneNumber", oldApplication.PhoneNumber); cmd.Parameters.AddWithValue("@originalEmailAddress", oldApplication.EmailAddress); cmd.Parameters.AddWithValue("@originalApplicationDate", oldApplication.ApplicationDate); cmd.Parameters.AddWithValue("@originalApplicationStatus", oldApplication.ApplicationStatus); cmd.Parameters.AddWithValue("@originalLastStatusDate", oldApplication.LastStatusDate); cmd.Parameters.AddWithValue("@originalRemarks", oldApplication.Remarks); cmd.Parameters.AddWithValue("@password", password); try { conn.Open(); rowsAffected = cmd.ExecuteNonQuery(); if (rowsAffected == 0) { throw new ApplicationException("Concurrency Violation"); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { conn.Close(); } return(rowsAffected); }