Error checking in .NET with SQL 2005

Question

Can I throw an exception on the SQL 2005 server so that I can catch it in C# code with SqlCommand.ExecuteQuery()?

Answer 1

Errors that occur on the SQL server bubble up to your .NET code as exceptions. To test, execute a query that will return an error (like a syntax error or something stupid) and watch for the exception in the debugger.

Answer 2

Use RAISERROR(). Errors raised with severity 1..10 will be informational messages that will trigger the SqlConnection.InfoMessage event. Errors raised with severity 16 will be translated into SqlError objects attached to a SqlException, which will be thrown by SqlClient when you execute any of the SqlCommand methods (ExecuteReader, ExecuteNonQuery, ExecuteScalar, ExecuteXmlReader and their async completion counterparts).

Do not use other severity for RAISERROR other than 1..10 and 16.

Answer 3

Yes, I do this all the time. For example,

SELECT @SurveyCode = (SELECT Value FROM Setting WHERE [Key] = @PubCode)

IF @SurveyCode IS NULL
BEGIN
    RAISERROR ('No survey code for the specified PubCode (%s ) in Setting table.', 16, 1, @PubCode)
    RETURN
END

---

System.Data.SqlClient.SqlException was unhandled
  Class=16
  ErrorCode=-2146232060
  LineNumber=15
  Message="No survey code for the specified PubCode ((null) ) in Setting table."
  Number=50000
  Procedure="GetSetting"
  Server="(local)"
  Source=".Net SqlClient Data Provider"
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       ....