Can SQL Server return an informational message to .NET System.Data.SqlClient program in contexts that do not involve any error

Question

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.

Answer 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
    }