Random Generator To Use Values From SQL Server DB

Question

I have an asp.net application which is a random generator of places.

At present I am the values sat in my code behind but I would like to move these into my SQL Server DB but I have no idea on how to do this. For reference I am using SQL Server Management Studio.

Is this possible or am I just over complicating it?

Code Behind

protected void BtnDecideForMe_Click(object sender, EventArgs e)
{
    List<string> Eat = new List<string>();
    Eat.Add("Chinese Buffet");
    Eat.Add("Harvester");
    Eat.Add("Frankie & Benny's");
    Eat.Add("Hungry Horse");
    Eat.Add("Blaize");
    Eat.Add("Chiquito");
    Eat.Add("Cafe Football");
    Eat.Add("Nando's");

    Random Place = new Random();
    int Places = Place.Next(0, Eat.Count);
    txtDestination.Text = Eat[Places];
}

View

<div class="row">
    <div class="col-sm-3">
        <asp:TextBox class="form-control" ID="txtDestination" runat="server" disabled></asp:TextBox>
    </div>
    <div class="col-sm-2">
        <asp:Button class="btn btn-success" ID="BtnDecideForMe" runat="server" Text="Decide For Me" OnClick="BtnDecideForMe_Click" />
    </div>
</div>

Code For Suggestion But Cant Get It Working

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

namespace PaydayLunchGenerator
{
    public partial class _Default : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void BtnDecideForMe_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
            "Data Source=DEV-116\\ONLINE;" +
            "Initial Catalog=PaydayLunch;" +
            "Integrated Security=True;";
            conn.Open();

            //using (SqlConnection conn = new SqlConnection(PaydayLunchConnectionString1))
            using (SqlCommand cmd = new SqlCommand("dbo.GetRandomPlace", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                // set up the parameters
                cmd.Parameters.Add("@OutputVar", SqlDbType.VarChar).Direction = ParameterDirection.Output;

                // open connection and execute stored procedure
                conn.Open();
                cmd.ExecuteNonQuery();

                // read output value from @OutputVar
                string place = Convert.ToString(cmd.Parameters["@OutputVar"].Value);
                conn.Close();

                txtDestination.Text = place;
            }
        }
    }
}

@Alec All the ones in my post plus what ever anyone else suggests so this list may grow. I do have another 8 to also add to my list but not yet — murday1983
– murday1983, Commented Oct 23, 2015 at 13:02
I've added an answer to create a table and insert this stuff for you. — Alec.
– Alec., Commented Oct 23, 2015 at 13:07

Alec. · Accepted Answer · 2015-10-23 13:25:34Z

2

You can achieve this by creating a view in SQL server and loading that view into a dataset. That way you can select from the dataset and refresh the data whenever you require.

Populating Dataset

Note - You could even go a step further and create a stored procedure that will just give you a random value from the table on demand :)

Create a stored procedure with an output variable, then inside create a select like this

 CREATE PROC sp_RandomPlace
 @OutputVar nvarchar(100) OUTPUT
 AS

SET @OutputVar =  (select top 1 percent Place from [PlaceTable] order by newid())

Then in your c#

using(SqlConnection conn = new SqlConnection(ConnectionString ))
using(SqlCommand cmd = new SqlCommand("dbo.sp_RandomPlace", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;

    // set up the parameters

    cmd.Parameters.Add("@OutputVar", SqlDbType.Nvarchar).Direction = ParameterDirection.Output;



    // open connection and execute stored procedure
    conn.Open();
    cmd.ExecuteNonQuery();

    // read output value from @OutputVar
    string place= Convert.ToString(cmd.Parameters["@OutputVar"].Value);
    conn.Close();
}

The code above is untested but you get the jist

edited Oct 23, 2015 at 13:25

answered Oct 23, 2015 at 13:01

Alec.

5,5255 gold badges40 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

murday1983 Over a year ago

No I have the table already setup with the values in. I want to know if it's possible to use this table for my random generator to use rather than my having to keep adding Eat.Add("XX"); every time a new place is decided on. At the moment on click of the button it uses my list in my code behind but I want to move this out as there could be a lot of entries in it over time

Alec. Over a year ago

See edits, it's pretty straightforward to do, and good practice.

murday1983 Over a year ago

How would I go about doing the sproc and then getting the value when my button is clicked

Alec. Over a year ago

See edits, I've not tested that stuff but that's how it works

Alec. Over a year ago

You need to add a connection string, so it knows how to connect with sql server see this: stackoverflow.com/questions/15631602/…

|

murday1983 · Accepted Answer · 2015-10-23 14:27:45Z

With the thanks to Alec, i manged to get it working eventually using the following:

protected void BtnDecideForMe_Click(object sender, EventArgs e)
{
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = "Data Source=DEV-116\\ONLINE;Initial Catalog=PaydayLunch;Integrated Security=True";

    using (SqlCommand cmd = new SqlCommand("dbo.GetRandomPlace", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        // set up the parameters
        cmd.Parameters.Add("@OutputVar", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output;

        // open connection and execute stored procedure
        conn.Open();
        cmd.ExecuteNonQuery();

        // read output value from @OutputVar
        string place = Convert.ToString(cmd.Parameters["@OutputVar"].Value);
        conn.Close();

        txtDestination.Text = place;
    }
}

Collectives™ on Stack Overflow

Random Generator To Use Values From SQL Server DB

2 Answers 2

12 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related