Can I throw an exception on the SQL 2005 server so that I can catch it in C# code with SqlCommand.ExecuteQuery()?
Add a comment
|
3 Answers
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.
Comments
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.
Comments
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)
....