c# - SqlCommand.ExecuteNonQuery() 总是返回 0

Question

我正在尝试通过我的 C# 代码处理 UPDATE 语句。我使用以下...

using (SqlCommand cmd = new SqlCommand(query, sqlConn))
{
    cmd.Parameters.AddWithValue("@CUSTOMER", intCustomer);
    cmd.Parameters.AddWithValue("@CONDITION", strCondition);
    cmd.Parameters.AddWithValue("@BOOK", strBook);
    cmd.Parameters.AddWithValue("@PAGE", strPage);
    cmd.Parameters.AddWithValue("@ENDPAGE", strEndPage);

    System.Diagnostics.Debug.WriteLine("Expanded query: " + 
        query.ExpandSqlQuery(cmd.Parameters));

    int affectedRows = cmd.ExecuteNonQuery();
    System.Diagnostics.Debug.WriteLine("Number of rows affected: " + affectedRows);
}

我曾尝试使用和不使用参数来执行此操作，只是为了调试，并且出于某种原因总是得到 0 的返回值。

我做了一个小扩展方法来扩展参数以显示实际查询(没有变量)...

public static string ExpandSqlQuery(this String input, SqlParameterCollection sqlParams)
{
    string results = input;

    foreach (SqlParameter p in sqlParams)
        results = results.Replace(p.ParameterName, p.Value.ToString());

    return results;
}

...我在执行查询之前调用它以查看将要运行的内容。

System.Diagnostics.Debug.WriteLine("Expanded query: " +
    query.ExpandSqlQuery(cmd.Parameters));

然后我采用在 C# 中返回 0 个受影响的行的完全相同的查询，并在 Microsoft SQL Server Management Studio 中手动运行它，让它告诉我 1 行受到了影响!

带参数的查询是...

UPDATE
  BookList
SET
  Overdue=2
WHERE
  Customer=@CUSTOMER
  and Condition='@CONDITION'
  and Book='@BOOK'
  and Page='@PAGE'
  and EndPage='@ENDPAGE'
  and Overdue=1;

UPDATE
  BookInfo
SET
  Finished=0
WHERE
  Customer=@CUSTOMER
  and Condition='@CONDITION'
  and Book='@BOOK';

展开的查询是...

UPDATE
  BookList
SET
  Overdue=2
WHERE
  Customer=85
  and Condition='old'
  and Book='00103'
  and Page='00304'
  and EndPage='00304'
  and Overdue=1;

UPDATE
  BookInfo
SET
  Finished=0
WHERE
  Customer=85
  and Condition='old'
  and Book='00103';

关于如何调试这个问题有什么想法吗？

Answer 1

您不得在参数两边加上引号。因此:

UPDATE
  BookList
SET
  Overdue=2
WHERE
  Customer=@CUSTOMER
  and Condition='@CONDITION'
  and Book='@BOOK'
  and Page='@PAGE'
  and EndPage='@ENDPAGE'
  and Overdue=1;

UPDATE
  BookInfo
SET
  Finished=0
WHERE
  Customer=@CUSTOMER
  and Condition='@CONDITION'
  and Book='@BOOK';

应该改为:

UPDATE
  BookList
SET
  Overdue=2
WHERE
  Customer=@CUSTOMER
  and Condition=@CONDITION
  and Book=@BOOK
  and Page=@PAGE
  and EndPage=@ENDPAGE
  and Overdue=1;

UPDATE
  BookInfo
SET
  Finished=0
WHERE
  Customer=@CUSTOMER
  and Condition=@CONDITION
  and Book=@BOOK;