public static bool Registercustomer(Customer cst) // returns generated customer id
{
bool reg = false;
SqlConnection connection = TravelExpertsDB.GetConnection();
// prepare the statement
string insertString = "insert into Customers " +
"(CustFirstName, CustLastName, CustPassword, CustAddress, CustCity, CustProv, CustPostal, Custcountry, CustHomePhone, CustBusPhone, CustEmail, CustAbout ) " +
"values(@CustFirstName, @CustLastName, @CustPassword, @CustAddress, @CustCity, @CustProv, @CustPostal, @Custcountry, @CustHomePhone, @CustBusPhone, @CustEmail, @CustAbout )";
SqlCommand insertCommand = new SqlCommand(insertString, connection);
// insertCommand.Parameters.AddWithValue("@PackageId", pkg.PackageId);
insertCommand.Parameters.AddWithValue("@CustFirstName", cst.CustFirstName);
insertCommand.Parameters.AddWithValue("@CustLastName", cst.CustLastName);
insertCommand.Parameters.AddWithValue("@CustPassword", cst.CustPassword);
// insertCommand.Parameters.AddWithValue("@CustGender", cst.CustGender);
insertCommand.Parameters.AddWithValue("@CustAddress", cst.CustAddress);
// insertCommand.Parameters.AddWithValue("@custAddress1", cst.custAddress1);
insertCommand.Parameters.AddWithValue("@CustCity", cst.CustCity);
insertCommand.Parameters.AddWithValue("@CustProv", cst.CustProv);
insertCommand.Parameters.AddWithValue("@CustPostal", cst.CustPostal);
insertCommand.Parameters.AddWithValue("@Custcountry", cst.Custcountry);
insertCommand.Parameters.AddWithValue("@CustBusPhone", cst.CustBusPhone);
insertCommand.Parameters.AddWithValue("@CustHomePhone", cst.CustHomePhone);
insertCommand.Parameters.AddWithValue("@CustEmail", cst.CustEmail);
insertCommand.Parameters.AddWithValue("@CustAbout", cst.CustAbout);
try
{
// open connection
connection.Open();
// execute the statement
int i = insertCommand.ExecuteNonQuery();
if (i == 1) // one record inserted
{
// retrieve customer id from the added record
// string selectString = "select @PackageId " +
// "from packages";
// SqlCommand selectCommand = new SqlCommand(selectString, connection);
// PackageId = Convert.ToInt32(selectCommand.ExecuteScalar()); // (int) does not work!!!
}
reg = true;
}
catch (Exception ex)
{
throw ex; // pass the buck
}
finally
{
connection.Close();
}
return(reg);
}
public static bool checkcustlogin(int CustomerID, string pass)
{
//by default returns not logged in logged value
bool logged = true;
//create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// prepare the statement
string PassString = "select CustomerID, CustPassword from Customers " +
"where CustomerID = @CustomerID";
//SqlCommand object that executes a select statement, page 641 Murach's C# 2015
SqlCommand selectCommand = new SqlCommand(PassString, connection);
selectCommand.Parameters.AddWithValue("@CustomerID", CustomerID);
try
{
// open the connection
connection.Open();
// execute the query
SqlDataReader reader = selectCommand.ExecuteReader();
// process if Technician exists
while (reader.Read())
{
//define values of individual instance
Customer cust1 = new Customer();
cust1.CustPassword = (string)reader["CustPassword"];
cust1.CustomerID = (int)reader["CustomerID"];
if (cust1.CustPassword.Trim() == pass)
{
//loggedin = true;
// Application["CustID"] = CustomerID;
logged = true;
return(logged);
}
else
{
logged = false;
return(logged);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(logged);
}
// Method created by Justin and Mike
public static List <BookingDetails> bookingDetails(int custID)
{
//make empty list
//List<int> bookingIds = new List<int>();
List <BookingDetails> bookingdetails = new List <BookingDetails>();
List <money> vacationCost = new List <money>();
//create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
//create selectcommand
string selectString = "select CustomerId, BookingDetailId, Destination, TripStart, TripEnd, BasePrice, bd.BookingId from Bookings b, BookingDetails bd where b.BookingId=bd.BookingId and CustomerId = @CustomerId";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
selectCommand.Parameters.AddWithValue("@CustomerId", custID);
try
{
connection.Open();
//run the selectCommand and process the results adding Bookings to the list
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.Default);
//process one row at a time
while (reader.Read())
{
BookingDetails Bkd = new BookingDetails();
Bkd.CustomerId = (int)reader["CustomerId"];
Bkd.BookingDetailId = (int)reader["BookingDetailID"];
Bkd.Destination = reader["Destination"].ToString();
DateTime tripEnd = (DateTime)reader["TripEnd"];
Bkd.End = tripEnd.ToString("d");
DateTime tripStart = (DateTime)reader["TripStart"];
Bkd.Start = tripStart.ToString("d");
decimal money = (decimal)reader["BasePrice"];
Bkd.BasePrice = money.ToString("C");
// Bkd.BasePrice = price.ToString("C");
Bkd.BookingId = (int)reader["BookingID"];
bookingdetails.Add(Bkd);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(bookingdetails);
}
public static bool checkcustlogin(int ID, string pass)
{
bool logged = true;
//loggedin = session [false];
//int CustID = (int) Application ["CustID"];
// CustIDD = (int)Application["CustID"] ;
SqlConnection connection = TravelExpertsDB.GetConnection();
// prepare the statement
string PassString = "select CustomerID, CustPassword from Customers";
SqlCommand selectCommand = new SqlCommand(PassString, connection);
try
{
// open the connection
connection.Open();
// execute the query
SqlDataReader reader = selectCommand.ExecuteReader();
// process if Technician exists
while (reader.Read())
{
//define values of individual instance
Customer cust1 = new Customer();
cust1.CustPassword = (string)reader["CustPassword"];
cust1.CustomerID = (int)reader["CustomerID"];
if ((cust1.CustPassword == pass) && (cust1.CustomerID == ID))
{
//loggedin = true;
// Application["CustID"] = ID;
logged = true;
return(logged);
}
else
{
logged = false;
return(logged);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(logged);
}
public static int getnewCustomer()
{
// reference to new (null) order object
Customer cust = new Customer();
//create connection to Northwinds database
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select command that pulls specific values from Orders table for 1 Order
string selectQuery = "SELECT CustomerID FROM Customers WHERE CustomerID = (SELECT MAX(CustomerID) FROM Customers)";
//Represents a stored procedure to execute against a SQL Server database
//info from website: msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand(v=vs.110).aspx
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
//use the store procedure to select the order using the parameter OrderID passed from the main form
// selectCommand.Parameters.AddWithValue("@CustomerID", );
try
{
// open connection
connection.Open();
//DataReader provides a way of reading a forward-only stream of rows from a SQL Server database
//Info from msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx
SqlDataReader reader = selectCommand.ExecuteReader();
//this if statements processes one row at a time
if (reader.Read())
{
//each row is a new object during importing
// cst = new Customer();
// Customer cust;
//define the OrderID and CustomerID of thew new object by using the reader
cust.CustomerID = (int)reader["CustomerID"];
}
//this catches the null exceptions on import
}
catch (Exception ex)
{
// throw exception to the form to handle
throw ex;
}
finally
{
connection.Close();
}
//if no exceptions return the imported order object
return(cust.CustomerID);
}
public static List <money> getPrice(int custID)
{
//make empty list of decimal money values
List <money> vacCost = new List <money>();
//create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
//create selectcommand
string selectString = "select BasePrice, bd.BookingId from Bookings b, BookingDetails bd where b.BookingId = bd.BookingId and CustomerId = @CustomerId";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
selectCommand.Parameters.AddWithValue("@CustomerId", custID);
try
{
connection.Open();
//run the selectCommand and process the results adding Bookings to the list
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.Default);
//process one row at a time
while (reader.Read())
{
money cost = new money();
cost.vacationCost = (decimal)reader["BasePrice"];
vacCost.Add(cost);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(vacCost);
}
public static Customer getCustomer(int CustomerID)
{
// reference to new (null) order object
Customer cust = new Customer();
//create connection to Northwinds database
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select command that pulls specific values from Orders table for 1 Order
string selectQuery = "select * " +
"from Customers " +
"where CustomerID = @CustomerID";
//Represents a stored procedure to execute against a SQL Server database
//info from website: msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand(v=vs.110).aspx
SqlCommand selectCommand = new SqlCommand(selectQuery, connection);
//use the store procedure to select the order using the parameter OrderID passed from the main form
selectCommand.Parameters.AddWithValue("@CustomerID", CustomerID);
try
{
// open connection
connection.Open();
//DataReader provides a way of reading a forward-only stream of rows from a SQL Server database
//Info from msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx
SqlDataReader reader = selectCommand.ExecuteReader();
//this if statements processes one row at a time
if (reader.Read())
{
//each row is a new object during importing
// cst = new Customer();
// Customer cust;
//define the OrderID and CustomerID of thew new object by using the reader
cust.CustomerID = CustomerID;
cust.CustFirstName = (string)reader["CustFirstName"];
cust.CustLastName = (string)reader["CustLastName"];
cust.CustPassword = (string)reader["CustPassword"];
//public string CustGender { get; set; }
cust.CustAddress = (string)reader["CustAddress"];
// public string custAddress1 { get; set; }
cust.CustCity = (string)reader["CustCity"];
cust.CustProv = (string)reader["CustProv"];
cust.CustPostal = (string)reader["CustPostal"];
cust.Custcountry = (string)reader["CustCountry"];
cust.CustHomePhone = (string)reader["CustHomePhone"];
if ((string)reader["CustEmail"] != null)
{
cust.CustEmail = (string)reader["CustEmail"];
}
// cust.CustAbout = (string) reader["CustAbout"];
//if not a null value define the ShippedDate of the new order object
}
//this catches the null exceptions on import
}
catch (Exception ex)
{
// throw exception to the form to handle
throw ex;
}
finally
{
connection.Close();
}
//if no exceptions return the imported order object
return(cust);
}
public static bool updateCustomer(Customer oldCustomer, Customer newCustomer)
{
bool successful = false;
SqlConnection connection = TravelExpertsDB.GetConnection();
string updateString = "update Customers set " +
// "CustomerID = @newCustomerID, " +
"CustFirstName = @newCustFirstName, " +
"CustLastName = @newCustLastName, " +
"CustPassword = @newCustPassword, " +
"CustAddress = @newCustAddress, " +
"CustCity = @newCustCity, " +
"CustProv = @newCustProv, " +
"CustPostal = @newCustPostal, " +
"CustCountry = @newCustCountry, " +
"CustHomePhone = @newCustHomePhone, " +
"CustEmail = @newCustEmail " +
// "CustAbout = @newCustAbout " +
"where " + // update succeeds only if record not changed by other users
"CustomerID = @oldCustomerID and " +
"CustFirstName = @oldCustFirstName and " +
"CustLastName = @oldCustLastName and " +
"CustPassword = @oldCustPassword and " +
"CustAddress = @oldCustAddress and " +
"CustCity = @oldCustCity and " +
"CustProv = @oldCustProv and " +
"CustPostal = @oldCustPostal and " +
"CustCountry = @oldCustCountry and " +
"CustHomePhone = @oldCustHomePhone and " +
"CustEmail = @oldCustEmail ";
// "CustAbout = @oldCustAbout ";
SqlCommand updateCommand = new SqlCommand(updateString, connection);
updateCommand.Parameters.AddWithValue("@oldCustomerID", oldCustomer.CustomerID);
updateCommand.Parameters.AddWithValue("@oldCustFirstName", oldCustomer.CustFirstName);
updateCommand.Parameters.AddWithValue("@oldCustLastName", oldCustomer.CustLastName);
updateCommand.Parameters.AddWithValue("@oldCustPassword", oldCustomer.CustPassword);
updateCommand.Parameters.AddWithValue("@oldCustAddress", oldCustomer.CustAddress);
updateCommand.Parameters.AddWithValue("@oldCustCity", oldCustomer.CustCity);
updateCommand.Parameters.AddWithValue("@oldCustProv", oldCustomer.CustProv);
updateCommand.Parameters.AddWithValue("@oldCustPostal", oldCustomer.CustPostal);
updateCommand.Parameters.AddWithValue("@oldCustCountry", oldCustomer.Custcountry);
updateCommand.Parameters.AddWithValue("@oldCustHomePhone", oldCustomer.CustHomePhone);
updateCommand.Parameters.AddWithValue("@oldCustEmail", oldCustomer.CustEmail);
updateCommand.Parameters.AddWithValue("@newCustomerID", newCustomer.CustomerID);
updateCommand.Parameters.AddWithValue("@newCustFirstName", newCustomer.CustFirstName);
updateCommand.Parameters.AddWithValue("@newCustLastName", newCustomer.CustLastName);
updateCommand.Parameters.AddWithValue("@newCustPassword", newCustomer.CustPassword);
updateCommand.Parameters.AddWithValue("@newCustAddress", newCustomer.CustAddress);
updateCommand.Parameters.AddWithValue("@newCustCity", newCustomer.CustCity);
updateCommand.Parameters.AddWithValue("@newCustProv", newCustomer.CustProv);
updateCommand.Parameters.AddWithValue("@newCustPostal", newCustomer.CustPostal);
updateCommand.Parameters.AddWithValue("@newCustCountry", newCustomer.Custcountry);
updateCommand.Parameters.AddWithValue("@newCustHomePhone", newCustomer.CustHomePhone);
updateCommand.Parameters.AddWithValue("@newCustEmail", newCustomer.CustEmail);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count == 1)
{
successful = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(successful);
}
