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