How to consume multiple result sets?

[Carl San]

I am using ADO.Net in VB.net application. .Net Framework 1.1.



In a stored procedure I have multiple output parameters and multiple result
sets. How to code for this in ADO.Net?

I have used MyCommandObject.ExecuteNonQuery() when there were only output
parameters and no rows were returned from stored procedure.



I have used

Dim MyDataReader As SqlDataReader = MyCommandObject.ExecuteReader() when I
wanted to read one set of result set returned by stored procedure.



How to call a stored procedure when I have multiple output parameters and
multiple results sets?

Thanks,

Carl

 

[Pablo Castro [MS]]

The datareader object returned by ExecuteReader() has a "NextResult()"
method. Simply call NextResult() in a loop until you go through all the
results. In sort-of-VB:

reader = command.ExecuteReader()
do
do while reader.Read()
' process the row
loop
loop while reader.NextResult() ' this moves to the next result-set

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[John Smith]

Can I safely assume that the result sets are in the same order as I coded in
the stored procedure? If the number of rows in a results set is zero, do I
need to use the NextResult() to read the output data.
Thanks,
Carl

 

[Sambathraj]

Hi,
Chances are there when one of your query did not return any results :-(. You
need to think about this and do. If you are mostly dependent on the order,
ensure you dump the result into temporary table in the SP and then use a
select statement against it. Quick Fix it this!
Regards
Sambath