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

Question

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

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

- Akshay

---

你正在另一个方法中设置连接字符串，而在按钮事件中使用空的con对象。 - Kundan Singh Chouhan

Answer 1

1. 你存在 SQL注入 的风险。不要通过拼接字符串来构建查询，而应该使用 SQL 参数。
2. 对于连接（以及任何实现 IDisposable 接口的对象），请使用 using语句块。使用 using 会在出错时自动关闭连接。
3. 异常出现的原因是你没有初始化 SqlCommand 的连接，因为你没有指定连接。你可以使用适当的构造函数或者属性来设置连接。

以下为示例：

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

- Tim Schmelter

Answer 2

这个错误描述得很准确，你还没有设置你的SQLCommand的Connection属性。

尝试添加：

cmd.Connection = con;

在调用ExecuteScalar()之前。

- Bridge

Answer 3

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

cmd.Connection = con;

在执行查询之前。

- Rohit Vats

Answer 4

您展示的代码存在多个问题 - 特别是一些严重的安全问题，我强烈建议您阅读SQL注入和准备好的语句/参数以及使用。

以下是一些快速纠正/评论:

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

- Yahia

Answer 5

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

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

此外，您的代码中有许多问题。它是这样工作的

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

- Zo Has

Answer 6

仅仅打开连接是不够的；
你需要将 con 与 cmd 关联起来。

- Yehuda Shapira

Answer 7

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

我认为这会很有帮助，而且更加安全。

- AleksAnderson IT