CODEWITHSUNDEEP
c#ms-accessoledb
user366312
user366312

Reputation: 16988

Random query returns same records each time

https://stackoverflow.com/a/9937425/159072

I am using the following query to select 5 random records from a table,

SELECT Top 5 *
FROM   (SELECT *,
           Rnd(ID) AS RandomValue
        FROM   Words)
ORDER  BY RandomValue

This query works fine in MS Access.

But, the problem occurs when I use it in a c# application. It returns same 5 records on each occasion.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;

namespace MicrosiftAccessDbProviderFactory______Test
{
    public class MyClass 
    {
        public int ID { get; set; }
        public string Name { get; set; }
    }

    public class Program
    {
        static void Main(string[] args)
        {
            string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\db1.mdb;Persist Security Info=False";
            string providerName = @"System.Data.OleDb";
            DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);

            IDbConnection Connection = factory.CreateConnection();
            Connection.ConnectionString = connString;
            Connection.Open();

            IDbTransaction Transaction = Connection.BeginTransaction();

            IDbCommand Command = factory.CreateCommand();
            Command.Connection = Connection;
            Command.Transaction = Transaction;

            int count = 5;

            Command.CommandText = @"SELECT Top " + count + @" ID, Name
                                    FROM   (SELECT *,
                                               Rnd(ID) AS RandomValue
                                            FROM   Words)
                                    ORDER  BY RandomValue";
            IDataReader dataReader = Command.ExecuteReader();
            IList<MyClass> list = null;
            MyClass item = null;
            while (dataReader.Read())
            {
                if (list == null)
                {
                    list = new List<MyClass>();
                }

                item = new MyClass();
                item.ID = dataReader.GetInt32(0);
                item.Name = dataReader.GetString(1);

                list.Add(item);
            }

            dataReader.Close();

            Transaction.Commit();

            string str = string.Empty;
        }
    }
}

How can I solve this issue?

Upvotes: 1

Views: 115

Answers (2)

Gustav
Gustav

Reputation: 55921

You need to salt the Rnd function, like:

SELECT * FROM SomeTable ORDER BY Rnd(-Timer()*[ID])

Thus, in your query:

Command.CommandText = @"SELECT Top " + count + @" ID, [Name]
                        FROM Words
                        ORDER BY Rnd(-Timer()*[ID])";

Upvotes: 1

Jonathan Willcock
Jonathan Willcock

Reputation: 5255

This has been intriguing me. What actually fascinates me is why it works in Access. I can understand why it doesn't work with c#: the rnd() is always initialised with the same values. My solution is UGLY but it works. What I am hoping is that someone will look at the solution and see how it can be improved. At least it contains the seeds of an idea, how to make this work between Access and c#.

Step 1.

Create a table (WordsGUID) in Access identical to the target table, but with one extra field, RandomGUID, set as Data Type AutoNumber, Field Size Replication Id. For good measure, I made it indexed no duplicates, but I doubt that that is necessary.

Step 2.

Insert the following in C#

Command.CommandText ="DELETE FROM WordsGUID";
Command.ExecuteNonQuery();

Command.CommandText ="INSERT INTO WordsGUID SELECT * FROM Words";
Command.ExecuteNonQuery();

Command.CommandText ="SELECT TOP 5 * FROM WordsGUID ORDER BY RandomGUID";
IDataReader dataReader = Command.ExecuteReader();

etc..

As I said ugly, and probably horribly slow if you have a large table, but I couldn't think of anything better!

Upvotes: 0

Related Questions