VB.NET - Multiple Result Sets in one query?

Question

I have a stored procedure:

CREATE PROCEDURE [TestProc]
AS
BEGIN
   select '1a', '1b'
   select '2a', '2b', '2c'
   select '3a', '3b'
END

If I execute the following query using SQL Management Studio,

exec TestProc

I get 3 result sets:

1. | 1a | 1b |

2. | 2a | 2b | 2c |

3. | 3a | 3b |

But when I use the stored procedure in ASP.NET(VB.NET),

Dim Connection As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnToHilmarc").ToString)
Dim Command As New SqlCommand("exec TestProc @First, @Second", Connection)
Dim Reader As SqlDataReader

Command.Parameters.AddWithValue("@First", "Hello")
Command.Parameters.AddWithValue("@Second", "World")

Connection.Open()
Reader = Command.ExecuteReader

While Reader.Read
    Response.Write(Reader(0) & " " & Reader(1) & "<br/>")
End While

Reader.Close()
Connection.Close()

I get only the first result set:

| 1a | 1b |

How can I get the three result sets using SqlDataReader? Or even three SqlDataReader's? Or is it possible to get multiple result sets in just one query in VB.NET? Is DataSet my only option? Thanks in advance.

Answer 1

You'll want to use the NextResult() of SqlDataReader to get the next result set:

Reader.NextResult();

SqlDataReader.NextResult() returns true if there's another result set, false otherwise, so you could (in your example code), use the following loop to avoid having 3 sets of code doing essentially the same thing:

Using Connection As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnToHilmarc").ToString())

    Dim Command As New SqlCommand("exec TestProc @First, @Second", Connection)
    Dim Reader As SqlDataReader

    Command.Parameters.AddWithValue("@First", "Hello")
    Command.Parameters.AddWithValue("@Second", "World")

    Connection.Open()
    Reader = Command.ExecuteReader()

    While Reader.Read() OrElse (Reader.NextResult() And Reader.Read())
        For i As Integer = 0 To Reader.FieldCount - 1
            Response.Write(Reader(i).ToString()
            Response.Write(" ")
        Next

        Response.Write("<br />")
    End While

    Reader.Close()

End Using

The OrElse will perform logical short-circuting:

1. If Reader.Read() returns true, it will read the next row without making a call to Reader.NextResult() (which would advance the reader to the next result and possibly cause loss of data). The And Reader.Read() is there to make sure you get the first row of the next result set.
2. If Reader.Read() is false, the next expression (Reader.NextResult()) will be evaluated - if it's true, it'll process the next result set.
3. If both are false, the loop is exited.