C# Select from database random no repeat

Question

I have a script that select from database in random order.

The script is a simulation of auto exam. The thing I want is that if a question has already been selected, to not be extracted again.

How can I do that? Other thing, if no one of those 3 checkboxes were selected, when message box appear, it selects another question. Select() initialize labels with value from database and I use it in Load form.

Here is the Code what i have tried so far:

private void select()
{
     string dataA = "SELECT * FROM questions order by rand()";
     MySqlCommand cmd = new MySqlCommand(dataA, index.connect);
     cmd.CommandType = CommandType.Text;
     using (index.connect)
     {
        index.connect.Open();
        MySqlDataReader rdr = cmd.ExecuteReader();
        try
        {
           if (rdr.Read())
           {
               label2.Text = rdr["question"].ToString();
               label2.AutoSize = true;
               label2.UseCompatibleTextRendering = true;
               label3.Text = rdr["answer1"].ToString();
               label4.Text = rdr["answer2"].ToString();
               label5.Text = rdr["answer3"].ToString();
               option1 = checkBox1.Checked;
               option2 = checkBox2.Checked;
               option3 = checkBox3.Checked;
            }
         }
         catch (Exception ex)
         {
             MessageBox.Show(ex.Message);
         }
         finally
         {
            index.connect.Close();
         }
      }
  }

private void button1_Click(object sender, EventArgs e) 
// in cazul in care raspunzi la o intrebare, 
//iar aceasta ramane, orice a-i raspunde, pune la incorect
{
    string dataA = "SELECT * FROM questions order by rand()";
    MySqlCommand cmd = new MySqlCommand(dataA, index.connect);
    cmd.CommandType = CommandType.Text;
    using (index.connect)
    {
        index.connect.Open();
        MySqlDataReader rdr = cmd.ExecuteReader();
        if (rdr.Read())
        {
            label2.Text = rdr["question"].ToString();
            label3.Text = rdr["answer1"].ToString();
            label4.Text = rdr["answer2"].ToString();
            label5.Text = rdr["answer3"].ToString();
            option1 = checkBox1.Checked;
            option2 = checkBox2.Checked;
            option3 = checkBox3.Checked;
            if (checkBox1.Checked == false && 
                checkBox2.Checked == false && 
                checkBox3.Checked == false)
            {
                MessageBox.Show("Bifati minim o casuta!");
                //imi selecteaza alta intrebare
                 return;
            }
            else
            {
               if ((option1.ToString() == rdr["option1"].ToString()) &&
                   (option2.ToString() == rdr["option2"].ToString()) && 
                   (option3.ToString() == rdr["option3"].ToString()))
               {
                    corect++;
                    label10.Text = corect.ToString();
                    checkBox1.Checked = false;
                    checkBox2.Checked = false;
                    checkBox3.Checked = false;
               }
               else
               {
                    incorect++;
                    label12.Text = incorect.ToString();
                    checkBox1.Checked = false;
                    checkBox2.Checked = false;
                    checkBox3.Checked = false;
               }
           }
       }
  }

Answer 1

You need to exclude the already selected questions from being returned to you.

To do this, you need to store the id of each question returned by your method in a collection and use that collection to exclude question for next selects.

Avoid using a flag in the database because that will work only if there is only one instance of application running. If there are multiple instances each instance will get different questions served and should exclude only questions received by it.

To exemplify:

public class Question
{
    public int Id {get; set;}
    public string Text {get; set;}
    public Answer[] Answers {get; set;}
}

public class QuestionSelector
{
    private readonly List<int> _previousQuestionIds = new List<int>();

    public Question NextQuestion()
    {
        var query = "select top 1 * from Questions ";
        if(ids.Any())
        {
            var ids = String.Join(",", _previousQuestionIds.Select(id=>id.ToString()));
            query += "  where id not in (" + ids + ") ";
        }
        query += " order by rand()";
        var question = ParseQuestion(query);
        _previousQuestionIds.Add(question.Id);
        return question;
    }

    private Question ParseQuestion(string query)
    {
        // query the database and convert the data from the returned row
    }
}

Having the above class just create an instance of it when loading your form and call NextQuestion() method to load the next question. It will start with an empty collection of ids meaning that on first call it won't exclude any question but each time you'll call the NextQuestion() method it will add the id of the returned question to the list and on the next call the question will be excluded.