How do you capture the output of a SQL Script run from .NET, including rows affected?

Question

Consider this simple SQL script:

PRINT N'Dropping CREATE_TABLE events from DatabaseLog table...'
DELETE FROM [dbo].[DatabaseLog] WHERE Event = N'CREATE_TABLE'
PRINT N'Dropping ALTER_TABLE events from DatabaseLog table...'
DELETE FROM [dbo].[DatabaseLog] WHERE Event = N'ALTER_TABLE'
PRINT N'Done!'

When run from SSMS, against AdventureWorks 2012, it gives this output:

Dropping CREATE_TABLE events from DatabaseLog table...

(70 row(s) affected)
Dropping ALTER_TABLE events from DatabaseLog table...

(117 row(s) affected)
Done!

How do I reproduce this in .NET, including the rows affected lines?

By hooking into the InfoMessage event on the SqlConnection, I get the output of the PRINT statements, but this doesn't include the rows affected lines. Ideally, I want to capture the output exactly as if it was run in SSMS.

Note: The script is user-supplied, so modifying the script to output the row counts manually is not an option.

Answer 1

Finally found the right event to get the individual statement counts from! StatementCompleted on the individual SqlCommand fires multiple times, once for each statement that affects rows. So:

public static void ExecuteScript(string connectionString, string sql)
{
    using (var conn = new SqlConnection(connectionString))
    {
        conn.InfoMessage += (sender, args) => Console.WriteLine(args.Message);
        conn.FireInfoMessageEventOnUserErrors = true;
        conn.Open();
        using (var cmd = new SqlCommand(sql, conn))
        {
            cmd.StatementCompleted += (sender, args) =>
                Console.WriteLine($"{Environment.NewLine}({args.RecordCount} row(s) affected)");
            cmd.ExecuteNonQuery();
        }
    }
}

reproduces the output from SSMS, for the queries I've tries so far, which have included CREATE TABLE, PRINT, INSERT, DELETE, and SELECT.

Setting FireInfoMessageEventOnUserErrors to true is necessary, otherwise all the InfoMessage events appear at the end, rather than being correctly interleaved with StatementCompleted events. However, when FireInfoMessageEventOnUserErrors is set to true, SqlException will not be thrown if there are errors, so you must check each InfoMessage for errors (if the Class is set to > 10).