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, Oct 23 '15 at 13:02
I've added an answer to create a table and insert this stuff for you. — Alec., Oct 23 '15 at 13:07

Alec. · Accepted Answer · 2015-10-23T13:25:34.917

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 '15 at 13:25

answered Oct 23 '15 at 13:01

Alec.

5,371
5
34
69

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 – murday1983 Oct 23 '15 at 13:10
See edits, it's pretty straightforward to do, and good practice. – Alec. Oct 23 '15 at 13:13
How would I go about doing the sproc and then getting the value when my button is clicked – murday1983 Oct 23 '15 at 13:17
See edits, I've not tested that stuff but that's how it works – Alec. Oct 23 '15 at 13:24
Does the c# bit go in my BtnDecideForMe_Click. Just to let you know that I'm completely new to developing so sorry if I keep pestering you – murday1983 Oct 23 '15 at 13:30
Yes it can do. then you'll want to use the string named 'place' as your result – Alec. Oct 23 '15 at 13:30
I'm so sorry for this, I'm a little confused could you please provide a full solution for my code behind. I have had a go and the only issue I have is that it says that "The name 'ConnectionSting' does not exist in the current context." – murday1983 Oct 23 '15 at 13:43
I have added what I currently have to my post – murday1983 Oct 23 '15 at 13:44
1

You need to add a connection string, so it knows how to connect with sql server see this: http://stackoverflow.com/questions/15631602/how-to-set-sql-server-connection-string – Alec. Oct 23 '15 at 13:46
could you have a look at and update my "Code For Suggestion But Cant Get It Working" as this is what I have at the mo. – murday1983 Oct 23 '15 at 13:50
You need to set ConnectionString to an actual sql server connection string. – Alec. Oct 23 '15 at 13:56
I have just updated my bit of code, is it correct although my 'Data Source' has a '\' in it. I tried to change it to '/' but my page fell over when I clicked my button with the following message "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" – murday1983 Oct 23 '15 at 14:03

murday1983 · Answer 2 · 2015-10-23T14:27:45.767

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;
    }
}

Random Generator To Use Values From SQL Server DB

2 Answers2