Retrieving random records from Database

Retrieving random records from Database


Sometimes it is required to get random records from the database. Each time we fetch some data, we will get a different instances of records. For example, in applications like Online Exam System, we have to fetch different sets of questions for different users giving the test.

The Requirement

1) The page will have a TextBox, a Button and a GridView.
2) The user will enter the number of required records in the TextBox.
3) On clicking the button, number of records specified by the user will be retrieved from the database randomly and get displayed in the GridView.
4) Each time the user clicks on the button different instances of records should be retrieved from the database.
5) Appropriate error message should be displayed, if the user enters an Invalid Number.
6) The required number of records should not be greater than the number of records available in the database.

1st Run

2nd Run



<html xmlns="">
<head runat="server">
<title>Untitled Page</title>
<form id="form1" runat="server">
<asp:TextBox ID="TxtRequiredRecords" runat="server"></asp:TextBox>
<asp:Button ID="BtnDisplayRecords" runat="server" OnClick="Button1_Click" Text="Display Records" /><br />
<br />
<asp:GridView ID="GvResults" runat="server">

C# Code Behind

static int TotalRecords;
SqlConnection con;
SqlDataAdapter sqlda;
DataTable dt;
protected void Page_Load(object sender, EventArgs e)
if (!Page.IsPostBack)
string query = "SELECT COUNT(*) AS TotalRecords FROM TblQuestions";
DataTable dt = GetRecords(query);
TotalRecords = Convert.ToInt32(dt.Rows[0]["TotalRecords"]);

protected void Button1_Click(object sender, EventArgs e)
bool IsInt;
int RequiredRecords;
string CSVData, query;
IsInt = Int32.TryParse(TxtRequiredRecords.Text, out RequiredRecords);
if (IsInt)
if (TotalRecords >= RequiredRecords)
CSVData = GetRandomNumbersCSV(TotalRecords, RequiredRecords);
query = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY QuestionID) AS RowID,* FROM TblQuestions) TempTable WHERE RowID IN(" + CSVData + ")";
dt = GetRecords(query);
GvResults.DataSource = dt;
Response.Write("Required Records must be greater than Requried Records.");
Response.Write("Invalid Number");

public ArrayList RandomNumbers(int max)
ArrayList lstNumbers = new ArrayList();
Random rndNumber = new Random();
int number = rndNumber.Next(1, max + 1);
int count = 0;
number = rndNumber.Next(1, max + 1);
if (!lstNumbers.Contains(number))
while (count <= 10 * max);
return lstNumbers;

public string GetRandomNumbersCSV(int max, int req)
string CSV = "";
ArrayList lstNumbers = RandomNumbers(max);
for (int i = 0; i < req; i++)
CSV += lstNumbers[i].ToString() + ",";
CSV = CSV.Remove(CSV.Length - 1);
return CSV;

public DataTable GetRecords(string Query)
con = GetConnection();
sqlda = new SqlDataAdapter(Query, con);
dt = new DataTable();
return dt;

public SqlConnection GetConnection()
con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ToString());
return con;

Code Explanation

On page load the total number of records available is retrieved from the database. When the user enters the required number of records in the TextBox and clicks on the button, the total and required numbers are passed to a method GetRandomNumbersCSV() which returns a CSV of random numbers. The CSV is used to get random records from the database.


The attachment with this post contains an attached sample Sql Sever Database in App_Data folder. So, you can run the project without any modification.

Thanks & Regards
Paritosh Mohapatra


  • Retrieving random records from Database Code Sample (
  • Comments

    Author: praveen27 Sep 2010 Member Level: Gold   Points : 0

    its nice...

    Guest Author: Nuwan12 Apr 2013

    Is possible to implement this code windows Application In c#

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: