How to get the original client-side SQL error message using Dapper?

Question

I would like to tell my example using an example.

Say we have a storage procedure, and the procedure contains this pair of lines of SQL code

BEGIN TRY  
    -- Generate a divide-by-zero error.  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    throw
END CATCH;  
GO  

and, of course, if we execute this sp, it will divide the exception by zero:

Division by zero error

Is it possible to take SQL message with Dapper? here is the dapper request call

 var data = sqlConnection.QueryFirstOrDefault<response>(QueryConstants.SpXXX,
 parameters, null, Constants.DAPPER_TIMEOUT, CommandType.StoredProcedure);

I am using sql server 2017 and Dapper 1.50.4.0

Github Problem # 959

Answer 1

I suspect that an exception in your stored procedure occurs after the first result grid. This problem is a subtle difference between Query<T>and QueryFirstOrDefault<T>. You can workaround:

var item = Query<T>(...).FirstOrDefault();

. , - , CommandBehavior.SingleResult ( dapper QueryFirstOrDefault), .

github, .

: - SingleResult ; ADO.NET, foo - , select 42; , :

using (var cmd = conn.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "foo";

    // if SingleResult is omitted, the error is observed
    // using (var reader = cmd.ExecuteReader())

    // if SingleResult is specified, the error is not observed
    using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
    {
        do
        {
            while (reader.Read()) { } // ignore the rows in the grid
        } while (reader.NextResult());
    }
}

, , SingleResult, ( , , )