public static int CheckUserID(string customerId) // Check UserID method that checks to see if the CustomerID is in the database
{
int UserID = 0; //Create an empty prodName string
#pragma warning disable CS0436 // Type conflicts with imported type
SqlConnection con = TravelExpertsDB.GetConnection();
#pragma warning restore CS0436 // Type conflicts with imported type
//Create a query that selects the Prodname from the products where productId is given
string selectQuery = "SELECT CustomerID " +
"FROM Customers " +
"WHERE CustomerID = @CustomerID";
SqlCommand selectCommand = new SqlCommand(selectQuery, con);
selectCommand.Parameters.AddWithValue("@CustomerID", customerId);
try
{
con.Open();
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
UserID = Convert.ToInt32(reader["CustomerID"].ToString());//set prod name to what was stored in the reader
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(UserID);//return the product name
}
public static string GetHashPassword(string customerId) // Retrieves the password from database using customerId as argument
{
string hashpass = ""; //Create an empty prodName string
SqlConnection con = TravelExpertsDB.GetConnection();
//Create a query that selects the Prodname from the products where productId is given
string selectQuery = "SELECT CustPassword " +
"FROM Customers " +
"WHERE CustomerID = @CustomerID";
SqlCommand selectCommand = new SqlCommand(selectQuery, con);
selectCommand.Parameters.AddWithValue("@CustomerID", customerId);
try
{
con.Open();
SqlDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
hashpass = (string)reader["CustPassword"];//set prod name to what was stored in the reader
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(hashpass);//return the product name
}
// inserts new customer record into register
public static bool AddCustomer(Customer cust) // returns generated customer id
{
// prepare connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// prepare the statement
string insertString = "insert into Customers " +
"(CustFirstName, CustLastName, CustAddress, CustCity, " +
" CustProv, CustPostal, CustCountry, CustHomePhone, " +
" CustBusPhone, CustEmail, AgentID, CustPassword) " +
"values( @CustFirstName, @CustLastName, @CustAddress, @CustCity, " +
" @CustProv, @CustPostal, @CustCountry, @CustHomePhone, " +
" @CustBusPhone, @CustEmail, @CustAgentID, @CustPassword)";
SqlCommand insertCommand = new SqlCommand(insertString, connection);
//bind with the information given
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.CustProvince);
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("@CustAgentID", cust.AgentID);
insertCommand.Parameters.AddWithValue("@Custpassword", cust.CustPassword);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery(); //for DML statements
//return true the the command was successful
if (count > 0)
{
return(true);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
//close connection
connection.Close();
}
return(false);
}
public static List <Agents> GetAgents()
{
List <Agents> agentlist = new List <Agents>(); //Create an empty list of suppliers
//List<string> agentlistfullName = new List<string>();//Create an empty list of suppliers
Agents agent = null; //Create a null supplier
SqlConnection con = TravelExpertsDB.GetConnection(); //Create a connection to db
//Build the query to access the supplierId and SupName from the Suppliers table
string selectQuery = "SELECT AgentID, AgtFirstName, AgtLastName " +
"FROM Agents";
//Build the selectCommand by giving SqlCommand the query and the connection to the db
SqlCommand selectCommand = new SqlCommand(selectQuery, con);
try
{
con.Open(); //Open the connection
SqlDataReader reader = selectCommand.ExecuteReader(); //Execute the select command and store results in reader
while (reader.Read()) //Read the suppliers if they still exist
{
agent = new Agents(); //Create a new supplier for this iteration
//Add the supplier properties
agent.AgentID = (int)reader["AgentID"];
agent.AgtFirstName = (string)reader["AgtFirstName"];
agent.AgtLastName = (string)reader["AgtlastName"];
agent.AgtFullName = agent.AgtFirstName + " " + agent.AgtLastName;
agentlist.Add(agent);//Add this supplier to the supplier list
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();//Close the connection
}
//foreach (Agents a in agentlist)
//{
// String FullName;
// FullName = agent.AgtFirstName + " " + agent.AgtLastName;
// agentlistfullName.Add(FullName);
//}
return(agentlist);
}
public static List <CustomerPackage> GetCustPackages(int custID)
{
List <CustomerPackage> packageList = new List <CustomerPackage>(); //Create an empty list of incidents
CustomerPackage customerPackage = null; //creae a null incident
SqlConnection con = TravelExpertsDB.GetConnection(); //get a connection to the database
string selectQuery = "SELECT Packages.PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, BookingId, BookingDate, TravelerCount " +
"FROM Packages INNER JOIN Bookings ON Bookings.PackageId = Packages.PackageId " +
"Where CustomerId = @CustomerId";
SqlCommand selectCommand = new SqlCommand(selectQuery, con);//create the select command
selectCommand.Parameters.AddWithValue("@CustomerId", custID);
try
{
con.Open(); //open the connection
SqlDataReader reader = selectCommand.ExecuteReader(); //Execute the query and store it in reader
while (reader.Read()) //read the incidents if they exist
{
//create new incident and add properties to them
customerPackage = new CustomerPackage();
customerPackage.PackageId = (int)reader["PackageId"];
customerPackage.PkgName = (string)reader["PkgName"];
customerPackage.PkgStartDate = reader["PkgStartDate"] as DateTime?;
customerPackage.PkgEndDate = reader["PkgEndDate"] as DateTime?;
customerPackage.PkgDesc = (string)reader["PkgDesc"];
customerPackage.PkgBasePrice = (decimal)reader["PkgBasePrice"];
customerPackage.BookingId = (int)reader["BookingId"];
customerPackage.BookingDate = reader["BookingDate"] as DateTime?;
customerPackage.TravelerCount = Convert.ToInt32(reader["TravelerCount"]);
packageList.Add(customerPackage);//add the incident to the incident list
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close(); //close the connection
}
return(packageList); //return the incident list
}
//get customer info
public static Customer GetCustInfo(int customerId)
{
Customer cust = new Customer(); //create a new customer
SqlConnection con = TravelExpertsDB.GetConnection(); //connect to db
// select values from customers based on ID
string selectString = "SELECT CustomerId, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, "
+ "CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail "
+ "From Customers "
+ "WHERE CustomerId = @CustomerId "
+ "ORDER BY CustFirstName";
//
SqlCommand cmd = new SqlCommand(selectString, con);
cmd.Parameters.AddWithValue("@CustomerId", customerId); // bind customer id
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader(); //execute query
while (reader.Read()) // while the reader is reading
{
cust = new Customer(); //create a new cust and bind the information
cust.CustomerID = (int)reader["CustomerId"];
cust.CustFirstName = reader["CustFirstName"].ToString();
cust.CustLastName = reader["CustLastName"].ToString();
cust.CustAddress = reader["CustAddress"].ToString();
cust.CustCity = reader["CustCity"].ToString();
cust.CustProvince = 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();
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(cust); //return the customer with attached information
}
// get customer information based on ID
public static Customer GetCustInfoFromEmail(string customerEmail)
{
Customer cust = new Customer(); //create a customer
SqlConnection con = TravelExpertsDB.GetConnection();
//get the customer based off of email
string selectString = "SELECT CustomerId, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, "
+ "CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail "
+ "From Customers "
+ "WHERE CustEmail = @CustomerEmail";
SqlCommand cmd = new SqlCommand(selectString, con);
cmd.Parameters.AddWithValue("@CustomerEmail", customerEmail); //bind to email
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //for each record
{
cust = new Customer(); //create a customer and add the values to new customer
cust.CustomerID = (int)reader["CustomerId"];
cust.CustFirstName = reader["CustFirstName"].ToString();
cust.CustLastName = reader["CustLastName"].ToString();
cust.CustAddress = reader["CustAddress"].ToString();
cust.CustCity = reader["CustCity"].ToString();
cust.CustProvince = 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();
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(cust); //return that customer
}
public static Customer GetCustInfo(int customerId)
{
Customer cust = new Customer();
SqlConnection con = TravelExpertsDB.GetConnection();
string selectString = "SELECT CustomerId, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, "
+ "CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail "
+ "From Customers "
+ "WHERE CustomerId = @CustomerId "
+ "ORDER BY CustFirstName";
SqlCommand cmd = new SqlCommand(selectString, con);
cmd.Parameters.AddWithValue("@CustomerId", customerId);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
cust = new Customer();
cust.CustomerID = (int)reader["CustomerId"];
cust.CustFirstName = reader["CustFirstName"].ToString();
cust.CustLastName = reader["CustLastName"].ToString();
cust.CustAddress = reader["CustAddress"].ToString();
cust.CustCity = reader["CustCity"].ToString();
cust.CustProvince = 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();
}
reader.Close();
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
return(cust);
}
public static bool emailalreadyused(string given_email)
{
string SelectedEmail = ""; //get email
bool emailpresent = true;
SqlConnection connection = TravelExpertsDB.GetConnection();
// create select command
string selectString = "select CustEmail from Customers";
SqlCommand selectCommand = new SqlCommand(selectString, connection);
try
{
// open connection
connection.Open();
// run the select command and process the results adding states to the list
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())// process next row
{
SelectedEmail = reader["CustEmail"].ToString();
if (SelectedEmail == given_email)
{
emailpresent = true;
}
else
{
emailpresent = false;
}
}
reader.Close();
}
catch (Exception ex)
{
throw ex; // throw it to the form to handle
}
finally
{
connection.Close();
}
return(emailpresent);
}
public static List <Agents> GetAgents()
{
List <Agents> agentlist = new List <Agents>(); //Create an empty list of Agents
Agents agent = null; //Create a null agent
SqlConnection con = TravelExpertsDB.GetConnection(); //Create a connection to db
//Build the query to access the AgentID and Names for the Agents Table
string selectQuery = "SELECT AgentID, AgtFirstName, AgtLastName " +
"FROM Agents";
//Build the selectCommand by giving SqlCommand the query and the connection to the db
SqlCommand selectCommand = new SqlCommand(selectQuery, con);
try
{
con.Open(); //Open the connection
SqlDataReader reader = selectCommand.ExecuteReader(); //Execute the select command and store results in reader
while (reader.Read()) //Read the agents if they still exist
{
agent = new Agents(); //Create a new agent for this iteration
//Add the agents properties
agent.AgentID = (int)reader["AgentID"];
agent.AgtFirstName = (string)reader["AgtFirstName"];
agent.AgtLastName = (string)reader["AgtlastName"];
agent.AgtFullName = agent.AgtFirstName + " " + agent.AgtLastName;
agentlist.Add(agent);//Add this agemt to the agent list
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();//Close the connection
}
//return List
return(agentlist);
}
public static bool UpdateCustomerInfo(Customer customer, Customer updateCust)
{
SqlConnection con = TravelExpertsDB.GetConnection();
string updateString = "UPDATE Customers "
+ "SET CustFirstName = @NewCustomerFirstName, "
+ "CustLastName = @NewCustLastName, "
+ "CustAddress = @NewCustAddress, "
+ "CustCity = @NewCustCity, "
+ "CustProv = @NewCustProv, "
+ "CustPostal = @NewCustPostal, "
+ "CustCountry = @NewcustCountry, "
+ "CustHomePhone = @NewCustHomePhone, "
+ "CustBusPhone = @NewCustBusPhone, "
+ "CustEmail = @NewCustEmail "
+ "WHERE CustomerId = @OldCustomerId "
+ "AND CustFirstName = @OldCustFirstName "
+ "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";
SqlCommand updateCmd = new SqlCommand(updateString, con);
updateCmd.Parameters.AddWithValue("@NewCustomerFirstName", updateCust.CustFirstName);
updateCmd.Parameters.AddWithValue("@NewCustLastName", updateCust.CustLastName);
updateCmd.Parameters.AddWithValue("@NewCustAddress", updateCust.CustAddress);
updateCmd.Parameters.AddWithValue("@NewCustCity", updateCust.CustCity);
updateCmd.Parameters.AddWithValue("@NewCustProv", updateCust.CustProvince);
updateCmd.Parameters.AddWithValue("@NewCustPostal", updateCust.CustPostal);
updateCmd.Parameters.AddWithValue("@NewcustCountry", updateCust.CustCountry);
updateCmd.Parameters.AddWithValue("@NewCustHomePhone", updateCust.CustHomePhone);
updateCmd.Parameters.AddWithValue("@NewCustBusPhone", updateCust.CustBusPhone);
updateCmd.Parameters.AddWithValue("@NewCustEmail", updateCust.CustEmail);
updateCmd.Parameters.AddWithValue("@OldCustomerId", customer.CustomerID);
updateCmd.Parameters.AddWithValue("@OldCustFirstName", customer.CustFirstName);
updateCmd.Parameters.AddWithValue("@OldCustLastName", customer.CustLastName);
updateCmd.Parameters.AddWithValue("@OldCustAddress", customer.CustAddress);
updateCmd.Parameters.AddWithValue("@OldCustCity", customer.CustCity);
updateCmd.Parameters.AddWithValue("@OldCustProv", customer.CustProvince);
updateCmd.Parameters.AddWithValue("@OldCustPostal", customer.CustPostal);
updateCmd.Parameters.AddWithValue("@OldcustCountry", customer.CustCountry);
updateCmd.Parameters.AddWithValue("@OldCustHomePhone", customer.CustHomePhone);
updateCmd.Parameters.AddWithValue("@OldCustBusPhone", customer.CustBusPhone);
updateCmd.Parameters.AddWithValue("@OldCustEmail", customer.CustEmail);
try
{
con.Open();
int count = updateCmd.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
