使用SQL命令作为C＃中的参数值

I have a SQL command I am running that works great however for one of the AddWithValue parameters I want to use another SQL command to get that value... this is what I have but the cmd2 I want to use isn't working. Is it even possible to get data that way in theory it makes sense but it doesn't seem to work..

我有一个我正在运行的SQL命令，但是对于其中一个AddWithValue参数我想使用另一个SQL命令来获取该值...这就是我所拥有的但我想要使用的cmd2不起作用。是否有可能以理论上的方式获取数据它是有道理的，但它似乎不起作用..

    cmd2 = new SqlCommand("SELECT acctNum FROM custInfo WHERE customerName = @customerName", cn);
    cmd2.Parameters.AddWithValue("@customerName", customerDropDown.Text);

    cmd = new SqlCommand("UPDATE custInfo SET ctGal = (ctGal - (@contractGallons)) WHERE acctNum = @acctNum", cn);

    cmd.Parameters.AddWithValue("@contractGallons", gallonsTextBox.Text)        
    cmd.Parameters.AddWithValue("@acctNum", cmd2);

3 个解决方案

#1

You must use cmd2.ExecuteReader() to get the acctNum for example

例如，您必须使用cmd2.ExecuteReader（）来获取acctNum

You can try following code

您可以尝试以下代码

using (SqlDataReader reader = cmd2.ExecuteReader())
{
  if (reader.Read())
  {
    cmd = new SqlCommand(@"UPDATE custInfo SET ctGal = (ctGal - 
     (@contractGallons)) WHERE acctNum = @acctNum", cn);

    cmd.Parameters.AddWithValue("@contractGallons", gallonsTextBox.Text)        
    cmd.Parameters.AddWithValue("@acctNum", reader["acctNum"]);

  }
}

Hope this will help..

希望这会有所帮助..

#2

I suggested combining both queries into one:

我建议将两个查询合并为一个：

//DONE: let keep query readable
string sql = 
  @"UPDATE custInfo 
       SET ctGal = (ctGal - (@contractGallons)) 
     WHERE acctNum IN (SELECT c.acctNum
                         FROM custInfo c 
                        WHERE c.customerName = @customerName)";

//DONE: wrap IDisposable into using
using (var cmd = new SqlCommand(sql, cn)) {
  //TODO: get rid of AddWithValue, but specify the actual fields' types
  cmd.Parameters.AddWithValue("@contractGallons", gallonsTextBox.Text);
  cmd.Parameters.AddWithValue("@customerName", customerDropDown.Text);

  cmd.ExecuteNonQuery();
}

#3

You have two choices if you want to go this route:

如果你想走这条路，你有两个选择：

Combine the two queries when you instantiate the second SqlCommand. This will require adding a second parameter to the second command.
在实例化第二个SqlCommand时合并这两个查询。这将需要向第二个命令添加第二个参数。
Or run the first command. Fetch the resulting acctNum and add it as a value for the second command.
或者运行第一个命令。获取生成的acctNum并将其添加为第二个命令的值。

Probably better would be to rewrite the two queries into a single joined query.

可能更好的方法是将两个查询重写为一个连接的查询。

#1