示例#1
0
        // TODO: add a respondent
        public static bool AddRespondent(Respondent respondent)
        {
            try
            {
                using (SqlConnection connection = BicycleStoreCorpDB.GetConnection())
                {
                    string queryString = "INSERT INTO Respondents (FirstName, LastName, EmailAddress, AddressLine1, AddressLine2, City, StateAbbr, ZipCode, LastSurvey)" +
                                         " VALUES (@FirstName, @LastName, @EmailAddress, @AddressLine1, @AddressLine2, @City, @StateAbbr, @ZipCode, GETDATE())";

                    SqlCommand cmd = new SqlCommand(queryString, connection);
                    cmd.Parameters.AddWithValue("@FirstName", respondent.FirstName);
                    cmd.Parameters.AddWithValue("@LastName", respondent.LastName);
                    cmd.Parameters.AddWithValue("@EmailAddress", respondent.EmailAddress);
                    cmd.Parameters.AddWithValue("@AddressLine1", respondent.AddressLine1);
                    cmd.Parameters.AddWithValue("@AddressLine2", respondent.AddressLine2);
                    cmd.Parameters.AddWithValue("@City", respondent.City);
                    cmd.Parameters.AddWithValue("@StateAbbr", respondent.StateAbbr);
                    cmd.Parameters.AddWithValue("@ZipCode", respondent.ZipCode);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                return(false);
            }

            return(true);

            // throw new NotImplementedException();
        }
示例#2
0
        // TODO: modify a respondent

        // TODO: delete a respondent

        public static int GetLastRespondentID()
        {
            int lastRespondentID = 0;

            try
            {
                using (SqlConnection connection = BicycleStoreCorpDB.GetConnection())
                {
                    string qryString = "SELECT TOP 1 RespondentID " +
                                       "FROM Respondents " +
                                       "ORDER BY RespondentID DESC;";

                    SqlCommand cmd = new SqlCommand(qryString, connection);
                    connection.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            lastRespondentID = Convert.ToInt32(reader["RespondentID"]);
                        }
                        connection.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }

            return(lastRespondentID);
        }
示例#3
0
        // get repondent ID by email
        public static int GetRespondentID(string email)
        {
            int respondentID = 0;

            try
            {
                using (SqlConnection connection = BicycleStoreCorpDB.GetConnection())
                {
                    string qryString = "SELECT RespondentID " +
                                       "FROM Respondents " +
                                       "WHERE EmailAddress = @EmailAddress ";
                    SqlCommand cmd = new SqlCommand(qryString, connection);
                    cmd.Parameters.AddWithValue("@EmailAddress", email);
                    connection.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            respondentID = Convert.ToInt32(reader["RespondentID"]);
                        }
                        connection.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }

            return(respondentID);
        }
示例#4
0
        // add response set for a respondent
        public static bool AddResponse(Response response)
        {
            try
            {
                using (SqlConnection connection = BicycleStoreCorpDB.GetConnection())
                {
                    string queryString = "INSERT INTO Responses (RespondentID, SurveyDate, Answer1, Answer2, Answer3, Answer4, Answer5, Answer6)" +
                                         " VALUES (@RespondentID, GETDATE(), @Answer1, @Answer2, @Answer3, @Answer4, @Answer5, @Answer6)";

                    SqlCommand cmd = new SqlCommand(queryString, connection);
                    cmd.Parameters.AddWithValue("@RespondentID", response.RespondentID);
                    cmd.Parameters.AddWithValue("@Answer1", response.Answer1);
                    cmd.Parameters.AddWithValue("@Answer2", response.Answer2);
                    cmd.Parameters.AddWithValue("@Answer3", response.Answer3);
                    cmd.Parameters.AddWithValue("@Answer4", response.Answer4);
                    cmd.Parameters.AddWithValue("@Answer5", response.Answer5);
                    cmd.Parameters.AddWithValue("@Answer6", response.Answer6);
                    connection.Open();
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                return(false);
            }

            return(true);

            // throw new NotImplementedException();
        }
示例#5
0
        // get the respondent information
        public static Respondent GetRespondent(string email)
        {
            Respondent matchingRespondent = new Respondent();

            try
            {
                using (SqlConnection connection = BicycleStoreCorpDB.GetConnection())
                {
                    string qryString = "SELECT * FROM Respondents WHERE EmailAddress = @Email";

                    SqlCommand cmd = new SqlCommand(qryString, connection);
                    cmd.Parameters.AddWithValue("@Email", email);
                    connection.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (email.Equals(reader["EmailAddress"].ToString()))
                            {
                                matchingRespondent.RespondentID = Convert.ToInt32(reader["RespondentID"]);
                                matchingRespondent.FirstName    = reader["FirstName"].ToString();
                                matchingRespondent.LastName     = reader["LastName"].ToString();
                                matchingRespondent.EmailAddress = reader["EmailAddress"].ToString();
                                matchingRespondent.AddressLine1 = reader["AddressLine1"].ToString();
                                matchingRespondent.AddressLine2 = reader["AddressLine2"].ToString();
                                matchingRespondent.City         = reader["City"].ToString();
                                matchingRespondent.StateAbbr    = reader["StateAbbr"].ToString();
                                matchingRespondent.ZipCode      = reader["ZipCode"].ToString();
                                matchingRespondent.LastSurvey   = (DateTime)reader["LastSurvey"];
                            }
                            else
                            {
                                throw new DataException("Unexpected respondent returned.");
                            }
                            // connection.Close();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }


            return(matchingRespondent);
        }
示例#6
0
        // get responses for a respondent
        public static Response GetResponse(int responseID)
        {
            Response matchingResponse = new Response();

            try
            {
                using (SqlConnection connection = BicycleStoreCorpDB.GetConnection())
                {
                    string qryString = "SELECT * FROM Responses WHERE ResponseID = @ResponseID";

                    SqlCommand cmd = new SqlCommand(qryString, connection);
                    cmd.Parameters.AddWithValue("@ResponseID", responseID);
                    connection.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (responseID == Convert.ToInt32(reader["ResponseID"]))
                            {
                                matchingResponse.ResponseID   = Convert.ToInt32(reader["ResponseID"]);
                                matchingResponse.RespondentID = Convert.ToInt32(reader["RespondentID"]);
                                matchingResponse.SurveyDate   = (DateTime)reader["SurveyDate"];
                                matchingResponse.Answer1      = reader["Answer1"].ToString();
                                matchingResponse.Answer2      = reader["Answer2"].ToString();
                                matchingResponse.Answer3      = reader["Answer3"].ToString();
                                matchingResponse.Answer4      = reader["Answer4"].ToString();
                                matchingResponse.Answer5      = reader["Answer5"].ToString();
                                matchingResponse.Answer6      = reader["Answer6"].ToString();
                            }
                            else
                            {
                                throw new DataException("Unexpected response returned.");
                            }
                            // connection.Close();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }

            return(matchingResponse);
        }
示例#7
0
        // TODO: delete response set for a respondent
        public static bool DeleteResponse(int responseID)
        {
            try
            {
                using (SqlConnection connection = BicycleStoreCorpDB.GetConnection())
                {
                    string qryString = "DELETE FROM Responses WHERE ResponseID = @ResponseID";

                    SqlCommand cmd = new SqlCommand(qryString, connection);
                    cmd.Parameters.AddWithValue("@ResponseID", responseID);
                    connection.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader());
                    return(true);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                return(false);
            }
        }
示例#8
0
        // check if email exists in respondents table
        public static bool EmailExists(string email)
        {
            int result = 0;

            try
            {
                using (SqlConnection connection = BicycleStoreCorpDB.GetConnection())
                {
                    string qryString = "SELECT COUNT(EmailAddress) AS num " +
                                       "FROM Respondents " +
                                       "WHERE EmailAddress = @EmailAddress " +
                                       "GROUP BY EmailAddress; ";
                    SqlCommand cmd = new SqlCommand(qryString, connection);
                    cmd.Parameters.AddWithValue("@EmailAddress", email);
                    connection.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToInt32(reader["num"]);
                        }
                        connection.Close();
                    }
                }

                if (result == 1)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                return(false);
            }
        }
示例#9
0
        // check last survey date
        public static bool CheckLastSurveyDate(string email)
        {
            int result = 0;

            try
            {
                using (SqlConnection connection = BicycleStoreCorpDB.GetConnection())
                {
                    string qryString = "SELECT DATEDIFF(year, GETDATE(), LastSurvey) as Years " +
                                       "FROM Respondents " +
                                       "WHERE EmailAddress = @EmailAddress ";
                    SqlCommand cmd = new SqlCommand(qryString, connection);
                    cmd.Parameters.AddWithValue("@EmailAddress", email);
                    connection.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result = Convert.ToInt32(reader["Years"]);
                        }
                        connection.Close();
                    }
                }

                if (result >= 1)
                {
                    return(true);
                }
                else
                {
                    return(false);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
                return(false);
            }
        }