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

I don't know what's that..Can you explain to me and give me a hand? — dpaul1994
– dpaul1994, Commented Jan 13, 2015 at 10:26
Because the simulation contains only 26 questions and in database I have over 1,000... — dpaul1994
– dpaul1994, Commented Jan 13, 2015 at 10:28

RePierre · Accepted Answer · 2015-01-13 13:50:27Z

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.

answered Jan 13, 2015 at 13:50

RePierre

9,5762 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dpaul1994 Over a year ago

Ok, but how can I do that in my code? There's too much code that have to be modified..

dpaul1994 Over a year ago

I mean, is possible to make it in my own script?

RePierre Over a year ago

@Paulicaaa I don't know of any way to do that only with a script and if there were a way I don't think it will be a simple one because you'd need to know which questions were loaded for each user in order to build the exclusion list. I don't want to sound rude but looking at the code you posted and how much duplication is in there this sort of change would be worth your while in future.

dpaul1994 Over a year ago

I see..well, thank you very much for help, I appreciate and I'll try to create a good code :)

Collectives™ on Stack Overflow

C# Select from database random no repeat

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related