如何将 SQL 查询的结果保存在 C#.net 中的变量中？

How to save the result of a SQL query in a variable in C#.net?

提问by Akshay

namespace Hotel
{
    public partial class Billing : Form
    {
        SqlConnection con = new SqlConnection();
        SqlDataAdapter da;
        SqlCommand cmd = new SqlCommand();
        DataTable dt = new DataTable();
        public Billing()
        {
            InitializeComponent();
        }

        private void Billing_Load(object sender, EventArgs e)
        {
            con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Projects\c# assignments\Hotel Manager\Hotel\database\master.mdf;Integrated Security=True;User Instance=True";
            //loadData();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            con.Open();
            int rno = Int32.Parse(txtRoom.Text);


            cmd.CommandText = "SELECT SUM(ItemRate) FROM logs WHERE RoomNo=" + rno +"";
            int amt = (int)cmd.ExecuteScalar();   //arror is at this part

       //ExecuteScalar: Connection property has not been initialized.

            cmd.CommandText = "INSERT INTO bill VALUES('" + txtBillNo.Text.ToString() + "','" + txtRoom.Text.ToString() + "','" + amt.ToString() + "')";
            con.Close();
            txtBillNo.Text = "";
            txtRoom.Text = "";
            BillView bv = new BillView();
            bv.ShowDialog();
        }
    }
}

please help me with this error i am not able to store the the SQL query result into a variable???

请帮我解决这个错误我无法将 SQL 查询结果存储到变量中？？？

采纳答案by Tim Schmelter

1. You are open for SQL-Injection. Don't concatenate strings to build your query. Instead use SQL-Parameters.
2. Use using-statementfor you connection (and everything else implementing IDisposable). Dispose will also close the connection, with usingeven on error.
3. The reason for the exception is that you don't have initialized the connection of the SqlCommandsince you have't specified the connection. You can use the propertyor the appropriate constructor.

1. 您对SQL-Injection 持开放态度。不要连接字符串来构建查询。而是使用 SQL 参数。
2. 使用using-statement你的连接（以及其他一切执行IDisposable）。Dispose 也会关闭连接，using即使出错。
3. 异常的原因是你没有初始化连接，SqlCommand因为你没有指定连接。您可以使用属性或适当的构造函数。

Here's an example:

下面是一个例子：

int amt;  
using (var con = new SqlConnection(ConnectionString)) {
    var sql = "SELECT SUM(ItemRate) FROM logs WHERE RoomNo = @RoomNo";
    using (var cmd = new SqlCommand(sql, con)) {
        cmd.Parameters.AddWithValue("@RoomNo", Int32.Parse(txtRoom.Text));
        con.Open();
        amt = (int)cmd.ExecuteScalar();
    }
}

回答by Yehuda Shapira

It's not enough to just openthe connection;
You need to associate conwith cmd.

仅仅打开连接是不够的；
您需要con与cmd.

回答by Bridge

It's exactly as the error described, you haven't set the Connectionproperty of your SQLCommand.

这正是作为错误描述，你没有设置Connection你的财产SQLCommand。

Try adding:

尝试添加：

cmd.Connection = con;

before you call ExecuteScalar().

在你打电话之前ExecuteScalar()。

回答by Rohit Vats

You've opened up a SqlConnection, but you haven't told the SqlCommand object to use it. Try adding this line:

您已经打开了一个 SqlConnection，但您还没有告诉 SqlCommand 对象使用它。尝试添加这一行：

cmd.Connection = con;

before you execute your query.

在执行查询之前。

回答by Yahia

There are several problems with the code you show - esp. some seroius security problems, I strongly advise you to read up on SQL injectionand prepared statements/parameters and using.

您显示的代码有几个问题 - 尤其是。一些严重的安全问题，我强烈建议您阅读SQL 注入和准备好的语句/参数并使用.

Just some quick correction/comments:

只是一些快速更正/评论：

namespace Hotel
{
    public partial class Billing : Form
    {
        SqlConnection con = new SqlConnection();
        SqlDataAdapter da;
        SqlCommand cmd = new SqlCommand();
        DataTable dt = new DataTable();
        public Billing()
        {
            InitializeComponent();
        }

        private void Billing_Load(object sender, EventArgs e)
        {
            con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Projects\c# assignments\Hotel Manager\Hotel\database\master.mdf;Integrated Security=True;User Instance=True";
            //loadData();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Projects\c# assignments\Hotel Manager\Hotel\database\master.mdf;Integrated Security=True;User Instance=True";
            con.Open();
            int rno = Int32.Parse(txtRoom.Text);

            cmd.Connection = con; // This solves the problem you see
            // HERE you SHOULD use a SQL paramter instead of appending strings to build your SQL !!!
            cmd.CommandText = "SELECT SUM(ItemRate) FROM logs WHERE RoomNo=" + rno +"";
            int amt = (int)cmd.ExecuteScalar();   //arror is at this part


            // HERE you SHOULD use a SQL paramter instead of appending strings to build your SQL !!!
            // Another point: you build an INSERT but never execute it ?!?
            cmd.CommandText = "INSERT INTO bill VALUES('" + txtBillNo.Text.ToString() + "','" + txtRoom.Text.ToString() + "','" + amt.ToString() + "')";
            con.Close();
            txtBillNo.Text = "";
            txtRoom.Text = "";
            BillView bv = new BillView();
            bv.ShowDialog();
        }
    }
}

回答by Zo Has

You have not provided the connection string inside your button1_click.

您尚未在 button1_click 中提供连接字符串。

con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Projects\c# assignments\Hotel Manager\Hotel\database\master.mdf;Integrated Security=True;User Instance=True";

Also there are many things wrong in your code. It works this way

您的代码中也有很多错误。它是这样工作的

{
  // Create Connection Object  
  // Provide connection object with Connection string
  // Create command object
  // Open connection
  // Execute command
  // Close connection
  // Dispose connection
}

回答by AleksAnderson IT

using (SqlConnection sqlcon = new SqlConnection("Connection String HERE"))
        {
            using (SqlCommand sqlcmd= new SqlCommand())
            {
                sqlcmd.Connection = sqlcon;            
                sqlcmd.CommandType = CommandType.Text;
                sqlcmd.CommandText = "SELECT SUM(ItemRate) FROM logs WHERE RoomNo=@rno";
                slqcmd.Parameters.AddWithValue("@rno", rno);
                try
                {
                    sqlcon.Open();
                    command.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    MessageBox.Show("Your Error Here");
                }
                finally
                {
                    connection.Close();
                }
            }

This will be helpful I think and its more safe

我认为这会有所帮助，而且更安全