C# – Using SqlDataReader to process multiple result sets

In this article I’ll show how to use the SqlDataReader ADO.NET class in two scenarios involving multiple result sets:

• Batches – When you execute multiple SELECTs in a single query. Each SELECT returns a different result set. You use a single reader to process the batch.
• Nested Queries – When you use multiple SqlDataReaders on the same connection at the same time, resulting in multiple active result sets.

I have a StreamingService database that has Movies, Shows, and Episodes tables (linked to the shows). First I’ll show the model classes I’m mapping the data into. Then I’ll show the two scenarios where I’m using SqlDataReader to process multiple result sets.

Note: This isn’t referring to the case where you’ve joined multiple tables and have a single result set.

First, here are the model classes

public class Movie
{
	public string Name { get; set; }
	public string Description { get; set; }
	public int RuntimeMinutes { get; set; }
	public int Year { get; set; }
}
public class Show
{
	public string Name { get; set; }
	public string Description { get; set; }
	public int NumberOfEpisodes { get; set; }
	public int NumberOfSeasons { get; set; }
	public int FirstYear { get; set; }
	public int? LastYear { get; set; }
	public List<Episode> Episodes { get; set; }

}
public class Episode
{
	public string Title { get; set; }
	public int Number { get; set; }
	public int Season { get; set; }
}
Code language: C# (cs)

Batches – One query that returns multiple result sets

To execute a batch query, you separate the individual SELECTs with semicolons, like this:

SELECT * FROM Shows; SELECT * FROM Movies;
Code language: C# (cs)

When you execute this and use a SqlDataReader to process the results, you have to call SqlDataReader.NextResult() to move to the next result set.

The following example is executing the Show/Movie queries in a single command. It then loops through the query results, creating a new object from each row by mapping the columns to properties, and adds each object to a list.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text.Json;

var conString = @"Server=<sql server instance>;Database=StreamingService;Integrated Security=true";

var shows = new List<Show>();
var movies = new List<Movie>();

using (var con = new SqlConnection(conString))
{
	con.Open();

	using var cmd = new SqlCommand(@"SELECT * FROM Shows; SELECT * FROM Movies;", con);
	using var reader = cmd.ExecuteReader();
		
	while (reader.Read())
	{
		shows.Add(new Show()
		{
			Name = reader.GetString("Name"),
			Description = reader.GetString("Description"),
			NumberOfEpisodes = reader.GetInt32("NumberOfEpisodes"),
			NumberOfSeasons = reader.GetInt32("NumberOfSeasons"),
			FirstYear = reader.GetInt32("FirstYear"),
			LastYear = reader.IsDBNull("LastYear") ? (int?)null : reader.GetInt32("LastYear"),
			Episodes = new List<Episode>()

		});
	}

	reader.NextResult();

	while (reader.Read())
	{
		movies.Add(new Movie()
		{
			Name = reader.GetString("Name"),
			Description = reader.GetString("Description"),
			RuntimeMinutes = reader.GetInt32("RuntimeMinutes"),
			Year = reader.GetInt32("Year")
		});
	}
}

var json = JsonSerializer.Serialize(new { Shows = shows, Movies = movies }, 
	new JsonSerializerOptions() { WriteIndented = true });
Console.WriteLine(json);
Code language: C# (cs)

Notice that it’s referring to columns by name – you need to add ‘using System.Data’ to get that behavior.

At the end, this example serializes the objects to JSON and outputs the following:

{
  "Shows": [
    {
      "Name": "Star Trek: Picard",
      "Description": "Picard goes on a space trip to stop android-hating Romulans",
      "NumberOfEpisodes": 10,
      "NumberOfSeasons": 1,
      "FirstYear": 2020,
      "LastYear": null,
      "Episodes": []
    },
    {
      "Name": "Breaking Bad",
      "Description": "Anti-hero story about a great chemist who uses his skills to become a drug kingpin",
      "NumberOfEpisodes": 62,
      "NumberOfSeasons": 5,
      "FirstYear": 2008,
      "LastYear": 2013,
      "Episodes": []
    },
    {
      "Name": "The Office",
      "Description": "A relatable comedy about what office workers do to survive the boredom of pointless work",
      "NumberOfEpisodes": 201,
      "NumberOfSeasons": 9,
      "FirstYear": 2005,
      "LastYear": 2013,
      "Episodes": []
    }
  ],
  "Movies": [
    {
      "Name": "Office Space",
      "Description": "A relatable comedy about a programmer who hates works",
      "RuntimeMinutes": 89,
      "Year": 1999
    },
    {
      "Name": "John Wick",
      "Description": "A revenge-seeking assassin goes after EVERYONE",
      "RuntimeMinutes": 101,
      "Year": 2014
    },
    {
      "Name": "Mad Max: Fury Road",
      "Description": "A car chase through the desert with guns, exploding spears, and the most metal guitarist ever",
      "RuntimeMinutes": 120,
      "Year": 2015
    }
  ]
}
Code language: JSON / JSON with Comments (json)

Nested Queries – Multiple active result sets

To work with multiple active result sets, you need to set MultipleActiveResultSets=true in the connection string. If you don’t do that, when you go to execute the second query, you’ll get the following exception:

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

The following example gets all the shows and then loops through them and gets their episodes:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text.Json;


var conString = 
	@"MultipleActiveResultSets=true;Server=<instanceName>;Database=StreamingService;Integrated Security=true;";

var shows = new List<Show>();

using (var con = new SqlConnection(conString))
{
	con.Open();

	using var showsCommand = new SqlCommand("SELECT * FROM Shows", con);
	using var showReader = showsCommand.ExecuteReader();
	
	while (showReader.Read())
	{

		var show = new Show()
		{
			Name = showReader.GetString("Name"),
			Description = showReader.GetString("Description"),
			NumberOfEpisodes = showReader.GetInt32("NumberOfEpisodes"),
			NumberOfSeasons = showReader.GetInt32("NumberOfSeasons"),
			FirstYear = showReader.GetInt32("FirstYear"),
			LastYear = showReader.IsDBNull("LastYear") ? (int?)null : showReader.GetInt32("LastYear"),
			Episodes = new List<Episode>()

		};

		shows.Add(show);

		//Nested query = using the same connection to fire a new query while processing the other query
		using (var episodesCommand = new SqlCommand($"SELECT * FROM Episodes WHERE Show=@Show", con))
		{
			episodesCommand.Parameters.AddWithValue("@Show", show.Name);
			
			using var episodeReader = episodesCommand.ExecuteReader();
			
			while (episodeReader.Read())
			{
				show.Episodes.Add(new Episode()
				{
					Number = episodeReader.GetInt32("Number"),
					Season = episodeReader.GetInt32("Season"),
					Title = episodeReader.GetString("Title")
				});
			}
		}
	}
}

Console.WriteLine(JsonSerializer.Serialize(shows, new JsonSerializerOptions() { WriteIndented = true }));
Code language: C# (cs)

Another disclaimer: You may be wondering, why don’t you join the Show/Episodes table here? Because that produces a single result set. This article is about dealing with multiple result sets, and I had to come up with an example.

This gets all of the shows and episodes and then outputs the following JSON:

[
  {
    "Name": "Star Trek: Picard",
    "Description": "Picard goes on a space trip to stop android-hating Romulans",
    "NumberOfEpisodes": 10,
    "NumberOfSeasons": 1,
    "FirstYear": 2020,
    "LastYear": null,
    "Episodes": [
      {
        "Title": "Remembrance",
        "Number": 1,
        "Season": 1
      },
      {
        "Title": "Maps and Legends",
        "Number": 2,
        "Season": 1
      },
      {
        "Title": "The End Is the Beginning",
        "Number": 3,
        "Season": 1
      },
      {
        "Title": "Absolute Candor",
        "Number": 4,
        "Season": 1
      },
      {
        "Title": "Stardust City Rag",
        "Number": 5,
        "Season": 1
      },
      {
        "Title": "The Impossible Box",
        "Number": 6,
        "Season": 1
      },
      {
        "Title": "Nepenthe",
        "Number": 7,
        "Season": 1
      },
      {
        "Title": "Broken Pieces",
        "Number": 8,
        "Season": 1
      },
      {
        "Title": "Et in Arcadia Ego Part 1",
        "Number": 9,
        "Season": 1
      },
      {
        "Title": "Et in Arcadia Ego Part 2",
        "Number": 10,
        "Season": 1
      }
    ]
  },
  {
    "Name": "Breaking Bad",
    "Description": "Anti-hero story about a great chemist who uses his skills to become a drug kingpin",
    "NumberOfEpisodes": 62,
    "NumberOfSeasons": 5,
    "FirstYear": 2008,
    "LastYear": 2013,
    "Episodes": []
  },
  {
    "Name": "The Office",
    "Description": "A relatable comedy about what office workers do to survive the boredom of pointless work",
    "NumberOfEpisodes": 201,
    "NumberOfSeasons": 9,
    "FirstYear": 2005,
    "LastYear": 2013,
    "Episodes": []
  }
]
Code language: JSON / JSON with Comments (json)

Note: I only put episodes for Picard in the database.