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.
We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect’s help, we were able to avoid any headaches!
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 DatabaseNext, 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:
    In the Server/Database Explorer with your Database.mdf expanded, right click the Tables folder.
  1. Select Add New Table.
  2. Add a New Column named Number, with a Data Type of int.
  3. Save the table as Numbers.
  4. Right click the Numbers table in the Server/Database Explorer and select Show Table Data.
  5. Add in the numbers 1 – 10 to new rows respectively.
     
Adding the Connection StringNext, 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 <configuration> tag, above the <system.web> tag, we need to add the following for our connection string:
Code Block
Ask a Question
Download Source
 Web.Config
The connection string to our database.
<connectionStrings>
    <add name="NumbersConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
This will establish a connection to the Database.mdf we added earlier.
If you’re looking for a really good web host, try Server Intellect – we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!
Adding the SQL QueryNext, 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:
    Code Block
    Ask a Question
    Download Source
     Default.aspx.cs
    The using statements we need for sending SQL Queries.
    using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
  3. In the Button1_Click event method, we need to add the following code:
    Code Block
    Ask a Question
    Download Source
     Default.aspx.cs
    The code to send our SQL query.
    protected void Button1_Click(object sender, EventArgs e)
{
    //Create a connection to our Numbers database, using the connection string we added to the Web.Config file
    SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["NumbersConnectionString"].ConnectionString);
    //Create our SQL command to select a random row
    SqlCommand cmd = new SqlCommand("SELECT TOP 1 Number FROM Numbers ORDER BY NEWID()", conn);
 
    cmd.CommandType = CommandType.Text;
 
    //open the connection and send the query
    using (conn)
    {
        conn.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
 
        if (rdr.Read())
        {
            //Set our text to the random number selected
            Label1.Text = rdr["Number"].ToString();
        }
    }
}
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.
Try Server Intellect for Windows Server Hosting. Quality and Quantity!
TestingTo 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. 
Get RandRow Sql ASP4 csharp