我正在尝试通过我的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';
The query expanded is...
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';
有什么想法可以解决这个问题的调试吗?
'@BOOK'
周围加上''
? - Niladri