This tutorial will demonstrate how to get a random row from your SQL database using ASP.NET 4.0 and C#.

Selecting random rows from a database can be useful for many different purposes such as displaying random images, quotes, products, or anything else that your database may contain. In this example we will choose a random number from a database.

Adding the Default.aspx Page
At this point in the tutorial I have created a new ASP.NET Empty Web Site. What we need to do first is add in a blank Web Form that we will use to test our SQL query and ensure that we are getting the correct results. To do this:

  1. Right click the project in your Solution Explorer.
  2. Select Add New Item…
  3. Select a Web Form and name it Default.aspx.
  4. Click Add.

Next, we are going to add a Label and a Button to the Web Form to allow us to test our SQL query. To do this, open Default.aspx up to Design mode and:

  1. Drag and drop a Label Control onto the Web Form.
  2. Insert a line break to the right of the Label.
  3. Drag and drop a Button Control under the Label.
  4. Change the Text property of the Button to ‘Click Me’.

Adding the Database
Next, we need to setup a simple databse that we will be using to select a random row from. In this case, I’m going to create a database with a table named Numbers that will contain 1 column, Number, and will have 10 different rows with the numbers 1 through 10 in them. To do this:

  1. Right click the project in your Solution Explorer.
  2. Select Add ASP.NET Folder -> App_Data.
  3. Right click the App_Data folder.
  4. Select Add New Item…
  5. Choose a SQL Server Database and use the default name Database.mdf.
  6. Click Add.

This should bring you to the Server/Database Explorer with your newly added database expanded. What we want to do now is add in a table named Numbers and enter some sample data into it. To do this:

  1. In the Server/Database Explorer with your Database.mdf expanded, right click the Tables folder.
  2. Select Add New Table.
  3. Add a New Column named Number, with a Data Type of int.
  4. Save the table as Numbers.
  5. Right click the Numbers table in the Server/Database Explorer and select Show Table Data.
  6. Add in the numbers 1 – 10 to new rows respectively.
     

Adding the Connection String
Next, we need to add in a connection string to our Database that we will use to send our SQL query. To do this, open up the Web.Config file for editing. In the tag, above the tag, we need to add the following for our connection string:

This will establish a connection to the Database.mdf we added earlier.

Adding the SQL Query
Next, we need to add in the code that will send the SQL query to our database and select a random row from our Numbers table. To do this, open the Default.aspx up to Design mode and:

  1. Double click the button to generate a click event method.
  2. At the top of the Default.aspx.cs we need to add the following using statements:
  3. In the Button1_Click event method, we need to add the following code:

Let’s review what this code is doing. First, we create a SqlConnection named ‘conn’. This uses the connection string we added earlier to connect to our database. Next, we create a SqlCommand named ‘cmd’ and use the following SQL query: “SELECT TOP 1 ‘ColumnName’ FROM ‘TableName’ ORDER BY NEWID()”. This query will reorganize the entire table randomly and then select the top row from it with the corresponding column name, in this case Number. After this, we simply format the SqlCommand properly by specifying our command type and then open the connection and send the query. We use the ExecuteReader() method here which returns to us a SqlDataReader that we can then use to pull the row data that we have selected. Next, we set the data in the Number column of the selected row to our label that we added earlier.

Testing
To test this out and ensure that we are indeed selecting random rows, load up the website and click the button that we added. Notice that the label displays a random number, 1 through 10, which corresponds to the selected row in the database.

The Default.aspx source looks like this:

Download Source Files