To read the article online, visit http://www.4GuysFromRolla.com/articles/051003-1.aspx

Displaying Records in a DataGrid in Random Order

By Scott Mitchell


Introduction


Imagine that you are building an online shopping site using ASP.NET. (Good choice! :-)) You decide that you want to have a list of your products displayed on a DataGrid. However, to improve the chances of making a sale, you decide that you want the products displayed in a random order on each visit to the page.

The question now stands: how can you have a DataGrid display its records in random order? There are two approaches that can be taken to surmount this problem:

  1. Modify the SQL statement that is used to populate the DataGrid. The SQL statement can be changed to return the records in random order.
  2. Retrieve the database results as you would normally, placing the results in a DataSet. Then, programmatically add an additional column to DataSet's DataTable, one that holds a random number. Finally, create a DataView and sort the DataView by this random number column.

In this article we'll examine both techniques and discuss the pros and cons of each.

Changing the SQL Statement


There's a very simple way to return a SQL query in random order - simply add ORDER BY NEWID() (The NEWID() feature was added to SQL Server 7.0.) That is, imagine your SQL query to retrieve the products to display in the DataGrid was something simple like:

SELECT ProductID, Name, Description, Price
FROM Products

To retrieve these results in random order, all you have to do is modify the SQL statement so that it looks like:

SELECT ProductID, Name, Description, Price
FROM Products
ORDER BY NEWID()

Each time this query is run, the rows will be returned in a random order. For more on this method, check out Using NEWID() to Randomly Sort Records and Randomly Sorting Query Results.

If you want to only show a subset of the records in random order, you can limit the number of records returned by using the TOP keyword like so:

SELECT TOP N ProductID, Name, Description, Price
FROM Products
ORDER BY NEWID()

Where N is the number of records you want to have returned (for example, 10). There is a live demo showing how to use this technique to display 10 random records from the tblFAQ table from the ASPFAQs.com database.

Programmatically Randomizing the Results


If you are not using SQL Server 7.0 or up you will not be able to take advantage of the NEWID() approach. Even if you are using SQL Server 7.0, you might not be able to adjust the SQL query; for example, you may have to call a provided stored procedure that returns the records in a specific order. Aside from overcoming these limitations, the programmatic approach also allows for more complex cases, like retrieving the 10 products that have sold the best, and displaying just those 10 in random order. (Yes, I know you can do the same with a SQL query only that employs derived tables, but it leads to a pretty messy SQL query and such a discussion is a bit beyond the scope of this article.)

Anywho, in order to programmatically display the results in random order, we will perform the following steps:

  1. Fill a DataSet with the SQL query results.
  2. Access the DataSet's DataTable that was populated.
  3. Add a new column called RandNum of type Integer to the DataTable.
  4. Iterate through the rows of the DataTable, adding a random integer value to each row's new RandNum column.
  5. Create a DataView from the DataTable.
  6. Set the DataView's Sort property to sort the records based on the value of the RandNum column.
  7. Bind the DataView to the DataGrid.

While this may seem like an inordinate number of steps, it really boils down to just a few lines of code. Let's take a look at the code:

Dim myConnection as New SqlConnection(connectionString)

Const strSQL as String = "sp_Popularity"
Dim myCommand as New SqlCommand(strSQL, myConnection)

'Fill the DataSet and close the connection...
Dim myDataAdapter as New SqlDataAdapter(myCommand)    
Dim myDataSet As New DataSet()
myDataAdapter.Fill(myDataSet, "FAQs")
myConnection.Close()
    
' Now, randomly order the DataTable's Rows Collection
' Start by adding a RandNum integer column to the DataTable
Dim myDataTable as DataTable = myDataSet.Tables(0)
myDataTable.Columns.Add(New DataColumn("RandNum", GetType(Integer)))

' Now, add a random number for the RandNum value for each row
Dim i as Integer
Dim rndNum as New Random()
For i = 0 to myDataTable.Rows.Count - 1
	myDataTable.Rows(i)("RandNum") = rndNum.Next(10000)
Next i

' Next, create a DataView and have it sorted by the RandNum column
Dim myDataView as DataView = myDataTable.DefaultView
myDataView.Sort = "RandNum"
        
'Finally, bind the DataView to the DataGrid
dgRandOrder.DataSource = myDataView
dgRandOrder.DataBind()    
[View a Live Demo!]

This code first creates a connection to the database called myConnection. Next, it creates a SqlCommand object to operator on the created connection calling the stored procedure sp_Popularity. I opted to create a demo calling this stored procedure for two reasons: first, it illustrates a case where you might have a stored procedure to call but cannot write your own SQL query, yet you can still order the results randomly; secondly, the sp_Popularity stored procedure returns the 10 most popular FAQs from the ASPFAQs.com database. That is, it presents a subset of the FAQs already ordered by their ViewCount attribute. So, with this example we'll show displaying only the most popular FAQs in random order.

Next, the database query is performed and the results are dumped into a DataSet named myDataSet. Following that, we reference the DataTable that was created for the DataSet and add a new DataColumn to the DataTable. This new DataColumn is called RandNum and accepts integer values.

After that, we iterate through the rows of the DataTable, assigning a random value for each of the row's new RandNum column. (Note that the rndNum.Next(10000) returns a random number between 0 and 99,999 - see the technical docs for more information on the Random class.) Once we have assigned a random number to each row, we can then sort the results using a DataView object. As you can see, we reference the DataTable's DefaultView property, which returns a DataView over the DataTable's data. Then, we simply set the Sort property to RandNum to order the results by the RandNum DataColumn.

Finally, we bind this DataView to the DataGrid. The results? The DataGrid rows are ordered by the random numbers assigned to the DataTable's rows. Therefore, the results are displayed in a random order. Try out the live demo to play with the results and see the full source code.

Conclusion


In this article we examined two methods for displaying records in a DataGrid in random order. The first method involved reconfiguring the SQL query statement to include: ORDER BY NEWID(). The second method involved programmatically adding a new DataColumn to the query results, and then assigning each row a random number. Finally, a DataView was used to sort the data by the specified random number column, and this DataView was bound to the DataGrid.

Happy Programming!

  • By Scott Mitchell

  • Article Information
    Article Title: ASP.NET.Displaying Records in a DataGrid in Random Order
    Article Author: Scott Mitchell
    Published Date: May 10, 2003
    Article URL: http://www.4GuysFromRolla.com/articles/051003-1.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers