Displaying Records in a DataGrid in Random OrderBy Scott Mitchell
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:
To retrieve these results in random order, all you have to do is modify the SQL statement so that it looks like:
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:
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
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
Integerto the DataTable.
- Iterate through the rows of the DataTable, adding a random integer value to each row's new
- Create a DataView from the DataTable.
- Set the DataView's
Sortproperty to sort the records based on the value of the
- 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:
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
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
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
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
Sort property to
RandNum to order the results by the
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.
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.