SqlDataReader only returns first select in SqlCommand

Question

I'm using a SqlCommand to execute a sql statement on a SqlServer2005 db. ( I also tried DbCommand.)

If I execute this sql in SQL Server Management Studio, I can send the "Results to Text". I would like to capture this text in my C# code.

This and this are related questions, but not the same:

My sql contains, next to print statements, also different select statements:

PRINT 'We are here'
SELECT Name FROM Table1
-- do some logic
SELECT Name, Country FROM Table1
PRINT 'We are done'

( "Strange sql" I hear you say. I know, but this is what our long-living maintenance scripts look like.)

I can use the SqlConnection.InfoMessage to catch the PRINT statements separately.

But SqlDataReader doesn't seem to support the 2 select statements, both with a different FieldCount.

I'm afraid I'm stuck with Process.Start and the sql server command line sqlcmd and reading the output, but I was looking for something more API'ish.

Thanks in advance!

Answer 1

SqlDataReader should work. Use the Read method to go through the rows of the first select, and NextResult to move to the results of the second select. Like so:

do {
    while (dataReader.Read()) {
        //... process one row
    }
} while (dataReader.NextResult()) //go to the results of the next SELECT        

Answer 2

You have to call .NextResult() on your DataReader in order to get the second set of results.