Multiple Result Sets from Stored Proc...

[bartee]

I am using asp.net 2.0 vb.

I have a dataset set up in the designer.

The table adapter is set to call a stored procedure. The stored procedure returns two result sets (two tables).

I have a datatable that is filled by the table adapter described above. Again, this is all done in the designer thus far.

The dataset is filled by the FIRST result set returned by the stored procedure.

Can I fill another datatable with the second result set?

Either in design time or run time.

Any examples are greatly appeciated. Thanks in advance.

 

[PGoRule]

Do you want second datatable to merge with the first dataset?
Look at DataSet.Merge method...you can merge another dataset, datatable, array of datarows with existing dataset.

Sharing the best from my side...

--Prashant--

 

[bartee]

thanks for the reply.

i don't think i want to merge.

basically, my stored procedure queries a large set of data into a single temp table. i then perform two different select statements on the temp table, thus producing two result sets as the product of the stored procedure.

my table adapter points to this procedure and i'm trying to populate one datatable with the first result set and another datatable with the second result set.

i'm currently doing this in the dataset designer but have only been able to populate a datatable with the first result set, but not the second.

any suggestions, etc?

 

[whatsthehampton]

Hi,

Your table adapter could call the one stored procedure which creates the temp table and then; passing one variable return the data you need.

Get temp table

if @Variable = 1
Begin
Select from temp table for datatable1
end
else
Begin
Select from temp table for datatable2
End

??

hth
j

http://www.bst.ac.jp

 

[bartee]

That may work.

I was trying to just "hit" the database once for better performance.

Any suggestions from that perspective?

Thank you very much for your response.

 

[BoulderBum]

Are you opposed to having a single DataSet hold the data from both resultsets? You'll still be able to access both DataTables seperately.

http://www.ondotnet.com/pub/a/dotnet/2002/12/16/multiresultsets_1202.html?page=2