Exemplo n.º 1
0
        public static int?GetCustomerChecks(string custuser, string custpass)
        {
            int?          custid        = null;                                                                    //initial value for custid
            string        md5pass       = FormsAuthentication.HashPasswordForStoringInConfigFile(custpass, "MD5"); //applied hash method to encrypt password inputed from Login.aspx
            SqlConnection connection    = TEDB.GetConnection();                                                    //connect to database
            string        selectSql     = "Select CustomerId from Customers where CustUser=@custuser and CustPass=@custpass";
            SqlCommand    selectCommand = new SqlCommand(selectSql, connection);

            selectCommand.Parameters.AddWithValue("@custuser", custuser);
            selectCommand.Parameters.AddWithValue("@custpass", md5pass);//only passed encrypted password into database
            try
            {
                connection.Open();
                SqlDataReader reader = selectCommand.ExecuteReader(System.Data.CommandBehavior.SingleRow);//get only one row back
                if (reader.Read())
                {
                    custid = Convert.ToInt32(reader["CustomerId"]);//get existed customerID
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(custid);
        }
Exemplo n.º 2
0
        //insert new customer to database
        public static int AddCustomer(Customer cust) // for register.aspx
        {
            int custID = 0;

            // prepare connection
            SqlConnection connection = TEDB.GetConnection();//connect to Database

            //Insert a new customer record to database
            string insertString = "insert into Customers " +
                                  "(CustFirstName, CustLastName, CustAddress, CustCity, CustProv, CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, AgentId, CustUser, CustPass) " +
                                  "values(@CustFirstName, @CustLastName, @CustAddress, @CustCity, @CustProv, @CustPostal, @CustCountry, @CustHomePhone, @CustBusPhone, @CustEmail, @AgentId, @CustUser, @CustPass)";
            SqlCommand insertCommand = new SqlCommand(insertString, connection);

            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("@AgentId", DBNull.Value);//according to requirement,AgentId is null
            insertCommand.Parameters.AddWithValue("@CustUser", cust.CustUser);

            insertCommand.Parameters.AddWithValue("@CustPass", cust.CustPass);

            try
            {
                // open connection
                connection.Open();

                // execute the statement
                int i = insertCommand.ExecuteNonQuery();
                if (i == 1) // one record inserted succcessfully
                {
                    // retrieve customer id from the added record
                    string selectString = "select ident_current('Customers') " +
                                          "from Customers";
                    SqlCommand selectCommand = new SqlCommand(selectString, connection);
                    custID = Convert.ToInt32(selectCommand.ExecuteScalar()); // (int) does not work!!!
                }
            }
            catch (Exception ex)
            {
                throw ex; // pass the buck
            }
            finally
            {
                connection.Close();
            }
            return(custID);
        }
Exemplo n.º 3
0
        public static Customer GetCustomer(string custUser) //this is for update.aspx pageolading
        {
            Customer cust = null;                           // found customer
            // define connection

            SqlConnection connection = TEDB.GetConnection();
            // define the select query command
            string selectQuery = "select CustomerId, CustFirstName, CustLastName, CustAddress, CustCity, CustProv, " +
                                 "CustPostal, CustCountry, CustHomePhone, CustBusPhone, CustEmail, AgentId, " +
                                 "CustUser, CustPass " +
                                 "from Customers " +
                                 "where CustUser= @CustUser";
            SqlCommand selectCommand = new SqlCommand(selectQuery, connection);

            selectCommand.Parameters.AddWithValue("@CustUser", custUser);
            try
            {
                // open the connection
                connection.Open();

                // execute the query
                SqlDataReader reader = selectCommand.ExecuteReader();

                // process the result if any
                if (reader.Read()) // if there is customer
                {
                    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.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     = GetNullableString(reader, "CustEmail");
                    cust.AgentId       = GetNullableInt(reader, "AgentId");
                    cust.CustUser      = reader["CustUser"].ToString();
                    cust.CustPass      = reader["CustPass"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex; // let the form handle it
            }
            finally
            {
                connection.Close(); // close connecto no matter what
            }

            return(cust);
        }
Exemplo n.º 4
0
        public static List <CustCost> AccountByProduct(int customerid)
        {
            CustCost        custcost  = null; // found customer
            List <CustCost> custcosts = new List <CustCost>();

            SqlConnection connection = TEDB.GetConnection();
            //join table bookings ,products,prodcutSuppliers
            string selectQuery = "select p.ProdName,bds.BookingId,bds.BasePrice,bds.AgencyCommission,bs.travelerCount from " +
                                 "bookings bs,BookingDetails bds,Products_Suppliers ps,Products p " +
                                 "where bs.BookingId=bds.BookingId and bds.ProductSupplierId=ps.ProductSupplierId " +
                                 "and ps.ProductId=p.ProductId and bs.CustomerId=@customerid";
            SqlCommand selectCommand = new SqlCommand(selectQuery, connection);

            selectCommand.Parameters.AddWithValue("@customerid", customerid);
            try
            {
                // open the connection
                connection.Open();

                // execute the query
                SqlDataReader reader = selectCommand.ExecuteReader();

                // process the result if any
                while (reader.Read()) // if there is customer
                {
                    custcost                         = new CustCost();
                    custcost.ProductName             = reader["ProdName"].ToString();
                    custcost.BookingId               = Convert.ToInt32(reader["BookingId"]);
                    custcost.PackageBasePrice        = Convert.ToDecimal(reader["BasePrice"]);
                    custcost.PackageAgencyCommission = Convert.ToDecimal(reader["AgencyCommission"]);
                    custcost.TravelerCount           = Convert.ToInt32(reader["travelerCount"]);
                    custcosts.Add(custcost);
                }
            }
            catch (Exception ex)
            {
                throw ex; // let the form handle it
            }
            finally
            {
                connection.Close(); // close connecto no matter what
            }

            return(custcosts);
        }
Exemplo n.º 5
0
        //verify if UserId is unique
        public static bool checkUserId(string custUser)
        {
            bool successfull = false;
            int  count;
            // define connection
            SqlConnection connection = TEDB.GetConnection();

            // define the select query command
            string checkUserQuery = "select count(*) " +
                                    "from Customers " +
                                    "where CustUser = @CustUser";
            SqlCommand checkUserCommand = new SqlCommand(checkUserQuery, connection);

            checkUserCommand.Parameters.AddWithValue("@CustUser", custUser);

            try
            {
                // open the connection
                connection.Open();


                count = Convert.ToInt32(checkUserCommand.ExecuteScalar());
                if (count >= 1)
                {
                    successfull = true;//there is a existig user
                }
                else
                {
                    successfull = false;//no existing user
                }
            }
            catch (Exception ex)
            {
                throw ex; // let the form handle it
            }
            finally
            {
                connection.Close(); // close connecto no matter what
            }
            return(successfull);
        }
Exemplo n.º 6
0
        //modify customer data
        public static bool UpdateCustomer(Customer customer, Customer old_customer) //for update.aspx to update new value to customer
        {
            bool          successful   = false;                                     //default condition is updating failed
            SqlConnection connection   = TEDB.GetConnection();
            string        updateString = "update Customers set " +
                                         "CustFirstName = @NewCustFirstName, " +
                                         "CustLastName = @NewCustLastName, " +
                                         "CustAddress = @NewCustAddress, " +
                                         "CustCity = @NewCustCity, " +
                                         "CustProv = @NewCustProv, " +
                                         "CustPostal = @NewCustPostal, " +
                                         "CustCountry = @NewCustCountry, " +
                                         "CustHomePhone = @NewCustHomePhone, " +
                                         "CustBusPhone = @NewCustBusPhone, " +
                                         "CustEmail = @NewCustEmail, " +
                                         "AgentId = @NewAgentId, " +
                                         "CustUser = @NewCustUser, " +
                                         "CustPass = @NewCustPass " +
                                         "where " + // update succeeds only if record not changed by other users
                                         "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 or " +
                                         "CustBusPhone Is NULL and @OldCustBusPhone Is NULL) and " +
                                         "(CustEmail = @OldCustEmail or " +
                                         "CustEmail Is NUll and @OldCustEmail IS NULL) and " +
                                         "(AgentId = @OldAgentId or " +
                                         "AgentId IS NULL and @OldAgentId is NULL) and " +
                                         "CustUser = @OldCustUser and " +
                                         "CustPass = @OldCustPass";
            SqlCommand updateCommand = new SqlCommand(updateString, connection);

            updateCommand.Parameters.AddWithValue("@OldCustFirstName", old_customer.CustFirstName);
            updateCommand.Parameters.AddWithValue("@OldCustLastName", old_customer.CustLastName);
            updateCommand.Parameters.AddWithValue("@OldCustAddress", old_customer.CustAddress);
            updateCommand.Parameters.AddWithValue("@OldCustCity", old_customer.CustCity);
            updateCommand.Parameters.AddWithValue("@OldCustProv", old_customer.CustProv);
            updateCommand.Parameters.AddWithValue("@OldCustPostal", old_customer.CustPostal);
            updateCommand.Parameters.AddWithValue("@OldCustCountry", old_customer.CustCountry);
            updateCommand.Parameters.AddWithValue("@OldCustHomePhone", old_customer.CustHomePhone);
            if (old_customer.CustBusPhone != null)
            {
                updateCommand.Parameters.AddWithValue("@OldCustBusPhone", old_customer.CustBusPhone);
            }
            else
            {
                updateCommand.Parameters.AddWithValue("@OldCustBusPhone", DBNull.Value);
            }
            if (old_customer.CustEmail != null)
            {
                updateCommand.Parameters.AddWithValue("@OldCustEmail", old_customer.CustEmail);
            }
            else
            {
                updateCommand.Parameters.AddWithValue("@OldCustEmail", DBNull.Value);
            }
            if (old_customer.AgentId != null)
            {
                updateCommand.Parameters.AddWithValue("@OldAgentId", old_customer.AgentId);
            }

            else
            {
                updateCommand.Parameters.AddWithValue("@OldAgentId", DBNull.Value);
            }
            updateCommand.Parameters.AddWithValue("@OldCustUser", old_customer.CustUser);
            updateCommand.Parameters.AddWithValue("@OldCustPass", old_customer.CustPass);

            updateCommand.Parameters.AddWithValue("@NewCustFirstName", customer.CustFirstName);
            updateCommand.Parameters.AddWithValue("@NewCustLastName", customer.CustLastName);
            updateCommand.Parameters.AddWithValue("@NewCustAddress", customer.CustAddress);
            updateCommand.Parameters.AddWithValue("@NewCustCity", customer.CustCity);
            updateCommand.Parameters.AddWithValue("@NewCustProv", customer.CustProv);
            updateCommand.Parameters.AddWithValue("@NewCustPostal", customer.CustPostal);
            updateCommand.Parameters.AddWithValue("@NewCustCountry", customer.CustCountry);
            updateCommand.Parameters.AddWithValue("@NewCustHomePhone", customer.CustHomePhone);
            if (customer.CustBusPhone != null)
            {
                updateCommand.Parameters.AddWithValue("@NewCustBusPhone", customer.CustBusPhone);
            }
            else
            {
                updateCommand.Parameters.AddWithValue("@NewCustBusPhone", DBNull.Value);
            }
            if (customer.CustEmail != null)
            {
                updateCommand.Parameters.AddWithValue("@NewCustEmail", customer.CustEmail);
            }
            else
            {
                updateCommand.Parameters.AddWithValue("@NewCustEmail", DBNull.Value);
            }
            if (customer.AgentId != null)
            {
                updateCommand.Parameters.AddWithValue("@NewAgentId", customer.AgentId);
            }
            else
            {
                updateCommand.Parameters.AddWithValue("@NewAgentId", DBNull.Value);
            }
            updateCommand.Parameters.AddWithValue("@NewCustUser", customer.CustUser);
            updateCommand.Parameters.AddWithValue("@NewCustPass", customer.CustPass);



            try
            {
                connection.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count == 1)
                {
                    successful = true;//if update succesfully ,return true
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(successful);
        }