/// <summary> /// Parses a CSV file with no headings and the following columns in order: question_text, correct_answer, wrong_answer1, wrong_answer2, /// wrong_answer3, category, level, reference. Each row of the file is inserted as a record in the database that has not been used. This /// method does not support picture questions /// </summary> /// <param name="csvPath">Path to csv file to import</param> /// <returns>true if the operation succeeds and false if it fails</returns> public bool importRegularQuestionsFromCSV(string csvPath) { bool result = true; //assume its good unless we see something bad TextFieldParser parser = new TextFieldParser(@csvPath); parser.TextFieldType = FieldType.Delimited; parser.SetDelimiters(","); while (!parser.EndOfData) { string[] fields = parser.ReadFields(); string question = fields[0]; string correct = fields[1]; string wrong1 = fields[2]; string wrong2 = fields[3]; string wrong3 = fields[4]; string category = fields[5]; int level = Int32.Parse(fields[6]); string reference = fields[7]; QuestionData q = new QuestionData(question, correct, wrong1, wrong2, wrong3, category, level, reference, false, ""); result &= this.insertRecord(q); //this will change result from true to false but will not change it from false to true } return(result); }
/// <summary> /// Updates a record in the questions table. The record with question_id matching the passed object will be updated to the values in the passed object /// </summary> /// <param name="record">Updated QuestionData object</param> /// <returns>true if the operation succeeds and false if it fails</returns> public override bool updateRecord(object record) { if (!(record is QuestionData)) { errorMessage = "Record is not QuestionData type"; return(false); } bool success; string qry = "UPDATE high_scores SET question_text=@a, correct_answer=@b, wrong_answer_1=@c, wrong_answer_2=@d, wrong_answer_3=@e, category=@f, level=@g, reference=@h, used=@i, image_file=@j, randomize_answers=@m WHERE question_id=@k"; if (this.openConnection()) { QuestionData d = (QuestionData)record; MySqlCommand cmd = new MySqlCommand(qry, connection); cmd.Parameters.AddWithValue("@a", d.question_text); cmd.Parameters.AddWithValue("@b", d.correct_answer); cmd.Parameters.AddWithValue("@c", d.wrong_answer_1); cmd.Parameters.AddWithValue("@d", d.wrong_answer_2); cmd.Parameters.AddWithValue("@e", d.wrong_answer_3); cmd.Parameters.AddWithValue("@f", d.category); cmd.Parameters.AddWithValue("@g", d.level); cmd.Parameters.AddWithValue("@h", d.reference); cmd.Parameters.AddWithValue("@i", d.used); cmd.Parameters.AddWithValue("@j", d.image_file); cmd.Parameters.AddWithValue("@k", d.question_id); cmd.Parameters.AddWithValue("@m", d.randomize_answers); int rowsAffected = cmd.ExecuteNonQuery(); success = true; if (rowsAffected == 0) { success = false; errorMessage = "No rows were updated"; } this.closeConnection(); } else { success = false; errorMessage = "Failed to open database connection"; } return(success); }
/// <summary> /// Inserts a record into the questions table with attributes from the passed object. /// The id in the object will be ignored and an id will be assigned by mysql's auto-increment /// </summary> /// <param name="record"></param> /// <returns>true if the operation succeeds and false if it fails</returns> public override bool insertRecord(object record) { if (!(record is QuestionData)) { errorMessage = "Record is not QuestionData type"; return(false); } bool success; string qry = "INSERT INTO questions(question_text, correct_answer, wrong_answer_1, wrong_answer_2, wrong_answer_3, category, level, reference, used, image_file, randomize_answers) VALUES(@a, @b, @c, @d, @e, @f, @g, @h, @i, @j, @k)"; if (this.openConnection()) { QuestionData d = (QuestionData)record; MySqlCommand cmd = new MySqlCommand(qry, connection); cmd.Parameters.AddWithValue("@a", d.question_text); cmd.Parameters.AddWithValue("@b", d.correct_answer); cmd.Parameters.AddWithValue("@c", d.wrong_answer_1); cmd.Parameters.AddWithValue("@d", d.wrong_answer_2); cmd.Parameters.AddWithValue("@e", d.wrong_answer_3); cmd.Parameters.AddWithValue("@f", d.category); cmd.Parameters.AddWithValue("@g", d.level); cmd.Parameters.AddWithValue("@h", d.reference); cmd.Parameters.AddWithValue("@i", d.used); cmd.Parameters.AddWithValue("@j", d.image_file); cmd.Parameters.AddWithValue("@k", d.randomize_answers); int rowsAffected = cmd.ExecuteNonQuery(); this.closeConnection(); success = true; if (rowsAffected == 0) { success = false; errorMessage = "No rows were inserted"; } } else { success = false; errorMessage = "Failed to open database connection"; } return(success); }