CODEWITHSUNDEEP
.netsqlsql-serversql-server-2005exception
Timmy
Timmy

Reputation: 12828

Error checking in .NET with SQL 2005

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

Upvotes: 2

Views: 129

Answers (3)

AMissico
AMissico

Reputation: 21684

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

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294307

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.

Upvotes: 4

3Dave
3Dave

Reputation: 29051

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.

Upvotes: 3

Related Questions