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.

what are you using to execute the commands? for the example you could have SqlCommand for each statement and then use ExectueNonQuery() method that returns rows affected — TZHX
– TZHX, Commented Jan 17, 2018 at 11:14
Currently, we're using ExecuteNonQuery() from SMO, in order to handle GO separators. Splitting the script into separate SqlCommands is not an option, because that doesn't work if the script uses variables. — Mark Raymond
– Mark Raymond, Commented Jan 17, 2018 at 13:42

Mark Raymond · Accepted Answer · 2018-01-25 16:35:03Z

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).

edited Jan 25, 2018 at 16:35

answered Jan 17, 2018 at 14:49

Mark Raymond

1,02612 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Guzman Over a year ago

I found it too but you beat me to it :-) The secret sauce was FireInfoMessageEventOnUserErrors but note the onus is on you to identify errors rather than rely on try/catch.

Mark Raymond Over a year ago

Thanks, I would probably have missed that detail! I'll add it to the answer.

Emre Kabaoglu · Accepted Answer · 2018-01-17 11:18:05Z

0

ExecuteNonQuery should return the affected rows;

SqlCommand comm = new SqlCommand("DELETE FROM [dbo].[DatabaseLog] WHERE Event = N'CREATE_TABLE'",conn);
int rowsAffected = comm.ExecuteNonQuery();

answered Jan 17, 2018 at 11:18

Emre Kabaoglu

13.2k4 gold badges27 silver badges48 bronze badges

4 Comments

Mark Raymond Over a year ago

This only works if there is only a single statement. As I am not in control of the script, and indeed the script will usually contain multiple statements, this is not an option.

Emre Kabaoglu Over a year ago

What value is returned if you have multiple statements. Did you try it ?

Dan Guzman Over a year ago

@Stormcloak, I would expect the cumulative row count for the entire batch to be returned.

Mark Raymond Over a year ago

Yes, it returns the total row count - 187, in the example I gave in the question.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related