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:
Modify the SQL statement that is used to populate the DataGrid. The SQL statement can be changed
to return the records in random order.
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()
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:
Fill a DataSet with the SQL query results.
Access the DataSet's DataTable that was populated.
Add a new column called RandNum of type Integer to the DataTable.
Iterate through the rows of the DataTable, adding a random integer value to each row's new
RandNum column.
Create a DataView from the DataTable.
Set the DataView's Sort property to sort the records based on the value of the
RandNum column.
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()
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.
