can i combine several select queries in .net so they are returned in 1 batch?

Question

i want to combine several select sqls into 1 connections to the database (SQL Server). the queries will contain different tables data so it must not done using sql. basicly i want to do that follwoing

select * from Table1
Go
Select * from Table2
Go
Select * from Table3

then send this query to the database and the i want to result to be returned in one hit. so i can then read the data using 3 datareaders. is this possible using c# and sqlserver?

Answer 1

You can use Multiple Active Result Sets: http://www.codeguru.com/csharp/csharp/cs%5Fnetwork/database/article.php/c8715

Answer 2

Maybe SqlDataReader.NextResult?

Answer 3

GO is not a SQL statement. So you must send to the server the 3 queries as a single batch:

SqlCommand cmd = new SqlCommand(
@"select * from Table1;
select * from Table2;
select * from Table3;", connection);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
   while (rdr.Read())
   {
     //read results from Table1
   };
   rdr.NextResult();
   while(rdr.Read())
   {
    //read results from Table2
   }
   rdr.NextResult();
   while(rdr.Read())
   {
     //read results from Table3
   }
 }