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!

Matt Howells · Accepted Answer · 2009-07-16 10:26:27Z

10

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

edited Jul 16, 2009 at 10:26

answered Jul 16, 2009 at 9:48

Matt Howells

41.4k20 gold badges86 silver badges104 bronze badges

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

Comments

Dave Markle · Accepted Answer · 2009-07-16 09:45:13Z

6

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

answered Jul 16, 2009 at 9:45

Dave Markle

98.3k20 gold badges152 silver badges172 bronze badges

Collectives™ on Stack Overflow

SqlDataReader only returns first select in SqlCommand

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related