This is an interesting question asked by Mr. Ram Nath Rao.
The history is:
Mr. Nath wants to shuffle his record(s) every time when the page refreshes, the same has been tried with the use of rand() function by him. Unfortunately, the results were not as expected.
Now, lets elaborate some interesting points towards this :
When anybody uses rand() function, what happens?
Select rand()as Random Number --creates random number
When you will repeat above statement, the new result is entirely different from the earlier one.
Now, try another similar query:
Select rand()as Random_Number,* from Employees
From the above result, note-down first column Random_Number. This column has similar values through-out the result. In other words, from above, we can sum up that the rand() function, generates a random number which is new every time we press or execute the query. Also, it doesn't change with rows when result-set retrieves more than one row(s). So, the problem of Mr. Ram Nath Rao doesn't resolve with the use of rand() function.
I recommended newid() to retrieve the solution of Mr. Ram's problem.
Check the following query sample:
Select newid()as RowId,* from Employees
Note-down first column of above result(s), every row has a new value.
Now, lets try to add more stuff to the abovee example:
Select newid()as RowId,* from Employees order by newid()
Now, regenerate above result(s) one more time, you will get different resul(s). This is the solution to the problem.
The above is a short-description how we can get random data in SqlServer2000. It is time to do all the above at application-level, I have decided to use Vs2005:
Step(s) to use:
1. Start your Vs2005
2. Create a New Website project named its as 'Shuffle Result'.
3. Rename your default web-page to 'shuffleresults.aspx'
4. Write the following lines
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="shuffleresults.aspx.cs" Inherits="shuffleresults" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>How to Shuffle Result-Sets</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid ID="dgEmp" runat="server" AlternatingItemStyle-BackColor="AliceBlue">
<AlternatingItemStyle BackColor="AliceBlue" />
</asp:DataGrid>
</div>
<br />
<div>
<asp:Button ID="btnShuffle" runat="server" ToolTip="Click to Shuffle Resuts" Text="Shuffle-Results"
OnClick="btnShuffle_Click" />
<asp:Button ID="btnNormal" runat="server" ToolTip="Click to Check Normal Result"
Text="Normal Results" OnClick="btnNormal_Click" />
</div>
</form>
</body>
</html>
[/code
5. Press F7 or choose code-view from Solution-Explorer
6. Add following sort-of-code in 'shuffleresults.aspx.cs'
/* This Example is a part of different
* examples shown in Book:
* C#2005 Beginners: A Step Ahead
* Written by: Gaurav Arora
* Reach at : g_arora@hotmail.com */
#region Code Region
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class shuffleresults : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData("ID");
}
}
void BindData(string strFldName)
{
SqlConnection myCon = new SqlConnection("Server=(local);uid=sa;pwd=;database=hrnpayroll");
SqlCommand myCmd = new SqlCommand("Select Top 10 * from Employees order by " + strFldName, myCon);
myCon.Open();
SqlDataReader myDr = myCmd.ExecuteReader();
dgEmp.DataSource = myDr;
dgEmp.DataBind();
myDr.Close();
myCon.Close();
}
protected void btnShuffle_Click(object sender, EventArgs e)
{
//Here just pass the newid() function to shuffle the output and see the magic
BindData("newid()");
}
protected void btnNormal_Click(object sender, EventArgs e)
{
//reset the dataresult
BindData("id");
}
}
#endregion Code Region
7. Run the above application by pressing F5.
8. It will generate following result(s):
The above is described "How one can shuffle the result-sets".
 Attachments
Shuffle result -datagrid (20033-31231-shuffleresults.aspx)Shuffle results - datagrids -codebehind (20033-31232-shuffleresults.aspx.cs)
|
No responses found. Be the first to respond and make money from revenue sharing program.
|