c# - SqlConnection InfoMessage triggering only at end of execution

Question

I am using

sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(sqlConnection_InfoMessage);

and ExecuteNonQuery() to execute a stored procedure.

I am using RAISERROR("Custom Message", 10 , 1) WITH NOWAIT in stored procedure to throw message.

I am getting all message thrown by sql server at end of query execution which i want at the time of process.

I could get all the message at real time by setting sqlConnection.FireInfoMessageEventOnUserErrors = true; but this cause the error with severity greater than 10 also be handled by SqlInfoMessageEventHandler which i want to be thrown as exception .

Is there any way to get my custom sql infomessage with severity 10 at real time in eventHandler but other as exception?

Answer 1

It seems like the issue is with ExecuteNonQuery().

In my case I used ExecuteScalar() and removed the line

sqlConnection.FireInfoMessageEventOnUserErrors = true;

Which cause the RAISERROR("Custom Message", 10 , 1) WITH NOWAIT to be caught by eventHandler and other as exceptions at real time.