public void CreateRecord(int textNumber, string username, List <Question> answers, List <Question> pupilAnswer)
{
int score = 0;
List <string> truOrFalseList = new List <string>();
for (int i = 0; i < answers.Count - 1; i++)
{
if (pupilAnswer[i].CorrectAnswer.ToString().Trim() == answers[i].CorrectAnswer.ToString().Trim())
{
truOrFalseList.Add("Верно");
score += answers[i].Cost;
}
if (pupilAnswer[i].CorrectAnswer.ToString().Trim() != answers[i].CorrectAnswer.ToString().Trim())
{
truOrFalseList.Add("Неверно");
}
}
string name = GetUserName(username);
string resultFillQuery = $"INSERT INTO PISA_UserResult ([Text_ID],[Question_1],[Question_2],[Question_3],[Question_4],[Question_5],[Points],[Username]) VALUES('{textNumber}','{truOrFalseList[0]}','{truOrFalseList[1]}','{truOrFalseList[2]}','{truOrFalseList[3]}','{truOrFalseList[4]}',{score},'{name}')";
SqlConnection con = new SqlConnection(SQL_Controller.GetSqlConnectionPath());
SqlCommand showResult = new SqlCommand(resultFillQuery, con);
con.Open();
showResult.ExecuteNonQuery();
con.Close();
}
public byte[] GetImage(int txtid)
{
SqlConnection con = new SqlConnection(SQL_Controller.GetSqlConnectionPath());
con.Open();
string sqlQuery = $"SELECT Text_Img FROM PISA_TextTable WHERE Text_ID = {txtid}";
SqlCommand cmd = new SqlCommand(sqlQuery, con);
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
if (reader.HasRows)
{
byte[] img = (byte[])reader[0];
if (img == null)
{
return(null);
}
else
{
return(img);
}
}
else
{
return(null);
}
}
/// <summary>
/// Заполняет текст вопросов. Первый параметр-список вопросов. Второй- ID набора вопросов
/// </summary>
/// <param name="quest"></param>
/// <param name="questSetNumber"></param>
/// <returns></returns>
public static List <Question> GetQuestionText(List <Question> quest, int questSetNumber)
{
List <string> testquestions = new List <string>();
using (SqlConnection con = new SqlConnection(SQL_Controller.GetSqlConnectionPath()))
{
SqlCommand comm = new SqlCommand("SELECT Question_Text FROM PISA_QuestinTable WHERE Question_Set_ID = " + questSetNumber, con);
con.Open();
DbDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
testquestions.Add(reader["Question_Text"].ToString());
}
}
foreach (string questText in testquestions)
{
quest.Add(new Question {
QuestionText = questText
});
}
return(quest);
}
private void FillUserReSults()
{
string resultFillQuery = @"SELECT p.[Test_ID],p.[Question_1],p.[Question_2],p.[Question_3],p.[Question_4]
,p.[Question_5]
,p.[Points]
,p.[Username] FROM PISA_UserResult p INNER JOIN PISA_UserRealName pr ON p.Username=pr.name WHERE pr.username ='******'";
DataTable usersResult = new DataTable();
SqlDataAdapter showResult = new SqlDataAdapter(resultFillQuery, SQL_Controller.GetSqlConnectionPath());
showResult.Fill(usersResult); // заполнение DataTable для создпния источника данных DataGreedView
resultDataGridView1.DataSource = usersResult;
}
public string GetUserName(string user)
{
string getUsernameQuery = $"SELECT name FROM PISA_UserRealName WHERE username ='******'";
SqlConnection con = new SqlConnection(SQL_Controller.GetSqlConnectionPath());
SqlCommand showResult = new SqlCommand(getUsernameQuery, con);
con.Open();
string username = showResult.ExecuteScalar().ToString();
con.Close();
return(username);
}
public void GenerateTest()
{
using (SqlConnection con = new SqlConnection(SQL_Controller.GetSqlConnectionPath()))
{
SqlCommand comm = new SqlCommand("SELECT Text FROM PISA_TextTable WHERE Text_ID = " + rndTextNum, con);
con.Open();
DbDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
textOfTest = reader["Text"].ToString();
}
}
}
private void LogInButton_Click(object sender, EventArgs e)
{
SqlConnection connectionToPISADB = new SqlConnection(SQL_Controller.GetSqlConnectionPath());
string loginQuery = "SELECT * FROM PISA_Autentification WHERE ussername ='" + textBox1.Text.Trim() + "' AND password ='******'";
SqlDataAdapter sda = new SqlDataAdapter(loginQuery, connectionToPISADB);// запрос, сверяющий логин и пароль
DataTable proxyTable = new DataTable();
sda.Fill(proxyTable);
if (proxyTable.Rows.Count == 1)
{
userName = textBox1.Text;
MainWindow workWindow = new MainWindow(userName); // передача имени пользователя для выгрузки результатов
this.Hide();
connectionToPISADB.Close();
workWindow.Show();
}
else
{
MessageBox.Show("Неверный логин и/или пароль");
}
}
/// <summary>
/// Метод обращается к БД для заполнения текста вариантов ответа конкретного вопроса.
/// </summary>
/// <param name="questionsWithText"></param>
/// <returns></returns>
public static List <Question> FillQuestionVariant(List <Question> questionsWithText)
{
using (SqlConnection con = new SqlConnection(SQL_Controller.GetSqlConnectionPath()))
{
foreach (Question q in questionsWithText)
{
SqlCommand fillCommand = new SqlCommand("SELECT * FROM PISA_QuestinTable WHERE Question_Text = '" + q.questionText + "'", con);
con.Open();
DbDataReader reader = fillCommand.ExecuteReader();
while (reader.Read())
{
q.VariantA = (reader["Question_Var_A"].ToString());
q.VariantB = (reader["Question_Var_B"].ToString());
q.VariantC = (reader["Question_Var_C"].ToString());
q.VariantD = (reader["Question_Var_D"].ToString());
q.CorrectAnswer = (reader["Question_Answer"].ToString());
q.Cost = (int.Parse(reader["Question_Value"].ToString()));
}
con.Close();
}
return(questionsWithText);
}
}
