1

Is it possible with a .NET client program, communicating with back-end SQL Server using the System.Data.SqlClient library, for the server to send an informational message to the client program in contexts that do not involve any error and for the client program to obtain the message that was sent?

  create proc NewFoo            
    @value text,
    @userid text
    as
    begin
     insert foo
     (name, createdby) values (@value, @userid);

     declare @recordcount int
     select @recordcount = count(*) from foo where createdby= @userid;

     if @recordcount = 100
         begin
           -- let user know they've created their 100th record. Woo-hoo!!
         end
   end

Here is an additional pseudo-code example, closer to the actual use case; note the differing severity:

 if @value > @maxallowed
   begin
      if @userlevel = 'manager' 
           raiserror('Exceeded max. Intentional?',10,1)
      else
           raiserror('Exceeds max. Your edit was not saved',11,1)
   end

P.S. Without using an OUT parameter or RETURN value parameter in the client-side command object.

P.P.S. I am using the SqlDataAdapter.Update method and some of the code uses a DataReader on the SqlCommand object.

Tim
  • 8,669
  • 31
  • 105
  • 183
  • 1
    Depends on how you define "an error". The `RAISERROR` function can be used to return informational messages to the client - anything with a severity less than 11 isn't treated by SQL Server as an error in terms of `TRY`/`CATCH` or aborting anything. – Damien_The_Unbeliever Mar 03 '21 at 16:02
  • @Damien_The_Unbeliever But will an SQL Server error severity less than 11 percolate up to the client? How/where does the client get the message? – Tim Mar 03 '21 at 21:23
  • However the message is generated, if it is coming from the SQL Server without being directly requested, your app would have to be coded to be listening/checking for that message in one way or another. Is there a reason you don't want to check your results with an additional query on success (or something like that)? – David Thompson Jun 30 '21 at 16:55
  • @DavidThompson : In essence, it's a feature that lets managers override a business rule. A user with managerial privileges will receive only a warning (severity 10) that a value was invalid (e.g. amount paid > max payment allowed) but the insert/update will succeed; normal users will have the statement rejected (severity 11). The warning for managers is to make sure their violation of the business rule was not accidental. – Tim Jun 30 '21 at 17:48
  • @MarcGravell gave me a lead in a comment on this question: https://stackoverflow.com/questions/49359528/system-data-sqlclient-sqlexception-on-dataadapter-filldatabtable?noredirect=1#comment120530265_49359528 but in my brief bit of testing it seems using the InfoMessage event `Connection.FireInfoMessageEventOnUserErrors = true` disables the exception catching for severity >= 11. – Tim Jun 30 '21 at 17:50
  • Further on that, when `FireInfoMessageEventOnUserErrors = true`, a `Reader` on a `SqlCommand` object (`reader= cmd.ExecuteReader()`) does not work when an error is raised in the SP, even if the error severity < 11. – Tim Jun 30 '21 at 18:34

1 Answers1

1

You can use PRINT in your stored procedure, and to capture it in the code by subscribing to the InfoMessage event in the SqlConnection object.

In your sql just put:

    PRINT 'Exceeded max. Intentional'

and in your code grab it like this:

    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        string msg = null;
        connection.InfoMessage += (sender, e) => { msg = e.Message; };
        // execute the procedure
        // check msg
    }
Yair Maron
  • 1,860
  • 9
  • 22