CODEWITHSUNDEEP
c#sql
Adam Pacey
Adam Pacey

Reputation: 11

pulling data from a SQL database table

I am pulling 25 random numbers between the range of 1-35 questions stored in SQL based on there ID number (1,2..20 etc) this is so a user taking the test will be given random questions from the question pool in SQL I am trying to get the SQL parameter to increment within the loop variable "a" is being used as the key to enter values into the respected arrays.

Sample code is below

    protected void Question_Fetch(int[] Mixed_Questions) 
    //this array is loaded with my mixed numbers between 1-35(no duplicates) the array length //is 25 
    {
        Question_String_list = new string[25];
        Question_Answers = new string[25];
        Num_Answers = new string[25];
        Types_Of_Question = new string[25];
        User_Answers = new string[25];
        isLocked = new string[25];

        using (SqlCommand Comd = new SqlCommand("SELECT Question_String, Question_Answer, Type_Of_Question, Possible_Answers FROM Question_Pool WHERE Question_ID = @Question_ID", Conn))
        {
            SqlParameter IDParam = Comd.Parameters.Add("@Question_ID", SqlDbType.Int);
            for (int a = 0; a < Mixed_Questions.Length; a++)
            {
                int Random_Number = Mixed_Questions[a];
                Comd.Parameters.AddWithValue("@Question_ID", Random_Number);
                Conn.Open();                                        
                SqlDataReader rdr = Comd.ExecuteReader();
                if (rdr.Read())
                {
                    IDParam = Mixed_Questions[a];
                    //Random_Number = Mixed_Questions[a];
                    //Comd.Parameters.AddWithValue("@Question_ID", Random_Number);
                    Question_String_list[a] = rdr.GetValue(0).ToString();
                    Question_Answers[a] = rdr.GetValue(1).ToString();
                    Types_Of_Question[a] = rdr.GetValue(2).ToString();
                    Num_Answers[a] = rdr.GetValue(3).ToString();
                    Conn.Close();
                }
            }
        }
        Answer_Controls();
        Init_Test_Details();
    }

Upvotes: 0

Views: 759

Answers (1)

Paul Stovell
Paul Stovell

Reputation: 32715

A better way to pull 35 random questions might be to do something like this:

select top 35 * from Question_Pool order by (newid())

This effectively randomises the questions before taking 35 of them. This way you can do it all in a single query rather than 35 queries.

Upvotes: 2

Related Questions