Returning a random row from sql database and displaying in a html table using ASP.NET

Question

Im new to using ASP.NET and would like to know how I can select a random row from a sql database and then display the fields in a html table on a separate page. It is intended that the user can press on button which will retrieve a random movie from the database and then display the movie details in a html table on a new page. I am not sure how to go about this and have been trying to use labels to display the data. Here is a sample of the code so far:

    private SqlConnection conn;
    protected void Page_Load(object sender, EventArgs e)
    {
            ConnectionStringSettings connString = ConfigurationManager.ConnectionStrings ["MovieAppConnectionString1"];
            conn = new SqlConnection(connString.ConnectionString);
    }

    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            conn.Open();
            string queryString = "SELECT TOP 1 * FROM Movie ORDER BY NEWID()";
            SqlCommand cmd = new SqlCommand(queryString, conn);
            {
                SqlDataReader reader = cmd.ExecuteReader();
                StringBuilder MyStringBuilder = new StringBuilder();
                while (reader.Read())
                {
                    Image2.Text = reader[2].ToString();
                    Label1.Text = reader[1].ToString();
                    Desc.Text = reader[3].ToString();
                    Direc.Text = reader[5].ToString();
                    Strs.Text = reader[7].ToString();
                    Rtime.Text = reader[4].ToString();
                    ImdbRt.Text = reader[8].ToString();
                }
            }
       }
        finally
        {
            conn.Close();
        }
        Server.Transfer("MovieSelected.aspx");

Answer 1

In your aspx.cs file:

int iLength = 0; 
int index = 0;

DataTable dt = new DataTable();
dt = SqlComm.SqlDataTable("SELECT * FROM Movie");

object obj = new object();
obj = SqlComm.SqlReturn("SELECT COUNT (yourTargetColumn) FROM yourTable");

if (obj != null)                    
     iLength = Convert.ToInt32(obj); 

string[] stringArray = new string[iLength];
for (index = 0; index < iLength; index++) 
{
     stringArray[index] = (string)dt.Rows[index]["yourTargetColumn"];                   
}
foreach (string strArray in stringArray)
{    
     Label yourLabel = new Label();
     PH.Controls.Add(yourLabel);
}

In your .aspx file:

<asp:PlaceHolder ID="PH" runat="server"></asp:PlaceHolder>          

Add a class to your App_Code folder named "SqlComm.cs":

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public class SqlComm
{
   static string DatabaseConnectionString = "your connection string";

   public static object SqlReturn(string sql)
    {
        using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);                  
            object result = (object)cmd.ExecuteScalar();
            return result;           

        }
    }

   public static DataTable SqlDataTable(string sql)
   {
       using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
       {
           SqlCommand cmd = new SqlCommand(sql, conn);
           cmd.Connection.Open();
           DataTable TempTable = new DataTable();
           TempTable.Load(cmd.ExecuteReader());
           return TempTable;
       }
}

---

Note: Do not forget to add the using System.Data.SqlClient to your code. Also, you just have to customize the SELECT command in order to get the data you want.

Answer 2

Change your sql server query from :

SELECT TOP 1 * FROM Movie ORDER BY NEWID()

to

SELECT TOP 1 * FROM Movie ORDER BY RAND()