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
        }
示例#3
0
        // 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);
        }
示例#4
0
        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
        }
示例#6
0
        //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
        }
示例#7
0
        // 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
        }
示例#8
0
        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);
        }
示例#9
0
        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);
        }
示例#10
0
        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);
        }
示例#11
0
        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();
            }
        }