//function to add new customer
public static int AddCustomer(Customer cust)
{
int custID = 0;
SqlConnection con = TravelExpertsDB.GetConnection();//create the connection
//create a command string
string insertStatement = "INSERT INTO Customers (CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, UserName, Password )" + " VALUES (@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProv, @CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone, @CustEmail, @UserName, @Password)";
//connect to database and execute the command
SqlCommand cmd = new SqlCommand(insertStatement, con);
//define the command objects values
cmd.Parameters.AddWithValue("@CustFirstName", cust.CustFirstName);
cmd.Parameters.AddWithValue("@CustLastName", cust.CustLastName);
cmd.Parameters.AddWithValue("@CustAddress", cust.CustAddress);
cmd.Parameters.AddWithValue("@CustCity", cust.CustCity);
cmd.Parameters.AddWithValue("@CustProv", cust.CustProv);
cmd.Parameters.AddWithValue("@CustPostal", cust.CustPostal);
cmd.Parameters.AddWithValue("@CustCountry", cust.CustCountry);
cmd.Parameters.AddWithValue("@CustHomePhone", cust.CustHomePhone);
cmd.Parameters.AddWithValue("@CustBusPhone", cust.CustBusPhone);
cmd.Parameters.AddWithValue("@CustEmail", cust.CustEmail);
cmd.Parameters.AddWithValue("@UserName", cust.UserName);
cmd.Parameters.AddWithValue("@Password", cust.Password);
try
{
con.Open(); //open the connection
cmd.ExecuteNonQuery(); //execute command
string selectQuery = "SELECT IDENT_CURRENT('Customers') FROM Customers";
SqlCommand selectCommand = new SqlCommand(selectQuery, con);
custID = Convert.ToInt32(selectCommand.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();//close connection
}
return(custID);
}//end of AddCustomer class
}//end of login function
//function to find customerid through the username for login the existing customer
public static Customer Find(string userName)
{
Customer cust = new Customer();
SqlConnection con = TravelExpertsDB.GetConnection();
string findStatement = "select * from Customers where (UserName=@UserName)";
SqlCommand cmd = new SqlCommand(findStatement, con);
cmd.Parameters.AddWithValue("@UserName", userName);
try
{
con.Open();
//read the row of customer information to get customer id
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
cust.CustomerId = Convert.ToInt32(reader["CustomerId"]);
cust.CustFirstName = reader["CustFirstName"].ToString();
cust.CustLastName = reader["CustLastName"].ToString();
cust.CustAddress = reader["CustAddress"].ToString();
cust.CustCity = reader["CustCity"].ToString();
cust.CustProv = reader["CustProv"].ToString();
cust.CustPostal = reader["CustPostal"].ToString();
cust.CustCountry = reader["CustCountry"].ToString();
cust.CustHomePhone = reader["CustHomePhone"].ToString();
cust.CustBusPhone = reader["CustBusPhone"].ToString();
cust.CustEmail = reader["CustEmail"].ToString();
cust.UserName = reader["UserName"].ToString();
cust.Password = reader["Password"].ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(cust);
}//end of Find function
}//end of checkUserName function
//Login function for existing customer
public static bool Login(string username, string password)
{
int verify;
//create the connection
SqlConnection con = TravelExpertsDB.GetConnection();
//command string to check the customer username and password
string login = "******";
SqlCommand cmd = new SqlCommand(login, con);
cmd.Parameters.AddWithValue("@UserName", username);
cmd.Parameters.AddWithValue("@Password", password);
try
{
con.Open();
//put the result of count in verify
verify = Convert.ToInt32(cmd.ExecuteScalar());
//if any customer exists with those username and password
if (verify > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
}//end of login function
// returns a List of BookingDetail objects from the database
public static List <BookingDetail> GetAllBookingDetails()
{
List <BookingDetail> bookingDetails = new List <BookingDetail>(); // instantiate an empty List of Bookings
SqlConnection conn = TravelExpertsDB.GetConnection(); // instantiate a DB connection object
// prepare the SQL statement
string selectStatement = "SELECT * FROM BookingDetails";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
try
{
conn.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
BookingDetail bookingDetail = new BookingDetail();
bookingDetail.BookingDetailId = (int)dr["BookingDetailId"];
// if necessary convert DB null values to C# nulls
if (dr["ItineraryNo"] == DBNull.Value)
{
bookingDetail.ItineraryNo = null;
}
else
{
bookingDetail.ItineraryNo = (double)dr["ItineraryNo"];
}
if (dr["TripStart"] == DBNull.Value)
{
bookingDetail.TripStart = null;
}
else
{
bookingDetail.TripStart = (DateTime)dr["TripStart"];
}
if (dr["TripEnd"] == DBNull.Value)
{
bookingDetail.TripEnd = null;
}
else
{
bookingDetail.TripEnd = (DateTime)dr["TripEnd"];
}
if (dr["Description"] == DBNull.Value)
{
bookingDetail.Description = null;
}
else
{
bookingDetail.Description = (string)dr["Description"];
}
if (dr["Destination"] == DBNull.Value)
{
bookingDetail.Destination = null;
}
else
{
bookingDetail.Destination = (string)dr["Destination"];
}
if (dr["BasePrice"] == DBNull.Value)
{
bookingDetail.BasePrice = null;
}
else
{
bookingDetail.BasePrice = (decimal)dr["BasePrice"];
}
if (dr["AgencyCommission"] == DBNull.Value)
{
bookingDetail.AgencyCommission = null;
}
else
{
bookingDetail.AgencyCommission = (decimal)dr["AgencyCommission"];
}
if (dr["BookingId"] == DBNull.Value)
{
bookingDetail.BookingId = null;
}
else
{
bookingDetail.BookingId = (int)dr["BookingId"];
}
if (dr["RegionId"] == DBNull.Value)
{
bookingDetail.RegionId = null;
}
else
{
bookingDetail.RegionId = (string)dr["RegionId"];
}
if (dr["ClassId"] == DBNull.Value)
{
bookingDetail.ClassId = null;
}
else
{
bookingDetail.ClassId = (string)dr["ClassId"];
}
if (dr["FeeId"] == DBNull.Value)
{
bookingDetail.FeeId = null;
}
else
{
bookingDetail.FeeId = (string)dr["FeeId"];
}
if (dr["ProductSupplierId"] == DBNull.Value)
{
bookingDetail.ProductSupplierId = null;
}
else
{
bookingDetail.ProductSupplierId = (int)dr["ProductSupplierId"];
}
bookingDetails.Add(bookingDetail);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(bookingDetails);
}
// returns a List of Booking objects from the database
public static List <Booking> GetAllBookings()
{
List <Booking> bookings = new List <Booking>(); // instantiate an empty List of Bookings
SqlConnection conn = TravelExpertsDB.GetConnection(); // instantiate a DB connection object
// prepare the SQL statement
string selectStatement = "SELECT * FROM Bookings";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
try
{
conn.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
Booking booking = new Booking();
booking.BookingId = (int)dr["BookingId"];
// convert DB null values to C# nulls
if (dr["BookingDate"] == DBNull.Value)
{
booking.BookingDate = null;
}
else
{
booking.BookingDate = (DateTime)dr["BookingDate"];
}
if (dr["BookingNo"] == DBNull.Value)
{
booking.BookingNo = null;
}
else
{
booking.BookingNo = (string)(dr["BookingNo"]);
}
if (dr["TravelerCount"] == DBNull.Value)
{
booking.TravelerCount = null;
}
else
{
booking.TravelerCount = (double)(dr["TravelerCount"]);
}
if (dr["CustomerId"] == DBNull.Value)
{
booking.CustomerId = null;
}
else
{
booking.CustomerId = (int)(dr["CustomerId"]);
}
if (dr["TripTypeId"] == DBNull.Value)
{
booking.TripTypeId = null;
}
else
{
booking.TripTypeId = (string)(dr["TripTypeId"]);
}
if (dr["PackageId"] == DBNull.Value)
{
booking.PackageId = null;
}
else
{
booking.PackageId = (int)(dr["PackageId"]);
}
bookings.Add(booking);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(bookings);
}
// return a List of Package objects from the database
public static List <Package> GetAllPackages()
{
List <Package> packages = new List <Package>(); // instantiate an empty List of Bookings
SqlConnection conn = TravelExpertsDB.GetConnection(); // instantiate a DB connection object
// prepare the SQL statement
string selectStatement = "SELECT * FROM Packages";
SqlCommand selectCommand = new SqlCommand(selectStatement, conn);
try
{
conn.Open();
SqlDataReader dr = selectCommand.ExecuteReader();
while (dr.Read())
{
Package package = new Package();
package.PackageId = (int)dr["PackageId"];
package.PkgName = (string)dr["PkgName"];
// if necessary, convert database null values to C# nulls
if (dr["PkgStartDate"] == DBNull.Value)
{
package.PkgStartDate = null;
}
else
{
package.PkgStartDate = (DateTime)dr["PkgStartDate"];
}
if (dr["PkgEndDate"] == DBNull.Value)
{
package.PkgEndDate = null;
}
else
{
package.PkgEndDate = (DateTime)dr["PkgEndDate"];
}
if (dr["PkgDesc"] == DBNull.Value)
{
package.PkgDesc = null;
}
else
{
package.PkgDesc = (string)dr["PkgDesc"];
}
if (dr["PkgBasePrice"] == DBNull.Value)
{
package.PkgBasePrice = null;
}
else
{
package.PkgBasePrice = (decimal)dr["PkgBasePrice"];
}
if (dr["PkgAgencyCommission"] == DBNull.Value)
{
package.PkgAgencyCommission = null;
}
else
{
package.PkgAgencyCommission = (decimal)dr["PkgAgencyCommission"];
}
packages.Add(package);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(packages);
}
}//end of Find function
/*
* author of below code: Hayley Mead
* Updating DataBase from when the customer has to edit their information in the customer profile page
*/
public static bool UpdateCust(Customer oldCust, Customer newCust)
{
SqlConnection connection = TravelExpertsDB.GetConnection();//connection to DB
bool success = true;
//finding record it needs to update "old" and replacing it with the "new" customer info
string update = "UPDATE Customers SET " +
"custFirstName = @NewCustFirstName, " +
"custLastName = @NewCustLastName, " +
"custAddress = @NewCustAddress, " +
"custCity = @NewCustCity, " +
"custProv = @NewCustProv, " +
"CustCountry = @newCustCountry," +
"CustHomePhone = @NewCustHomePhone, " +
"CustBusPhone = @NewCustBusPhone, " +
"CustEmail = @NewCustEmail, " +
"UserName = @NewUserName, " +
"Password = @NewPassword " +
"WHERE CustomerId = @OldCustomerId " +
"AND custFirstName = @OldCustFirstName " + // to identify record to update
"AND custLastName = @OldCustLastName " +
"AND custAddress = @OldCustAddress " +
"AND custCity = @OldCustCity " +
"AND CustProv = @OldCustProv " +
"AND CustPostal = @OldCustPostal " +
"AND CustCountry = @OldCustCountry " +
"AND CustHomePhone = @OldCustHomePhone " +
"AND CustBusPhone = @OldCustBusPhone " +
"AND CustEmail = @OldCustEmail " +
"AND UserName = @OldUserName " +
"AND Password = @OldPassword ";
SqlCommand updateCmd = new SqlCommand(update, connection);
//New
updateCmd.Parameters.AddWithValue("@NewCustFirstName", newCust.CustFirstName);
updateCmd.Parameters.AddWithValue("@NewCustLastName", newCust.CustLastName);
updateCmd.Parameters.AddWithValue("@NewCustAddress", newCust.CustAddress);
updateCmd.Parameters.AddWithValue("@NewCustCity", newCust.CustCity);
updateCmd.Parameters.AddWithValue("@NewCustProv", newCust.CustProv);
updateCmd.Parameters.AddWithValue("@NewCustPostal", newCust.CustPostal);
updateCmd.Parameters.AddWithValue("@NewCustCountry", newCust.CustCountry);
updateCmd.Parameters.AddWithValue("@NewCustHomePhone", newCust.CustHomePhone);
updateCmd.Parameters.AddWithValue("@NewCustBusPhone", newCust.CustBusPhone);
updateCmd.Parameters.AddWithValue("@NewCustEmail", newCust.CustEmail);
updateCmd.Parameters.AddWithValue("@NewUserName", newCust.UserName);
updateCmd.Parameters.AddWithValue("@NewPassword", newCust.Password);
//Old
updateCmd.Parameters.AddWithValue("@OldCustomerId", oldCust.CustomerId);
updateCmd.Parameters.AddWithValue("@OldCustFirstName", oldCust.CustFirstName);
updateCmd.Parameters.AddWithValue("@OldCustLastName", oldCust.CustLastName);
updateCmd.Parameters.AddWithValue("@OldCustAddress", oldCust.CustAddress);
updateCmd.Parameters.AddWithValue("@OldCustCity", oldCust.CustCity);
updateCmd.Parameters.AddWithValue("@OldCustProv", oldCust.CustProv);
updateCmd.Parameters.AddWithValue("@OldCustPostal", oldCust.CustPostal);
updateCmd.Parameters.AddWithValue("@OldCustCountry", oldCust.CustCountry);
updateCmd.Parameters.AddWithValue("@OldCustHomePhone", oldCust.CustHomePhone);
updateCmd.Parameters.AddWithValue("@OldCustBusPhone", oldCust.CustBusPhone);
updateCmd.Parameters.AddWithValue("@OldCustEmail", oldCust.CustEmail);
updateCmd.Parameters.AddWithValue("@OldUserName", oldCust.UserName);
updateCmd.Parameters.AddWithValue("@OldPassword", oldCust.Password);
try
{
connection.Open();
int rowsUpdated = updateCmd.ExecuteNonQuery();
if (rowsUpdated == 0)
{
success = false; //if rows where not updated and success returns false
}
}
catch (Exception ex)//catching all exeptions
{
throw ex;
}
finally
{
connection.Close(); //closing connection
}
return(success); //returning updated Info if it was "true"
}
