为什么 SqlCommand.ExecuteNonQuery 抛出的 SqlException 包含所有 PRINT 作为错误? [英] Why does a SqlException thrown by SqlCommand.ExecuteNonQuery contain all the PRINTs as errors?

问题描述

当我运行以下代码段时

try
{
 using (SqlConnection conn = new SqlConnection("I'm shy"))
 {
  conn.Open();

  using (SqlCommand cmd = conn.CreateCommand())
  {
   cmd.CommandText = "PRINT 'A';PRINT 'B';PRINT 'C';RAISERROR('SQL_Error', 18, 1)";
   cmd.ExecuteNonQuery();
  }
 }
}
catch (SqlException ex)
{
 MessageBox.Show(ex.Message);
}

我收到以下消息:

SQL_Error
A
B
C

和 ex.Errors 有 4 个条目(对应于打印的 3 个 SqlError 的 SqlError.Class 为 0(vs. 18 为真正的错误)

and ex.Errors has 4 entries (The 3 SqlError's corresponding to the prints have a SqlError.Class of 0 (vs. 18 for the real error)

但是，如果我用 ExecuteScalar 替换 ExecuteNonQuery，我会得到预期的结果:

However, if I replace ExecuteNonQuery with ExecuteScalar, I get the expected result:

消息是 SQL_Error 而我在 ex.Errors...

The message is SQL_Error and I only have one entry in ex.Errors...

有什么办法可以避免cmd.ExecuteNonQuery的奇怪行为??

Is there any way to avoid the strange behavior of cmd.ExecuteNonQuery??

推荐答案

不，您无法避免这种行为.它是 TdsParser.ThrowExceptionAndWarning() 编写方式的结果

No you can't avoid this behavior. Its the result of the way TdsParser.ThrowExceptionAndWarning() is written

尤其是这一行

  bool breakConnection = this.AddSqlErrorToCollection(ref temp, ref this._errors) | this.AddSqlErrorToCollection(ref temp, ref this._attentionErrors);
        breakConnection |= this.AddSqlErrorToCollection(ref temp, ref this._warnings);
        breakConnection |= this.AddSqlErrorToCollection(ref temp, ref this._attentionWarnings);

我的猜测是，无论出于何种原因，ExecuteScaler 的 _error 或 _attentionErrors 集合之一是空的，而 ExecuteNonQuery 则不是.

My guess is that for whatever reason one of the collection _error or _attentionErrors is empty for ExecuteScaler and its not for ExecuteNonQuery.

我敢肯定，如果您四处探索，您可能会找出原因.

I'm sure if you poked around enough you could probably find out why.

无论如何，您似乎已经有了解决方法.只使用 SQLExecption.Error 中的第一项

In any case you seem to have the workaround already. Only use the first item in SQLExecption.Error

这篇关于为什么 SqlCommand.ExecuteNonQuery 抛出的 SqlException 包含所有 PRINT 作为错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！