Retrieving random records from Database
Retrieving random records from Database
Introduction
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
Code
Design
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<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">
</asp:GridView>
</div>
</form>
</body>
</html>
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;
GvResults.DataBind();
}
else
{
Response.Write("Required Records must be greater than Requried Records.");
}
}
else
{
Response.Write("Invalid Number");
}
}
public ArrayList RandomNumbers(int max)
{
ArrayList lstNumbers = new ArrayList();
Random rndNumber = new Random();
int number = rndNumber.Next(1, max + 1);
lstNumbers.Add(number);
int count = 0;
do
{
number = rndNumber.Next(1, max + 1);
if (!lstNumbers.Contains(number))
{
lstNumbers.Add(number);
}
count++;
}
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();
con.Open();
sqlda = new SqlDataAdapter(Query, con);
dt = new DataTable();
sqlda.Fill(dt);
con.Close();
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.
Note
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
its nice...