Question

When I run the following snippet

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

I get the following message:

SQL_Error
A
B
C

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)

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

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

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

Answer 1

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

particularly this line

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

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.

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

Answer 2

ExecuteNonQuery normally returns a recordset while ExecuteScalar returns the first row + first column.

友情链接