随机生成器使用值从SQL Server数据库

本文关键字：SQL Server 数据库 随机 | 更新日期: 2023-09-27 18:18:54

我有一个asp.net应用程序，它是位置的随机生成器。

目前我是坐在我的代码后面的值，但我想把这些移动到我的SQL Server DB，但我不知道如何做到这一点。作为参考，我使用SQL Server管理工作室。

这是可能的还是我只是把它复杂化了?

代码后面 

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];
}
<<p> 视图/strong> 
<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>

代码提示但不能让它工作

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

随机生成器使用值从SQL Server数据库

您可以通过在SQL server中创建视图并将该视图加载到数据集中来实现这一点。这样，您就可以从数据集中进行选择，并在需要时刷新数据。

填充数据集

注意-你甚至可以更进一步，创建一个存储过程，它只会根据需要从表中给你一个随机值:)

创建一个带有输出变量的存储过程，然后在其中创建如下所示的select 

 CREATE PROC sp_RandomPlace
 @OutputVar nvarchar(100) OUTPUT
 AS
SET @OutputVar =  (select top 1 percent Place from [PlaceTable] order by newid())

然后在你的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();
}

上面的代码没有经过测试，但是您得到了jist

多亏了Alec，我终于用下面的代码让它工作了:

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;
    }
}
相关文章：