Trying to Randomly select 3 records from Data base..

Hey There,
I am relativley new to C# and VS2010 and I been assigned a task of reading the DB and randomly selecting 3 records that meet certain criteria to be displayed on the user screen, so they can choose which record they want to use. I am using C# and VS2010 and it will be a Windows application. I certainly would appreciate any help. I have looked on Google and the examle I have found there only talk about numbers....

Thanks,
Ben

Suppose that you have a query which is capable to select all records that meet the specified criteria. Then use the same query (or at least its WHERE clause) to select IDs. Then pick three ID values at random and that would be the output.

Roughly, the code might look like this:

SqlCommand cmd = new SqlCommand("SELECT ValueID FROM Value WHERE ... ", conn);    // Fill in the WHERE clause
List ids = new List();

using (SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
        ids.Add(reader.GetInt32(0));
}

int valuesCount = 3;
List selectedIDs = new List();
Random rand = new Random();

for (int i = 0; i < valuesCount; i++)
    if (ids.Count > 0)
    {
        int index = rand.Next(ids.Count);   // Random vaule 0..count-1
        selectedIDs.Add(ids[index]);
        ids.RemoveAt(index);
    }
    else
    {
        break; // There are less than 3 values selected in total
    }

// Now select rows with specified ids

StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM Value WHERE ValueID IN (");
bool first = true;

foreach (int id in selectedIDs)
{
    if (!first)
        sb.Append(", ");
    first = false;
    sb.Append(id);
}

sb.Append(")");

cmd = new SqlCommand(sb.ToString(), conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        // Do whatever needed to do with randomly selected rows
    }
}

Zoran

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ben meade15y ago
Thanks for your quick reply. This looks like it will get me going in the right direction..Ben
Zoran Horvat15y ago
Suppose that you have a query which is capable to select all records that meet the specified criteria. Then use the same query (or at least its WHERE clause) to select IDs. Then pick three ID values at random and that would be the output.

Roughly, the code might look like this:

SqlCommand cmd = new SqlCommand("SELECT ValueID FROM Value WHERE ... ", conn);    // Fill in the WHERE clause
List ids = new List();

using (SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
        ids.Add(reader.GetInt32(0));
}

int valuesCount = 3;
List selectedIDs = new List();
Random rand = new Random();

for (int i = 0; i < valuesCount; i++)
    if (ids.Count > 0)
    {
        int index = rand.Next(ids.Count);   // Random vaule 0..count-1
        selectedIDs.Add(ids[index]);
        ids.RemoveAt(index);
    }
    else
    {
        break; // There are less than 3 values selected in total
    }

// Now select rows with specified ids

StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM Value WHERE ValueID IN (");
bool first = true;

foreach (int id in selectedIDs)
{
    if (!first)
        sb.Append(", ");
    first = false;
    sb.Append(id);
}

sb.Append(")");

cmd = new SqlCommand(sb.ToString(), conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        // Do whatever needed to do with randomly selected rows
    }
}

Zoran