SqlDataReader returns only the first selection in SqlCommand

Question

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

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

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

My sql also contains various expressions next to print statements:

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

(“Strange sql,” I heard you say. I know, but that’s what our long-lasting maintenance scripts look like.)

I can use SqlConnection.InfoMessage to commit PRINT statements separately.

But SqlDataReader does not seem to support two select statements, as with the other FieldCount field.

I'm afraid that I am stuck in Process.Start and the sql server sqlcmd command line and read the output, but I was looking for something more than an API. '

Thanks in advance!

Answer 1

SqlDataReader should work. Use the Read method to view the rows of the first selection, and NextResult to jump to the results of the second selection. For instance:

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

Answer 2

You must call . NextResult () in its DataReader to get a second set of results.