SqlDataReader only returns first select in SqlCommand
c#, sql-server-2005, text
Solution
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
Problem
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!