SqlCommand.ExecuteReaderAsync stops firing InfoMessage event after first SELECT statement in a stored procedure

Question

I have to use stored procedures for accessing data and also consume messages (like PRINT 'hello') sent from the DB engine.

When I a use InfoMessage event of SQL connection and fill the data into DataTable, everything works perfect.

However, when I have to read data sequentially and use SqlDataReader.ExecuteReaderAsync, connection stops firing InfoMessage after first select statement in the stored procedure:

C# code:

using (SqlConnection con = new SqlConnection("connection-string"))
{
    con.Open();
    con.InfoMessage += (s, e) => {
        Console.WriteLine(e.Message);
    };

    using (SqlCommand command = new SqlCommand("spTestMessage", con))
    {
        command.CommandType = System.Data.CommandType.StoredProcedure;

        SqlDataReader reader = await command.ExecuteReaderAsync();

        int cntr = 0;

        while (reader.Read())
        {
            Console.WriteLine($"Loaded row {cntr++}");
        }

        // reader.NextResult(); // this line forces firing rest of InfoMessage events
    }
}

SQL Stored Procedure:

CREATE PROCEDURE [dbo].[spTestMessage]
AS

PRINT 'Before select 1'
select * from MyTable
PRINT 'After select 1'

PRINT 'Before select 2'
select * from MyTable
PRINT 'After select 2'

Program output:

Before select 1

Why it stops working? I thing there is something wrong with Reader, because when I use command.ExecuteNonQueryAsync(); instead of command.ExecuteReaderAsync();, it also works.

I incidentally found out, that commented row reader.NextResult(); forces the connection to flush the messages and fire remaining events. However, its a very unlucky workaround.

Thanks for any help!

try `while(await reader.ReadAsync())` instead of `while (reader.Read())`. — vikscool, May 10 '19 at 08:58
Try OpenAsync() on the connection and ReadAsync() as well. Seems odd you are executing the reader async, but not reading async. — William Xifaras, May 10 '19 at 15:33

vikscool · Answer 1 · 2019-05-16T09:04:19.970

The reason why your command.ExecuteNonQueryAsync() stops giving your result after the first run, with or without reader.ReadAsync(), because any async SQL command will be completed as soon as the first result is returned to the client, and info messages does count as a result. And when you fire the reader.NextResult() or await reader.NextResultAsync() it checks with the reader for further results.

If you want to read more about Asynchronous SQL processing you can check Remus answer and for NextResult check out this example.

Consuming the InfoMessages:

var _msgs = new List<string>();
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(<YourConnectionString>))
{
    //Appending an event handler for InfoMessages
    con.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs args)
    {
        //reader will invoke the delegate on every itteration of results coming from query
        _msgs.Add(args.Message);
        return;
    };
    using (var cmd = new System.Data.SqlClient.SqlCommand("spTestMessage", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        using (System.Data.SqlClient.SqlDataReader reader = await cmd.ExecuteReaderAsync())
        {
            var incr = 0;
            while (await reader.ReadAsync())
            {
                //Statements to read data from Table1
                Console.WriteLine(reader.GetString(0));
                incr++;
            }
            while (await reader.NextResultAsync())
            {
                while (await reader.ReadAsync())
                {
                     //Statements to read data from Table2
                     Console.WriteLine(reader.GetString(0));
                     incr++;
                 }
             }
         }
     } 
}

Note: The above procedure can be used with Synchronous operations as well just change the signature of the methods from async to sync.

Ok, many thanks. I read all referenced articles, but I still don't understand what is the best practice to run async reader and consume all info messages. Does the `reader.NextResult();` consume resources and may cause performance problems? — Tom, May 13 '19 at 07:37
The `reader.NextResult()` is a *synchronous* approach whereas the `reader.NextResultAsync()` is an *asynchronous* I only recommended that as you were going with `command.ExecuteReaderAsync()` and it is better to have symmetry of `Async` functions instead of a mixed approach. As for Performance yes the `Async` function will be helpful for you. — vikscool, May 14 '19 at 05:27
Of course I'll use async version and won't mix approaches. The questions are: does the `NextResult/NextResultAsync` consume resources a lot and is there another way how to consume all InfoMessages? — Tom, May 15 '19 at 06:21
@Tom for your **1st question**: no it will not consume many resources but again it will depend on how you will architect your process but if you are worried that it might than you can change the process signature to `sync`. As for your **2nd question**: i have updated my answer on how I will consume the `info messages`(*print statements from SQL server*). — vikscool, May 16 '19 at 09:11

SqlCommand.ExecuteReaderAsync stops firing InfoMessage event after first SELECT statement in a stored procedure

1 Answers1