public List<Question> LoadFromXls()
{
List<Question> questions = new List<Question>();
Workbook workbook = new Workbook();
workbook.LoadFromFile(fileName);
var worksheet = workbook.Worksheets[0];
int rowIDX = 1;
int cellIDX = 1;
while (rowIDX != worksheet.Rows.Length && !worksheet.Rows[rowIDX].Cells[cellIDX].Value.Equals(""))
{
Question question = new Question();
question.Value = worksheet.Rows[rowIDX].Cells[cellIDX].Value;
rowIDX++;
for (int i = 1; i <= 4; i++)
{
question.Add(new Answer(worksheet.Rows[rowIDX].Cells[cellIDX].Value,
worksheet.Rows[rowIDX].Cells[cellIDX + 1].Value.Equals("+")));
rowIDX++;
}
questions.Add(question);
}
return questions;
}
public Question(Question copy)
{
ID = copy.ID;
Subject = copy.Subject;
Value = copy.Value;
Answers = new List<Answer>(copy.Answers);
}
public void Add(Question question)
{
if (Questions == null)
{
Questions = new List<Question>();
}
question.Subject = this;
Questions.Add(question);
}
public void Delete(Question question)
{
using (var conn = sqlConnection.Connection)
{
conn.Open();
foreach (var answer in question.Answers)
{
answerDao.Delete(conn, answer.ID);
}
questionDao.Delete(conn, question.ID);
}
}
public void SaveOrUpdate(Question question)
{
using (var conn = sqlConnection.Connection)
{
conn.Open();
questionDao.SaveOrUpdate(conn, question);
foreach (var answer in question.Answers) // потом сохраняем каждый ответ поотдельности
{
answerDao.SaveOrUpdate(conn, answer);
}
}
}
/// <summary>
/// Сохраняет изменения в таблице
/// </summary>
/// <param name="question">вопрос</param>
public void SaveOrUpdate(SQLiteConnection conn, Question question)
{
if (question.ID == 0)
{
int ID = indexService.NextID(Question.TABLE_NAME);
string sql = string.Format(insertIntoSql, Question.TABLE_NAME, ID, question.Value,
question.Subject.ID);
ExecuteNonQuery(conn, sql);
question.ID = ID;
}
else
{
using (SQLiteCommand insertSQL = new SQLiteCommand("UPDATE QUESTIONS SET Value = @Value, Subject_ID = @Subject_ID WHERE ID = @ID", conn))
{
insertSQL.Parameters.AddWithValue("@Value", question.Value);
insertSQL.Parameters.AddWithValue("@Subject_ID", question.Subject.ID);
insertSQL.Parameters.AddWithValue("@ID", question.ID);
insertSQL.ExecuteNonQuery();
}
}
}
/// <summary>
/// Преобразует ответ в "вопрос"
/// </summary>
/// <param name="reader">Ответ из БД</param>
/// <returns>вопрос</returns>
private Question ToQuestion(SQLiteDataReader reader)
{
Question question = new Question();
question.ID = Convert.ToInt32(reader["ID"]);
question.Value = Convert.ToString(reader["Value"]);
question.Subject = new Subject(Convert.ToInt32(reader["Subject_ID"]));
return question;
}
////////////////
private void UpdateForm(Question question)
{
gbSubjectData.Enabled = false;
tlpSubjectQuestions.Enabled = true;
rtb_Question.Text = question.Value;
rtb_Answer_A.Text = question.Answers[0].Value;
rb_Answer_A.Checked = question.Answers[0].Right;
rtb_Answer_B.Text = question.Answers[1].Value;
rb_Answer_B.Checked = question.Answers[1].Right;
rtb_Answer_C.Text = question.Answers[2].Value;
rb_Answer_C.Checked = question.Answers[2].Right;
rtb_Answer_D.Text = question.Answers[3].Value;
rb_Answer_D.Checked = question.Answers[3].Right;
}
private void новыйВопросToolStripMenuItem_Click(object sender, EventArgs e)
{
if (treeView_Subjects.SelectedNode != null)
{
var SelectedNode = treeView_Subjects.SelectedNode;
if (SelectedNode.Tag is Question) {
SelectedNode = SelectedNode.Parent;
}
Question question = new Question("Новый вопрос");
question.Add(new Answer());
question.Add(new Answer());
question.Add(new Answer());
question.Add(new Answer());
TreeNode qNode = new TreeNode();
qNode.Text = question.Value;
qNode.Tag = question;
SelectedNode.Nodes.Add(qNode);
treeView_Subjects.SelectedNode = qNode;
forSaveQuestion = true;
tsbSaveCurrent.Enabled = true;
}
}