data reader ?

is it possible to use a data reader to read from 2 tables with 1 store procedure(sp)?

---------------

ex:

create sp1

as

select * from tbl1

select * from tbl2

---------------

how can i use a data reader to read the items from tbl2?

If you have SqlServer 2005 (inc Express), you can use MARS -MultipleActiveResultSets. It's only available with SqlClient, so won't work with Oracle, or MySQL etc.|||

One of the restrictions of ADO.NET and datreaders is that although SQL can return multiple datasets, ADO.NET can only read the first.

|||

Use a DataSet in this instance to retrieve your results. Then, you can retrieve the items out of the two tables that will be returned.

|||

It looks like you can support mulitple resultsets with a DataReader as well although I've always used a DataSet in this instance. See the following article.

http://msdn.microsoft.com/msdnmag/issues/04/06/DataPoints/#S2

|||

TATWORTH:

One of the restrictions of ADO.NET and datreaders is that although SQL can return multiple datasets, ADO.NET can only read the first.

As I understand it, that's only the case when using the SqlDataSource control, although I can't remember whether it can only hold the first resultset, or the last....

sql