public static List <string> GetUserNames()
{
List <string> list = new List <string>();
string username;
// initialize connection
SqlConnection conn = TravelExperts1DB.GetConnection();
string selectString = "SELECT CustUserName FROM Customers";
SqlCommand getCustomer = new SqlCommand(selectString, conn);
try
{
conn.Open(); // create connection
SqlDataReader myReader = getCustomer.ExecuteReader();
while (myReader.Read()) // if the user name matches a customer in the database
{
// create customer object based on database information of the matching user name
username = null;
username = myReader["CustUserName"].ToString();
list.Add(username);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close(); // close connection
}
return(list); // return instance of customer
}
//insert customer record into Travel ExpertsDB
public static bool CreateCustomer(Customer cust)
{
bool inserted = false;
// initialize connection
SqlConnection conn = TravelExperts1DB.GetConnection();
// creation of database INSERT method string
string insertString = "IF NOT EXISTS (SELECT * FROM Customers WHERE CustUserName = @CustUserName) " +
"INSERT INTO Customers " +
"(CustFirstName, CustLastName, CustAddress, CustCity, " +
"CustProv, CustPostal, CustCountry, CustHomePhone, " +
"CustBusPhone, CustEmail, CustUserName, CustPassword) " +
"VALUES(@CustFirstName, @CustLastName, @CustAddress, @CustCity, " +
"@CustProv, @CustPostal, @CustCountry, @CustHomePhone, " +
"@CustBusPhone, @CustEmail, @CustUserName, @CustPassword)";
SqlCommand insertCommand = new SqlCommand(insertString, conn);
insertCommand.Parameters.AddWithValue("@CustFirstName", cust.CustFirstName);
insertCommand.Parameters.AddWithValue("@CustLastName", cust.CustLastName);
insertCommand.Parameters.AddWithValue("@CustAddress", cust.CustAddress);
insertCommand.Parameters.AddWithValue("@CustCity", cust.CustCity);
insertCommand.Parameters.AddWithValue("@CustProv", cust.CustProv);
insertCommand.Parameters.AddWithValue("@CustPostal", cust.CustPostal);
insertCommand.Parameters.AddWithValue("@CustCountry", cust.CustCountry);
insertCommand.Parameters.AddWithValue("@CustHomePhone", cust.CustHomePhone);
insertCommand.Parameters.AddWithValue("@CustBusPhone", cust.CustBusPhone);
insertCommand.Parameters.AddWithValue("@CustEmail", cust.CustEmail);
insertCommand.Parameters.AddWithValue("@CustUserName", cust.CustUserName);
insertCommand.Parameters.AddWithValue("@CustPassword", cust.CustPassword);
try
{
// open connection
conn.Open();
inserted = Convert.ToBoolean(insertCommand.ExecuteNonQuery());
////integer returned by the INSERT SQL command
//int insertSuccess = insertCommand.ExecuteNonQuery();
//if (insertSuccess == 1) // insert successful {
//{
//}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(inserted);
}
public static Customer GetCustomerByUserName(string custUserName)
{
Customer cust = null;
// initialize connection
SqlConnection conn = TravelExperts1DB.GetConnection();
string selectString = "SELECT CustomerId, CustFirstName, CustLastName, CustAddress, CustCity, " +
"CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, " +
"CustUserName, CustPassword FROM Customers WHERE CustUserName = @CustUserName";
SqlCommand getCustomer = new SqlCommand(selectString, conn);
getCustomer.Parameters.AddWithValue("@CustUserName", custUserName);
try
{
conn.Open(); // create connection
SqlDataReader myReader = getCustomer.ExecuteReader();
while (myReader.Read()) // if the user name matches a customer in the database
{
// create customer object based on database information of the matching user name
cust = new Customer();
cust.CustomerId = Convert.ToInt32(myReader["CustomerId"]);
cust.CustFirstName = myReader["CustFirstName"].ToString();
cust.CustLastName = myReader["CustLastName"].ToString();
cust.CustAddress = myReader["CustAddress"].ToString();
cust.CustCity = myReader["CustCity"].ToString();
cust.CustProv = myReader["CustProv"].ToString();
cust.CustPostal = myReader["CustPostal"].ToString();
cust.CustCountry = myReader["CustCountry"].ToString();
cust.CustHomePhone = myReader["CustHomePhone"].ToString();
cust.CustBusPhone = myReader["CustBusPhone"].ToString();
cust.CustEmail = myReader["CustEmail"].ToString();
cust.CustUserName = myReader["CustUserName"].ToString();
cust.CustPassword = myReader["CustPassword"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close(); // close connection
}
return(cust); // return instance of customer
}
public static List <PurchasedItem> PurchasedItemsByCustomer(string custUserName)
{
// initialize connection
SqlConnection conn = TravelExperts1DB.GetConnection();
List <PurchasedItem> purchasedItems = new List <PurchasedItem>();
string selectString = "SELECT BookingDetails.Destination, BookingDetails.[Description], " +
"Products.ProdName, BookingDetails.TripStart, BookingDetails.TripEnd, Bookings.TravelerCount, " +
"Bookings.BookingId, Bookings.BookingDate, TripTypes.TTName, Classes.ClassName, " +
"BookingDetails.BasePrice, Fees.FeeAmt " +
"FROM Customers " +
"JOIN Bookings " +
"ON Customers.CustomerId = Bookings.CustomerId " +
"JOIN BookingDetails " +
"ON Bookings.BookingId = BookingDetails.BookingId " +
"JOIN TripTypes " +
"ON Bookings.TripTypeId = TripTypes.TripTypeId " +
"JOIN Classes " +
"ON BookingDetails.ClassId = Classes.ClassId " +
"JOIN Fees " +
"ON BookingDetails.FeeId = Fees.FeeId " +
"JOIN Products_Suppliers " +
"ON BookingDetails.ProductSupplierId = Products_Suppliers.ProductSupplierId " +
"JOIN Products " +
"ON Products_Suppliers.ProductId = Products.ProductId " +
"WHERE CustUserName = @CustUserName";
SqlCommand getPurchasedItems = new SqlCommand(selectString, conn);
getPurchasedItems.Parameters.AddWithValue("@CustUserName", custUserName);
try
{
conn.Open(); // create connection
SqlDataReader myReader = getPurchasedItems.ExecuteReader();
while (myReader.Read()) // if the user name matches a customer in the database
{
// create purchItem object based on database information of the matching user name
PurchasedItem purchItem = new PurchasedItem();
purchItem.Destination = myReader["Destination"].ToString();
purchItem.Description = myReader["Description"].ToString();
purchItem.ProdName = myReader["ProdName"].ToString();
purchItem.TripStart = Convert.ToDateTime(myReader["TripStart"]);
purchItem.TripEnd = Convert.ToDateTime(myReader["TripEnd"]);
purchItem.TravelerCount = Convert.ToInt32(myReader["TravelerCount"]);
purchItem.BookingId = Convert.ToInt32(myReader["BookingId"]);
purchItem.BookingDate = Convert.ToDateTime(myReader["BookingDate"]);
purchItem.TTName = myReader["TTName"].ToString();
purchItem.ClassName = myReader["ClassName"].ToString();
purchItem.BasePrice = Convert.ToDecimal(myReader["BasePrice"]);
purchItem.FeeAmt = Convert.ToDecimal(myReader["FeeAmt"]);
purchasedItems.Add(purchItem);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close(); // close connection
}
return(purchasedItems); // return instance of customer
}
public static bool UpdateCustomer(Customer Ocust, Customer cust)
{
bool updsuccessful = true; // flag for successful update
// initialize connection
SqlConnection conn = TravelExperts1DB.GetConnection();
// creation of database INSERT method string
string updString = "UPDATE Customers SET CustFirstName = @NCustFirstName, CustLastName = @NCustLastName, " +
"CustAddress = @NCustAddress, CustCity = @NCustCity, CustProv = @NCustProv, " +
"CustPostal = @NCustPostal, CustCountry = @NCustCountry, CustHomePhone = @NCustHomePhone, " +
"CustBusPhone = @NCustBusPhone, CustEmail = @NCustEmail, CustUserName = @NCustUserName, " +
"CustPassword = @NCustPassword WHERE CustFirstName = @OCustFirstName AND " +
"CustLastName = @OCustLastName AND CustPostal = @OCustPostal AND CustCountry = @OCustCountry " +
"AND CustHomePhone = @OCustHomePhone AND CustBusPhone = @OCustBusPhone AND " +
"CustEmail = @OCustEmail AND CustUserName = @OCustUserName AND CustPassword = @OCustPassword";
SqlCommand updCommand = new SqlCommand(updString, conn);
updCommand.Parameters.AddWithValue("@OCustFirstName", Ocust.CustFirstName);
updCommand.Parameters.AddWithValue("@OCustLastName", Ocust.CustLastName);
updCommand.Parameters.AddWithValue("@OCustAddress", Ocust.CustAddress);
updCommand.Parameters.AddWithValue("@OCustCity", Ocust.CustCity);
updCommand.Parameters.AddWithValue("@OCustProv", Ocust.CustProv);
updCommand.Parameters.AddWithValue("@OCustPostal", Ocust.CustPostal);
updCommand.Parameters.AddWithValue("@OCustCountry", Ocust.CustCountry);
updCommand.Parameters.AddWithValue("@OCustHomePhone", Ocust.CustHomePhone);
updCommand.Parameters.AddWithValue("@OCustBusPhone", Ocust.CustBusPhone);
updCommand.Parameters.AddWithValue("@OCustEmail", Ocust.CustEmail);
updCommand.Parameters.AddWithValue("@OCustUserName", Ocust.CustUserName);
updCommand.Parameters.AddWithValue("@OCustPassword", Ocust.CustPassword);
updCommand.Parameters.AddWithValue("@NCustFirstName", cust.CustFirstName);
updCommand.Parameters.AddWithValue("@NCustLastName", cust.CustLastName);
updCommand.Parameters.AddWithValue("@NCustAddress", cust.CustAddress);
updCommand.Parameters.AddWithValue("@NCustCity", cust.CustCity);
updCommand.Parameters.AddWithValue("@NCustProv", cust.CustProv);
updCommand.Parameters.AddWithValue("@NCustPostal", cust.CustPostal);
updCommand.Parameters.AddWithValue("@NCustCountry", cust.CustCountry);
updCommand.Parameters.AddWithValue("@NCustHomePhone", cust.CustHomePhone);
updCommand.Parameters.AddWithValue("@NCustBusPhone", cust.CustBusPhone);
updCommand.Parameters.AddWithValue("@NCustEmail", cust.CustEmail);
updCommand.Parameters.AddWithValue("@NCustUserName", cust.CustUserName);
updCommand.Parameters.AddWithValue("@NCustPassword", cust.CustPassword);
try
{
// open connection
conn.Open();
//integer returned by the UPDATE SQL command
int updSuccess = updCommand.ExecuteNonQuery();
if (updSuccess == 1) // update successful {
{
updsuccessful = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(updsuccessful);
}