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