5

I have a Stored Procedure which sometimes return an exception after SELECT statement. For example:

BEGIN TRY
 SELECT
    EmployeeId,
    EmployeeName
 FROM dbo.abc

 THROW 50000, 'exception occurs here', 16;
END TRY
BEGIN CATCH
 THROW 50000, 'exception', 16;
END CATCH

I am using ExecuteReaderAsync method to read data.

In case of exception, I should get exception in application. For example:

using (var reader = await ExecuteReaderAsync())
{
 while (reader.Read())
 {
 }
}

But I am not getting any exception.

This is a very strange behaviour. Because if this happens when we have some logic after SELECT and logic fails (e.g. any important UPDATE statement fails) then this will cause problem in the Application's behaviour.

Can somebody help me why I this strange behaviour is happening.

user1780538
  • 900
  • 3
  • 13
  • 22
  • Did you check this link - https://stackoverflow.com/questions/22126828/catch-sql-raise-error-in-c-sharp ? – Jatin Parmar Feb 15 '18 at 12:32
  • @JatinParmar that is severity 16, though - it should be fine? (note: it *isn't* 16 because of the last parameter; it is 16 because of `throw`: "There is no severity parameter. The exception severity is always set to 16.") – Marc Gravell Feb 15 '18 at 12:38
  • See [The Curious Case of Undetected SQL Exceptions](http://www.dbdelta.com/the-curious-case-of-undetected-sql-exceptions/). – Dan Guzman Feb 15 '18 at 12:38

2 Answers2

7

It is possible that this is simply because you disposed the reader before it had found the error - which is later in the stream than the first grid. Try doing:

using (var reader = await ExecuteReaderAsync())
{
    while (reader.Read()) { ... }

    // consume any trailing pieces
    while (reader.NextResult()) {}
}

The second loop (over NextResult()) ensures that the TDS parser has consumed the entire result. You might also prefer to use await reader.ReadAsync() and await reader.NextResultAsync(), since you're clearly in an async method here.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • as a side note: this is *just one more edge case* that tools like "Dapper" quietly fix for you; Dapper has all the `while (await reader.NextResultAsync(cancel).ConfigureAwait(false)) { /* ignore subsequent result sets */ }` and `while (reader.NextResult()) { /* ignore remaining result sets */ }` code that you would expect - so you just use `connection.Query(sql, args)` – Marc Gravell Feb 15 '18 at 12:41
  • This problem just began the other day for me. Ever find a working solution? Did everything here. No errors on SQL side. No errors on C# side, but application exits immediately after running ExecuteReaderAsync . Been driving me crazy - weirder still, there's about 40 other identical async queries in the code that work just fine! This stopped OnLY on console app. Go figure – MC9000 Oct 11 '22 at 12:01
0

I encountered this in an additional scenario using the following code:

using (DbDataReader reader = await command.ExecuteReaderAsync(CommandBehaviour.SingleResult).ConfigureAwait(false))
{
    // ...

    while (await reader.NextResultAsync().ConfigureAwait(false)) { }
}

In this scenario, CommandBehaviour.SingleResult will prevent the exception being raised when a result set is returned first, even when using the NextResult loop. It will stop reading from the connection once it receives a result which means it doesn't see the error.

To fix, just remove the CommandBehaviour parameter from ExecuteReaderAsync() and ensure you include the while loop.

G-DE
  • 21
  • 1
  • 1
    While this answer may provide some useful information when passing in `CommandBehavior.SingleResult`, it does not answer the question asked. – Vijay Varadan Dec 23 '22 at 22:37
  • 1
    @VijayVaradan I absolutely agree. I initially looked at leaving a comment on the answer but don't have enough reputation. It was either leave an answer or not contribute unfortunately. – G-DE Dec 25 '22 at 01:10