public static SurveyResults GetSurveyResults(int surveyId, String user, DateTime date)
{
if (!isConnected)
{
Init();
}
SurveyResults resultsList = new SurveyResults();
resultsList.surveyId = surveyId;
SQLiteCommand sqliteCommand = dbConnection.CreateCommand();
sqliteCommand.Parameters.Add(new SQLiteParameter(":survey_id", surveyId));
sqliteCommand.Parameters.Add(new SQLiteParameter(":user_name", user));
sqliteCommand.Parameters.Add(new SQLiteParameter(":date", date));
sqliteCommand.CommandText = "SELECT * FROM " + RESULTS_TABLE + " WHERE ((survey_id=:survey_id) AND (user_name=:user_name) AND (date=:date))";
SQLiteDataReader sqliteReader = sqliteCommand.ExecuteReader();
// Save each result to resultsList
while (sqliteReader.Read())
{
PollResult curResult = new PollResult();
curResult.answerId = Convert.ToInt32(sqliteReader["answer_id"].ToString());
curResult.customChoice = sqliteReader["custom_choice"].ToString();
curResult.date = sqliteReader["date"].ToString();
curResult.questionId = Convert.ToInt32(sqliteReader["question_id"].ToString());
curResult.userName = sqliteReader["user_name"].ToString();
resultsList.results.Add(curResult);
}
return resultsList;
}
/// <summary>
/// Receive from Client survey result and save it in database
/// </summary>
/// <param name="resultsList">list of results</param>
public static void SaveSurveyResult(SurveyResults resultsList)
{
if (!isConnected)
{
Init();
}
// Get current date
//string currentDate = String.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now);
SQLiteCommand sqliteCommand = dbConnection.CreateCommand();
sqliteCommand.CommandText = "INSERT INTO " + RESULTS_TABLE + "(user_name, survey_id, question_id, answer_id, custom_choice, date) VALUES(:username, :survey, :question, :answer, :custom, :date)";
sqliteCommand.Parameters.Add(new SQLiteParameter(":username", resultsList.userName));
sqliteCommand.Parameters.Add(new SQLiteParameter(":survey", resultsList.surveyId.ToString()));
sqliteCommand.Parameters.Add(new SQLiteParameter(":date", DateTime.Now));
sqliteCommand.Parameters.Add(new SQLiteParameter(":question"));
sqliteCommand.Parameters.Add(new SQLiteParameter(":answer"));
sqliteCommand.Parameters.Add(new SQLiteParameter(":custom"));
// Insert new surveyResult to database
foreach (PollResult result in resultsList.results)
{
sqliteCommand.Parameters[":question"].Value = result.questionId.ToString();
sqliteCommand.Parameters[":answer"].Value = result.answerId.ToString();
sqliteCommand.Parameters[":custom"].Value = result.customChoice;
sqliteCommand.ExecuteNonQuery();
}
}
public void SaveSurveyResult(SurveyResults resultsList)
{
if (Authentication.userName == USER_NAME && Authentication.password == PASSWORD)
{
DAL.PollDAL.SaveSurveyResult(resultsList);
}
}