Timing of RAISERROR returns in Entity Framework ExecuteSqlCommand?

Question

I'm inheriting a long-running stored procedure to be brought into a new application using Entity Framework. The stored procedure displays informative messages as it runs, through the use of

RAISERROR (@MSG, 0, 1) WITH NOWAIT

to put something out as a message.

Generally to call stored procedures I use

context.Database.ExecuteSqlCommand("exec dbo.LongStoredProcedure", parameters);

and I figured I could intercept the messages by setting up an event listener, as:

var sqlConn = context.Database.Connection as SqlConnection;
if (sqlConn != null) sqlConn.InfoMessage += sqlConn_InfoMessage;

but what that gets me, in this case, is a single hit on sqlConn_InfoMessage once the stored procedure has finished running, with all the messages concatenated with newlines in the SqlInfoMessageEventArgs message.

What I want is to capture the messages as they're being emitted in the stored procedure. What should I be doing in this case? Working directly through sqlConn for the entire thing?

score 0 · Accepted Answer · answered Mar 06 '18 at 22:39

In the end this answer pointed me close to where I wanted to go:

sqlConn.InfoMessage += sqlConn_InfoMessage);
var command = sqlConn.CreateCommand();
command.Transaction = context.Database.CurrentTransaction.UnderlyingTransaction 
    as SqlTransaction;
command.CommandTimeout = 60 * 20;
command.CommandText = "dbo.LongStoredProcedure";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddRange(parameters.ToArray());
command.ExecuteScalar();

got me live updates to sqlConn_InfoMessage.

Timing of RAISERROR returns in Entity Framework ExecuteSqlCommand?

1 Answers1