ADO.NET Review: Multiple Select Queries in one SqlCommand

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)

Download Sample – MultiQueryCommand.zip (2.68 kb) 

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I will never give away, trade or sell your email address. You can unsubscribe at any time.