SQL randomize rows and columns

Question

May I know, how can I randomize data gathered and randomize selected using SQL I will integrate this to c# winform

For example

main content-----content1-----content2-----content3-----content4

To

main content-----content1-----content4-----content2-----content3

Like a multiple choice question, randomize the question and its choices. Thanks in advance.

edit:

this is my class.

public class qbank
{
    string question, c1, c2, c3, c4, ans;

    public string Ans
    {
        get { return ans; }
        set { ans = value; }
    }

    public string C1
    {
        get { return c1; }
        set { c1 = value; }
    }

    public string C2
    {
        get { return c2; }
        set { c2 = value; }
    }

    public string C3
    {
        get { return c3; }
        set { c3 = value; }
    }

    public string C4
    {
        get { return c4; }
        set { c4 = value; }
    }

    public string Question
    {
        get { return question; }
        set { question = value; }
    }

this is my winform

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

namespace frmMain
{
    public partial class frmPIPETest : Form
    {
        string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;
        Data Source=C:\Users\rehpe\Documents\Visual Studio 2015\Projects\panibago\questionbank.accdb;
        Persist Security Info=False";
        string query = "";
        OleDbConnection conn = null;

        public frmPIPETest()
        {
            InitializeComponent();
        }

        private void frmPIPETest_FormClosing(object sender, FormClosingEventArgs e)
        {
            System.Media.SystemSounds.Beep.Play();
            DialogResult dialog = MessageBox.Show("Do you really want to exit?",
                                  "Exit Program",
                                  MessageBoxButtons.YesNo,
                                  MessageBoxIcon.Question,
                                  MessageBoxDefaultButton.Button2);
            if (dialog == DialogResult.Yes)
            {
                Application.ExitThread();
            }
            else
            {
                e.Cancel = true;
            }
        }

        private void btnBack_Click(object sender, EventArgs e)
        {
            System.Media.SystemSounds.Beep.Play();
            DialogResult dialog = MessageBox.Show("Go back to topic selection?",
                                  "Return",
                                  MessageBoxButtons.YesNo,
                                  MessageBoxIcon.Question,
                                  MessageBoxDefaultButton.Button2);
            if (dialog == DialogResult.Yes)
            {
                frmPIPE frm = new frmPIPE();
                frm.Show();
                Hide();
            }
        }

        int i = 0;
        private void timer1_Tick(object sender, EventArgs e)
        {
            i++;
            lblTimer.Text = i.ToString() + "s";
        }

        public qbank()
        {
            IEnumerable<qbank> content = new List<qbank>
            var random = new Random();
            var result = content
                .Select(c =>
                {
                    var strings = new[]
                    {
                        c.C1,
                        c.C2,
                        c.C3,
                        c.C4,
                    }.OrderBy(x => random.Next())
                    .ToArray();
                    return new qbank
                    {
                        Question = c.Question,
                        C1 = strings[0],
                        C2 = strings[1],
                        C3 = strings[2],
                        C4 = strings[3],
                    };
                })
            .OrderBy(x => random.Next());
        }
    }
}

Im attaching pictures since there are errors to where I created my list.

Expected Interface

Is the content being generated through SQL ? Can you share the code with us ? — sagi, Sep 10 '17 at 10:39
@sagi i have a database (access) that is connected to my winform. i still dont have the code since i dont know how to query it. i tried to query random data. but all i can do is to randomize its rows, i also wanted to randomize selected columns. — Jepher John Chang, Sep 10 '17 at 10:50
@JepherJohnChang Here is an example (https://stackoverflow.com/questions/5383498/shuffle-rearrange-randomly-a-liststring). You may need to create a List of all your choices and then randomize it with a method. — Odonno, Sep 10 '17 at 10:54

score 1 · Answer 1 · answered Sep 10 '17 at 10:39

1

You can order by rand() in mysql query:

Example:

SELECT * FROM content ORDER BY rand()

RAND([N])

Returns a random floating-point value v in the range 0 <= v < 1.0. To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j − i)).

answered Sep 10 '17 at 10:39

Abhilekh Singh

2,845
2
18
24

yes i tried that. but it can only randomize the rows. i wanted to randomize not only the rows but its columns within that row. – Jepher John Chang Sep 10 '17 at 10:51
@JepherJohnChang . . . Randomize the columns in the application layer. – Gordon Linoff Sep 10 '17 at 11:08
@GordonLinoff how? – Abdul Samad Sep 10 '17 at 14:24

Aleks Andreev · Answer 2 · 2017-09-10T12:21:18.787

1

I'm assuming you already have a DTO class in your code like this:

public class Table
{
    public string MainContent { get; set; }
    public string Content1 { get; set; }
    public string Content2 { get; set; }
    public string Content3 { get; set; }
    public string Content4 { get; set; }
}

now you can randomize rows and columns with Linq:

IEnumerable<Table> content = /* get it from DbContext */;
var random = new Random();

var result = content
    .Select(c =>
    { 
        var strings = new[]
            {
                c.Content1,
                c.Content2,
                c.Content3,
                c.Content4,
            }.OrderBy(x => random.Next()) // randomize columns
            .ToArray();

        return new Table
        {
            MainContent = c.MainContent,
            Content1 = strings[0],
            Content2 = strings[1],
            Content3 = strings[2],
            Content4 = strings[3],
        };
    })
    .OrderBy(x => random.Next());  // randomize rows

NOTE: If your table is large add filtering (Where) and pagination (Skip and Take) before first Select like this:

.Where(t => t.MainContent.Contains("abc"))
.Skip(100)
.Take(10)
.AsEnumerable()

EDIT: You can find working example here

edited Sep 10 '17 at 12:21

answered Sep 10 '17 at 11:10

Aleks Andreev

7,016
8
29
37

yes i have a dto class. sorry but im lost at your x, t and c variable. since they are not declared? or i have to set them to my condition? – Jepher John Chang Sep 10 '17 at 11:59
@JepherJohnChang `x`, `t` anc `c` is a lambda parameters. I've added a link to demo. – Aleks Andreev Sep 10 '17 at 12:23
thank you. ill get back to my code as soon as i got home. – Jepher John Chang Sep 10 '17 at 12:58
This will not randomize the columns I believe – Abdul Samad Sep 10 '17 at 14:23
@AbdulSamad I've tested this code on in-memory collections and it's works fine. Could you please give me an example when this code doesn't work? – Aleks Andreev Sep 10 '17 at 16:26
@AleksAndreev sorry I was wrong. It's working fine :) – Abdul Samad Sep 11 '17 at 11:43
@AleksAndreev is there a way i can messge you? i cant post my code thru comment. – Jepher John Chang Sep 12 '17 at 08:54
@JepherJohnChang you may [edit](https://stackoverflow.com/posts/46139771/edit) your question or [create a new one](https://stackoverflow.com/questions/ask). – Aleks Andreev Sep 12 '17 at 08:58
@AleksAndreev I have edited my question. Sorry I am really new to this thing. – Jepher John Chang Sep 12 '17 at 10:58
@JepherJohnChang you need to fetch data from database. See [this](https://stackoverflow.com/questions/15148588/) question. Then map `result` to UI controls. Please do not expect that I or some one else on SO will write entire code for you. – Aleks Andreev Sep 12 '17 at 12:18
sorry. i didnt mean to expect that. i just posted it for easier to pin point my problem. i dont know the terms i need to look to. – Jepher John Chang Sep 13 '17 at 04:02

SQL randomize rows and columns

2 Answers2