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

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

user281677 user281677 · Accepted Answer · 2015-04-24T18:26:27

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

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

1 Answers