// 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();
}
// 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);
}
// 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);
}
// 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();
}
// 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);
}
// 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);
}
// 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);
}
}
// 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);
}
}
// 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);
}
}