How to Use SqlDataReader to process multiple result sets in C#

1. Batches: When you execute multiple SELECT statements in a single query, each returning a different result set. You use a single reader to process the batch.
2. Nested Queries: When you execute multiple SqlDataReaders on the same connection simultaneously, resulting in multiple active result sets.

I have a Library database with Books, Authors, and Genres tables. I’ll first show the model classes into which I’m mapping the data. Then, I’ll demonstrate the two scenarios where I use SqlDataReader to process multiple result sets.

Note: This is not about joining multiple tables to produce a single result set.

public class Book
{
    public string Title { get; set; }
    public string Description { get; set; }
    public int Year { get; set; }
    public string Genre { get; set; }
}

public class Author
{
    public string Name { get; set; }
    public string Biography { get; set; }
    public List<Book> Books { get; set; }
}

public class Genre
{
    public string Name { get; set; }
    public string Description { get; set; }
}

To execute a batch query, separate each SELECT statement with a semicolon, as shown below:

SELECT * FROM Authors; SELECT * FROM Books; SELECT * FROM Genres;

When executing this query and using a SqlDataReader, you'll need to call NextResult() to move to the next result set.

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

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

var authors = new List<Author>();
var books = new List<Book>();
var genres = new List<Genre>();

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

    using var cmd = new SqlCommand(@"SELECT * FROM Authors; SELECT * FROM Books; SELECT * FROM Genres;", con);
    using var reader = cmd.ExecuteReader();
    
    // Process Authors
    while (reader.Read())
    {
        authors.Add(new Author()
        {
            Name = reader.GetString("Name"),
            Biography = reader.GetString("Biography"),
            Books = new List<Book>()
        });
    }

    reader.NextResult();
    
    // Process Books
    while (reader.Read())
    {
        books.Add(new Book()
        {
            Title = reader.GetString("Title"),
            Description = reader.GetString("Description"),
            Year = reader.GetInt32("Year"),
            Genre = reader.GetString("Genre")
        });
    }

    reader.NextResult();
    
    // Process Genres
    while (reader.Read())
    {
        genres.Add(new Genre()
        {
            Name = reader.GetString("Name"),
            Description = reader.GetString("Description")
        });
    }
}

var json = JsonSerializer.Serialize(new { Authors = authors, Books = books, Genres = genres }, 
    new JsonSerializerOptions() { WriteIndented = true });

Console.WriteLine(json);

Note: In the code above, we process the Authors, Books, and Genres result sets in sequence. At the end of the process, the objects are serialized to JSON, and the output will be a structure that looks like:

{
  "Authors": [
    {
      "Name": "J.K. Rowling",
      "Biography": "Author of the Harry Potter series",
      "Books": []
    },
    {
      "Name": "George Orwell",
      "Biography": "Author of 1984 and Animal Farm",
      "Books": []
    }
  ],
  "Books": [
    {
      "Title": "Harry Potter and the Sorcerer's Stone",
      "Description": "The first book in the Harry Potter series.",
      "Year": 1997,
      "Genre": "Fantasy"
    },
    {
      "Title": "1984",
      "Description": "A dystopian novel set in a totalitarian society.",
      "Year": 1949,
      "Genre": "Dystopian"
    }
  ],
  "Genres": [
    {
      "Name": "Fantasy",
      "Description": "Books containing magical elements and imaginary worlds."
    },
    {
      "Name": "Dystopian",
      "Description": "Books set in a bleak future society, often under authoritarian rule."
    }
  ]
}

To work with multiple active result sets, ensure that your connection string has MultipleActiveResultSets=true. Without this setting, you'll encounter the following exception:

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

The following example retrieves all authors and then retrieves their books using nested queries.

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

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

var authors = new List<Author>();

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

    using var authorsCommand = new SqlCommand("SELECT * FROM Authors", con);
    using var authorReader = authorsCommand.ExecuteReader();
    
    while (authorReader.Read())
    {
        var author = new Author()
        {
            Name = authorReader.GetString("Name"),
            Biography = authorReader.GetString("Biography"),
            Books = new List<Book>()
        };

        authors.Add(author);

        // Nested query – Get books for this author
        using (var booksCommand = new SqlCommand($"SELECT * FROM Books WHERE Author=@Author", con))
        {
            booksCommand.Parameters.AddWithValue("@Author", author.Name);
            
            using var bookReader = booksCommand.ExecuteReader();
            
            while (bookReader.Read())
            {
                author.Books.Add(new Book()
                {
                    Title = bookReader.GetString("Title"),
                    Description = bookReader.GetString("Description"),
                    Year = bookReader.GetInt32("Year"),
                    Genre = bookReader.GetString("Genre")
                });
            }
        }
    }
}

Console.WriteLine(JsonSerializer.Serialize(authors, new JsonSerializerOptions() { WriteIndented = true }));

This will output a list of authors with their associated books:

[
  {
    "Name": "J.K. Rowling",
    "Biography": "Author of the Harry Potter series",
    "Books": [
      {
        "Title": "Harry Potter and the Sorcerer's Stone",
        "Description": "The first book in the Harry Potter series.",
        "Year": 1997,
        "Genre": "Fantasy"
      }
    ]
  },
  {
    "Name": "George Orwell",
    "Biography": "Author of 1984 and Animal Farm",
    "Books": [
      {
        "Title": "1984",
        "Description": "A dystopian novel set in a totalitarian society.",
        "Year": 1949,
        "Genre": "Dystopian"
      }
    ]
  }
]

This approach avoids joining the Authors and Books tables because joining produces a single result set.