Dapper return print message from stored procedure

1
votes

I have a stored procedure which I can't modify and I'm trying to get the PRINT message that get's returned if an error occurs. The code in my stored procedure is.

IF((ISNULL(@ID,''))='')
BEGIN
    PRINT N'Error: This ID is invalid.'; 
    RETURN;
END

In my dapper call, I have the following:

var storedProcResult = connection.Query("AdventureWorks.dbo.myStoredProcedure", new
{
    @ID = null
},
 commandType: CommandType.StoredProcedure
);

The value of storedProcResult is 0 but I'm interested getting the PRINT message. Please advise.

2
c#sqlsql-servertsqldapper
I don't think Dapper has anything specific for this. You can hook up a handler to SqlConnection.InfoMessage manually -- however, for error code specifically, what you really want is THROW / RAISERROR so you get an actual SqlException. PRINT messages are very easy to ignore by clients, and in fact the vast majority of them do ignore them. Did whoever wrote this procedure live in a land where only Management Studio was used for all work, and nobody ever ignored the "Messages" tab? Seems like something that's not viable in the long run. - Jeroen Mostert
I'm about 99% (or so) sure that you are out of luck. Your sproc returns nothing. The PRINT statement simply writes to the console - Flydog57
If I'm able to modify the stored procedure, what is the best approach to get error messages. Using error codes? - usr4896260
@Flydog57 there is no console; it writes to the TDS stream; you can receive that via InfoMessage (SSMS etc just do the exact same thing internally, and present it as though there is a console) - Marc Gravell
to confirm: @JeroenMostert is correct - Dapper makes absolutely no provision for this currently, for multiple reasons, but not least: the API for this (InfoMessage) is at the connection level, not the command, and Dapper is mostly interested in commands as the unit of work. Also: it isn't defined on DbConnection, but is instead SqlConnection specific (Dapper tries to be connection-agnostic as far as possible). But: just subscribe to InfoMessage in your code, and you should be set! - Marc Gravell

2 Answers

0
votes

You can use the structure bellow and just replace the value of @ErrorMessage variable.

CREATE OR ALTER PROCEDURE DBO.SPR_PROCEDURE_NAME()AS
BEGIN

    DECLARE 
     @ErrorMessage NVARCHAR(4000)
    ,@ErrorSeverity INT
    ,@ErrorState INT

    BEGIN TRY



        --YOUR CODE HERE



    END TRY

    BEGIN CATCH     
        IF (XACT_STATE()) = -1
            ROLLBACK TRAN

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),  
               @ErrorState = ERROR_STATE();  
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

    END CATCH

END
0
votes

Based on the comments of Jeroen and Marc I have written a very basic code to get the messages of a single command:

var infoMessageBuilder = new StringBuilder();

void OnInfoMessageEvent(object _, SqlInfoMessageEventArgs args)
{
    infoMessageBuilder.AppendLine(args.Message);
}

// Register message event listener
cn.InfoMessage += OnInfoMessageEvent;
await cn.ExecuteAsync("AdventureWorks.dbo.myStoredProcedure", new {@ID = null}, commandType: CommandType.StoredProcedure);

// Unregister listener to stop fetching events from the connection
cn.InfoMessage -= OnInfoMessageEvent;

// Get messages as string
var message = infoMessageBuilder.ToString();

I registered an event handler right before command execution and will write all output of the InfoMessage event to a StringBuilder. The handler will be removed after execution. This works perfectly fine for me. Hope it helps someone else who will get here.