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.

Tim · Accepted Answer · 2011-08-10 04:54:29Z

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:

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.
If Reader.Read() is false, the next expression (Reader.NextResult()) will be evaluated - if it's true, it'll process the next result set.
If both are false, the loop is exited.

Jay Riggs · Accepted Answer · 2011-08-10 04:35:26Z

2

Take a look at the SqlDataReader.NextResult method. Call it on your SqlDataReader.

answered Aug 10, 2011 at 4:35

Jay Riggs

53.7k10 gold badges146 silver badges156 bronze badges

Collectives™ on Stack Overflow

VB.NET - Multiple Result Sets in one query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related