public bool AddNewQuestion(QuestionModel Question)
{
if (Question == null) return false;
bool success = false;
string command1Text = "INSERT INTO Questions(QID, QBody, MultipleAnswers, Images)" +
"VALUES(@qID, @qBody, @hasMultiAnswers, @hasImages)";
string command2Text = "INSERT INTO Answers(AID, QID, ABody, IsCorrect)" +
"VALUES(@aID, @qID, @aBody, @isCorrect)";
string command3Text = "INSERT INTO Images(IID, QID, Image)" +
"VALUES(@iID, @qID, @image)";
using (SqlCeConnection connection = new SqlCeConnection(connectionSting))
{
connection.Open();
using (IDbTransaction tx = connection.BeginTransaction(IsolationLevel.Serializable))
{
using (SqlCeCommand command1 = new SqlCeCommand(command1Text, connection))
{
try
{
command1.Parameters.AddWithValue("@qID", Question.QID);
command1.Parameters.AddWithValue("@QBody", Question.QBody);
if (Question.HasMultipleAnswers)
{
command1.Parameters.AddWithValue("@hasMultiAnswers", 1);
}
else command1.Parameters.AddWithValue("@hasMultiAnswers", 0);
if (Question.HasImages)
{
command1.Parameters.AddWithValue("@hasImages", 1);
}
else command1.Parameters.AddWithValue("@hasImages", 0);
command1.ExecuteNonQuery();
success = true;
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
success = false;
}
}
for (int i = 0; i < Question.Answers.Count; i++)
{
using (SqlCeCommand command2 = new SqlCeCommand(command2Text, connection))
{
try
{
command2.Parameters.AddWithValue("@aID", Question.Answers[i].AID);
command2.Parameters.AddWithValue("@qID", Question.Answers[i].QID);
command2.Parameters.AddWithValue("@aBody", Question.Answers[i].AnswerBody);
if (Question.Answers[i].IsCorrectAnswer)
{
command2.Parameters.AddWithValue("@isCorrect", 1);
}
else command2.Parameters.AddWithValue("@isCorrect", 0);
command2.ExecuteNonQuery();
success = true;
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
success = false;
}
}
}
for (int i = 0; i < Question.Images.Count; i++)
{
using (SqlCeCommand command3 = new SqlCeCommand(command3Text, connection))
{
try
{
command3.Parameters.AddWithValue("@iID", Question.Images[i].ImageID);
command3.Parameters.AddWithValue("@qID", Question.Images[i].QID);
SqlCeParameter picparameter = new SqlCeParameter();
picparameter.SqlDbType = SqlDbType.Image;
picparameter.ParameterName = "image";
picparameter.Value = Question.Images[i].Image;
command3.Parameters.Add(picparameter);
command3.ExecuteNonQuery();
success = true;
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
success = false;
}
}
}
}
}
return success;
}
public List<QuestionModel> GetAllQuestions()
{
List<QuestionModel> QList = new List<QuestionModel>();
string commandText = "SELECT QID, QBody, MultipleAnswers, Images FROM Questions";
using (SqlCeConnection connection = new SqlCeConnection(connectionSting))
{
using (SqlCeCommand command = new SqlCeCommand(commandText, connection))
{
try
{
connection.Open();
SqlCeDataReader reader = command.ExecuteReader();
while (reader.Read())
{
QuestionModel q = new QuestionModel();
q.QID = new Guid(reader[0].ToString());
q.QBody = reader[1].ToString();
q.HasMultipleAnswers = (bool)reader[2];
q.HasImages = (bool)reader[3];
q.Answers = GetAnswers(q.QID, connection);
q.Images = GetAllImageFromDB(q.QID, connection);
QList.Add(q);
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
}
return QList;
}