How to check for errors in C# using ExecuteStoreQuery executing a stored procedure?

Question

I have a stored procedure that I am calling using the ObjectContext ExecuteStoreQuery method so that I can get the result set that the stored procedure returns.

I have it inside of a try/catch block, however even though the stored procedure returns the following error it doesn't fall into the catch block.

How do I trap for this type of error?

Or is there another way of executing a stored procedure and getting a result set back?

Error when executed in SQL:

Msg 50000, Level 16, State 2, Procedure ComputeCharges, Line 6440
The following error occurred while computing charges:
Error Number: 515, Line Number: 5867
Error Message: Cannot insert the value NULL into column 'TransactionAmount', table 'Transactions'; column does not allow nulls. INSERT fails.

Calling code:

using (DbContext dbContext = GetDbContext())
{
   using (ObjectContext objContext = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)dbContext).ObjectContext)
   {
       try
       {
           IEnumerable<ResultSet> results = objContext.ExecuteStoreQuery<ResultSet>(sqlString).ToList();
        }
       catch (Exception e)
       {
          EventLogger.LogException(e, title: "An error occurred while computing charges.", silent: false);
       }
   }
}

See http://stackoverflow.com/questions/15820505/dbentityvalidationexception-how-can-i-easily-tell-what-caused-the-error — abatishchev, Apr 23 '15 at 16:51
Is the stored procedure doing any error handling itself, or is it a simple insert? If the sql fails, it should throw an exception which would enter your `catch` block. Also, can you attach the debugger or add a throw to the `catch` block and ensure that the issue isn't that your `EventLogger` is not logging properly? — Andy_Vulhop, Apr 23 '15 at 16:55
The stored procedure is using a try catch block and at the end of the catch raising an error and returning a status of 99. RAISERROR('%s Error Number: %d, Line Number: %d Error Message: %s', @ ERROR_SEVERITY, @ ERROR_STATE, @ UserMessage, @ ERROR_NUMBER, @ ERROR_LINE, @ ERROR_MESSAGE) RETURN(99) The results of the RAISERROR from SQL is listed in the above post. — user281677, Apr 24 '15 at 16:25
It almost appears to be a SQL Server issue. If I raise an artificial error myself before the try catch block, there is an exception that is caught correctly in C#. However, if I raise the thrown error or an artificial error either one inside the catch block or after the catch block then the C# code continues on as if nothing happened in the query. — user281677, Apr 24 '15 at 17:39
Microsoft's documentation clearly states that this should work: -- Use RAISERROR inside the CATCH block to return error -- information about the original error that caused -- execution to jump to the CATCH block. RAISERROR (@ ErrorMessage, -- Message text. @ ErrorSeverity, -- Severity. @ ErrorState -- State. ); https://msdn.microsoft.com/en-us/library/ms178592(v=sql.110).aspx — user281677, Apr 24 '15 at 17:42
The premise of this question is false. This catch block does catch all exceptions. No question about it. The exception must be happening outside of the catch or the catch will be catching it. How did you determine that it does not catch the exception? — usr, Apr 24 '15 at 21:36
No, its not false a false premise. The problem is in the way exceptions are checked as noted below. The problem is not that it doesn't catch the exception, the issue is that it did not throw an exception to be caught. As long as the result set comes back and it is able to get the result set, it will never thrown any further SQL errors that happen afterward. The fix below did work and correct the problem. — user281677, Apr 27 '15 at 14:16

score 0 · Answer 1 · answered Apr 24 '15 at 18:26

0

An exception will be thrown in C# correctly in this case as long as the result set has yet to be selected/returned. The stored procedure was selecting the result set in the middle of the process and not at the end of the try block. Once the select statement for the result set was moved to the end of the try block it threw the exception in C#.

answered Apr 24 '15 at 18:26

user281677

249
1
2
9

I have no idea why was this downvoted - the question along with the comments above helped me to find the reason I don't get an exception in the C# code. Thank you so much! – Danylo Yelizarov Apr 18 '20 at 02:23

How to check for errors in C# using ExecuteStoreQuery executing a stored procedure?

1 Answers1