Select TOP N random rows from database in DataTable using C# in Windows Application

RPA
 
on May 12, 2021 12:18 AM
463 Views

Hi!

I used this UnSelect (UnCheck) RadioButton when moving to next question in Quiz application in Windows Form

I need get 25 question from database table by random, because I have more than 300 questions on table.

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 12, 2021 05:07 AM

Hi RPA,

Refer below code.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace QuesQuiz
{
    public partial class Form1 : Form
    {
        public int i;
        static int index = 0;
        static int correct = 0;
        static string answer = "";
        static DataTable dtQuestions;
        int noQuestion = 25;
        public struct Question { public string question; }
        public struct Option { public string optOne, optTwo, optThree, optFour, optFive; }
        public Form1()
        {
            InitializeComponent();
            timer_quiz.Enabled = false;
            timer_quiz.Interval = 6000;
            i = noQuestion;
            grpBox.Visible = false;
            PopulateQuestions();
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            timer_quiz.Enabled = true;
            timer_quiz.Start();
            GenerateQuestionsOptions(index);
            lblAnswer.Visible = false;
            grpBox.Visible = true;
        }

        public void GenerateQuestionsOptions(int index)
        {
            // Question based on Index
            Question question = new Question();
            question.question = dtQuestions.Rows[index]["QuestionDescription"].ToString();

            // Options based on Question
            Option options = new Option();
            options.optOne = dtQuestions.Rows[index]["OptionOne"].ToString();
            options.optTwo = dtQuestions.Rows[index]["OptionTwo"].ToString();
            options.optThree = dtQuestions.Rows[index]["OptionThree"].ToString();
            options.optFour = dtQuestions.Rows[index]["OptionFour"].ToString();
            options.optFive = dtQuestions.Rows[index]["OptionFive"].ToString();

            // Answer based on Question
            answer = PopulateCorrectAnswer(dtQuestions.Rows[index]["QuestionDescription"].ToString());

            // Adding options to List for shuffling options
            List<string> optionsList = new List<string>();
            optionsList.Add(options.optOne);
            optionsList.Add(options.optTwo);
            optionsList.Add(options.optThree);
            optionsList.Add(options.optFour);
            optionsList.Add(options.optFive);

            // Shuffle options List
            List<string> shuffledOptions = optionsList.OrderBy(a => Guid.NewGuid()).ToList();
            //List<string> shuffledOptions = RandomizeList(optionsList);

            // Assigning question and options
            lblQuestion.Text = (index + 1) + " : " + question.question.ToUpper();
            rbOptionOne.Text = shuffledOptions[0];
            rbOptionTwo.Text = shuffledOptions[1];
            rbOptionThree.Text = shuffledOptions[2];
            rbOptionFour.Text = shuffledOptions[3];
            rbOptionFive.Text = shuffledOptions[4];

            rbOptionOne.Checked = false;
            rbOptionTwo.Checked = false;
            rbOptionThree.Checked = false;
            rbOptionFour.Checked = false;
            rbOptionFive.Checked = false;
        }

        public List<string> RandomizeList(List<string> originalList)
        {
            List<string> randomList = new List<string>();
            Random random = new Random();
            string value = default(string);
            while (originalList.Count() > 0)
            {
                var nextIndex = random.Next(0, originalList.Count());
                value = originalList[nextIndex];
                randomList.Add(value);
                originalList.RemoveAt(nextIndex);
            }
            return randomList;
        }

        // Get random 25 questions and options from database
        private void PopulateQuestions()
        {
            DataTable dt = new DataTable();
            string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT TOP " + noQuestion + " * FROM QuestionTable ORDER BY NEWID()", con))
                {
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        con.Open();
                        da.Fill(dt);
                        con.Close();
                    }
                }
            }
            dtQuestions = new DataTable();
            dtQuestions = dt;
        }

        //Get answer for question from database
        private string PopulateCorrectAnswer(string questionDescription)
        {
            DataTable dt = new DataTable();
            string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("GetCorrectAnswer", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Question", questionDescription);
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        con.Open();
                        da.Fill(dt);
                        con.Close();
                    }
                }
            }
            return dt.Rows[0].ItemArray[0].ToString();
        }

        private void btnBack_Click(object sender, EventArgs e)
        {
            timer_quiz.Enabled = false;
            timer_quiz.Interval = 60000;
            i = 60;
            index = 0;
            GenerateQuestionsOptions(index);
            lblAnswer.Visible = false;
            btnBack.Visible = false;
            btnStart.Visible = true;
        }

        private void timer_quiz_Tick(object sender, EventArgs e)
        {
            if (i > 0)
            {
                i = i - 2;
                lblQuestion.Text = "0" + i.ToString();
                foreach (Control control in grpBox.Controls)
                {
                    if (control.GetType().Name.ToLower() == "radiobutton")
                    {
                        if ((control as RadioButton).Checked)
                        {
                            if (answer.Trim().ToLower() == (control as RadioButton).Text.Trim().ToLower())
                            {
                                correct++;
                            }
                        }
                    }
                }
                if (index < dtQuestions.Rows.Count - 1)
                {
                    index++;
                    GenerateQuestionsOptions(index);
                    lblAnswer.Visible = false;
                }
            }
            else
            {
                grpBox.Visible = false;
                btnStart.Visible = false;
                btnBack.Visible = true;
                lblAnswer.Visible = true;
                timer_quiz.Stop();
                lblAnswer.Text = correct + " correct answer out of " + dtQuestions.Rows.Count + " questions.";
            }
        }
    }
}