If you perform multiple select queries inside your stored procedures I believe you actually return all of the result sets to the client. (except when you assign a value to a parameter like SELECT @myVar = [ColumnA] FROM Table1). This is something to be careful of since you might unnecessarily return results sets that you actually don't need to return.
But this behavior also has an advantage. Obviously, if you need to retrieve two result sets, there is no need to create two or more SqlCommand to retrieve them. I believe this is handled under the hood if you use SqlDataAdapter.Fill where the dataset being filled will result the sets as part of its tables collection. However in this example we will illustrate how it can be done using SqlDataReader using non other than Reader.NextResult() method.
This is something I don't see often (might not be the case for you) so without much further ado, here's a sample code that might explain better than words. [more]
Once again this uses AdventureWorks database.
1 using System;
2 using System.Data.SqlClient;
3
4 namespace Console2
5 {
6 internal class Program
7 {
8 private static void Main(string[] args)
9 {
10 string connectionString =
11 @"Data Source=.sql2005;Initial Catalog=AdventureWorks;Integrated Security=SSPI;";
12
13 using (SqlConnection conn = new SqlConnection(connectionString))
14 {
15 conn.Open();
16
17 // our command text containing two select queries
18 string commandText =
19 "SELECT ProductCategoryID, Name FROM [Production].[ProductCategory]; " +
20 "SELECT ProductSubcategoryID, Name FROM [Production].[ProductSubcategory]";
21
22 SqlCommand cmd = new SqlCommand(commandText, conn);
23 using (SqlDataReader rdr = cmd.ExecuteReader())
24 {
25 // look into categories result set
26 Console.WriteLine("Categories");
27 while (rdr.Read())
28 {
29 int categoryID = rdr.GetInt32(0);
30 string categoryName = rdr.GetString(1);
31 // NOTE: be careful when using indexes (as in above)
32 // make sure they match the order of columns in your select query
33
34 Console.WriteLine(categoryID + "t" + categoryName);
35 }
36
37 // look into categories result set
38 rdr.NextResult();
39
40 // for separation in display only
41 Console.WriteLine(String.Empty.PadLeft(20, '-'));
42
43 Console.WriteLine("SubCategories");
44 while (rdr.Read())
45 {
46 int subCategoryID = rdr.GetInt32(0);
47 string subCategoryName = rdr.GetString(1);
48 Console.WriteLine(subCategoryID + "t" + subCategoryName);
49 }
50 }
51 }
52
53 Console.ReadKey();
54 }
55 }
56 }
And running the code should result to something like in the image below.
So there you have it, a simple example.
The same result can be expected using stored procedures. Say you have the following stored procedure:
CREATE PROC MultiQuery
AS
SELECT
ProductCategoryID,
Name
FROM Production.ProductCategory
SELECT
ProductSubcategoryID,
Name
FROM Production.ProductSubcategory
And this will be your modified code
1 using System;
2 using System.Data;
3 using System.Data.SqlClient;
4
5 namespace Console2
6 {
7 internal class Program
8 {
9 private static void Main(string[] args)
10 {
11 string connectionString =
12 @"Data Source=.sql2005;Initial Catalog=AdventureWorks;Integrated Security=SSPI;";
13
14 using (SqlConnection conn = new SqlConnection(connectionString))
15 {
16 conn.Open();
17
18 // stored procedure name
19 string commandText = "MultiQuery";
20
21 SqlCommand cmd = new SqlCommand(commandText, conn);
22 cmd.CommandType = CommandType.StoredProcedure;
23 using (SqlDataReader rdr = cmd.ExecuteReader())
24 {
25 // look into categories result set
26 Console.WriteLine("Categories");
27 while (rdr.Read())
28 {
29 int categoryID = rdr.GetInt32(0);
30 string categoryName = rdr.GetString(1);
31 // NOTE: be careful when using indexes (as in above)
32 // make sure they match the order of columns in your select query
33
34 Console.WriteLine(categoryID + "t" + categoryName);
35 }
36
37 // look into categories result set
38 rdr.NextResult();
39
40 // for separation in display only
41 Console.WriteLine(String.Empty.PadLeft(20, '-'));
42
43 Console.WriteLine("SubCategories");
44 while (rdr.Read())
45 {
46 int subCategoryID = rdr.GetInt32(0);
47 string subCategoryName = rdr.GetString(1);
48 Console.WriteLine(subCategoryID + "t" + subCategoryName);
49 }
50 }
51 }
52
53 Console.ReadKey();
54 }
55 }
56 }
I just realized that selecting the code with the line numbers is a pain so including sample project (Console App) in the link below (commandType.Text sample only)