Storing database records into array with a for loop | C#

Question

I am creating an online test software using C# WinForms and SQL Server. Now I have two tables (SQL server) one of is a question, and one is the question_answers. I am inserting the questions and their answers from the database to the array. I have the code something like this.

public dbtest()
{
  sqlconnection = new SqlConnection(ConnectionString);
  Query = "SELECT questions.id as qid, questions.question as qq FROM dbo.questions; ";
  sqlcommand = new SqlCommand(Query, sqlconnection);
  sqlconnection.Open();
  SqlDataReader sdr = sqlcommand.ExecuteReader();

  while (sdr.Read())
  {
    int quiz_id;
    bool quiz_id1 = Int32.TryParse(sdr["qid"].ToString(), out quiz_id);

    sqlconnection = new SqlConnection(ConnectionString);
    Query =  "SELECT id, question_id, answer, is_correct FROM dbo.question_answers WHERE question_id = " + quiz_id + "  ; ";
    sqlcommand = new SqlCommand(Query, sqlconnection);
    sqlconnection.Open();
    SqlDataReader answr = sqlcommand.ExecuteReader();

    while (answr.Read())
    {
      questions[quiz_id, 0] = sdr["qq"].ToString();
      for (int ii = 1; ii < 5; ii++)
      {
        questions[quiz_id, ii] = answr["answer"].ToString();
      }
    }
  }
}

I need an array to do like this.

questions[0, 0] = "The 2006 World Cup Football Tournament held in";
questions[0, 1] = "France";
questions[0, 2] = "China";
questions[0, 3] = "*Germany";
questions[0, 4] = "Brazil";
//
questions[1, 0] = "The 'Black flag' signifies";
questions[1, 1] = "revolution/danger";
questions[1, 2] = "*protest";
questions[1, 3] = "truce";
questions[1, 4] = "peace";
//
questions[2, 0] = "Robert Koch worked on";
questions[2, 1] = "*tuberculosis";
questions[2, 2] = "cholera";
questions[2, 3] = "malaria";
questions[2, 4] = "diabetes";

But when I use for loop, I get the same answer records like this.

questions[0, 0] = "The 2006 World Cup Football Tournament held in";
questions[0, 1] = "France";
questions[0, 2] = "France";
questions[0, 3] = "France";
questions[0, 4] = "France";

I am new in C# WinForms programming. And I can not get better algorithm. So I have two questions. The first one: How can I fix this code's problem?

while (answr.Read())
{
  questions[quiz_id, 0] = sdr["qq"].ToString();
  for (int ii = 1; ii < 5; ii++)
  {
    questions[quiz_id, ii] = answr["answer"].ToString();
  }
}

The second: Are there other optimally solutions to get questions and their answers from database?

Excuse me if I can not explain. Thanks for answers!

Answer 1

the following code :

for (int ii = 1; ii < 5; ii++)
{
    questions[quiz_id, ii] = answr["answer"].ToString();
}

will set the same answer in the sub array questions [0, X] with X between 1 and 4.
And then another answer in the sub array questions [1, X] with X between 1 and 4.
And then another answer in the sub array questions [2, X] with X between 1 and 4.

You need to get rid of that unnecessary loop.

public dbtest(){
        sqlconnection = new SqlConnection(ConnectionString);
        Query =
            "SELECT questions.id as qid, questions.question as qq FROM dbo.questions; ";
        sqlcommand = new SqlCommand(Query, sqlconnection);
        sqlconnection.Open();
        SqlDataReader sdr = sqlcommand.ExecuteReader();

        while (sdr.Read())
        {
            int quiz_id;
            bool quiz_id1 = Int32.TryParse(sdr["qid"].ToString(), out quiz_id);

            sqlconnection = new SqlConnection(ConnectionString);
            Query =
            "SELECT id, question_id, answer, is_correct FROM dbo.question_answers WHERE question_id = " + quiz_id + "  ; ";
            sqlcommand = new SqlCommand(Query, sqlconnection);
            sqlconnection.Open();
            SqlDataReader answr = sqlcommand.ExecuteReader();
            questions[quiz_id, 0] = sdr["qq"].ToString();
            var ii = 1;
            while (answr.Read())
            {
                questions[quiz_id, ii] = answr["answer"].ToString();
                ii++;
            }
        }
    }

You could use SQL join to map question and answer and just transform the result set into an array of some sort.