How to Read Multiple Result Sets Using ADO.NET

Introduction

When querying a database, especially in complex applications, it’s common to return multiple result sets in a single SQL query or stored procedure. Instead of making multiple database calls, you can boost performance and reduce overhead by fetching all the data at once.

In this guide, we’ll learn how to read multiple result sets using ADO.NET with SqlDataReader.

Scenario

Suppose you want to display:

• A list of products
• Each product’s average review rating
• Related tags

Instead of calling three separate queries, we’ll combine the result into one and parse them sequentially using SqlDataReader.

Code Example

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("your-multi-        select-stored-procedure-or-query", conn))
{
    conn.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        // 1st result set: Products
        List<Product> products = new List<Product>();
        while (reader.Read())
        {
            products.Add(new Product {
                Id = reader.GetInt32(0),
                Title = reader.GetString(1),
                // Add other columns as needed
            });
        }
        // 2nd result set: Reviews
        if (reader.NextResult())
        {
            List<Review> reviews = new List<Review>();
            while (reader.Read())
            {
                reviews.Add(new Review {
                    ProductId = reader.GetInt32(0),
                    AvgStar = reader.GetDecimal(1)
                });
            }
        }
        // 3rd result set: Tags
        if (reader.NextResult())
        {
            List<Tag> tags = new List<Tag>();
            while (reader.Read())
            {
                tags.Add(new Tag {
                    Id = reader.GetInt32(0),
                    Title = reader.GetString(1)
                });
            }
        }
        // Add more reader.NextResult() as needed
    }
}

Handling Multiple SELECT Queries in SQL with ADO.NET

Example SQL Query

You can write a query like this in your stored procedure or script:

-- Multiple SELECTs
```sql
SELECT Id, Title FROM Products;
SELECT ProductId, AVG(Stars) AS AvgStar FROM Reviews GROUP BY ProductId;
SELECT TagId, Title FROM Tags;

Notes

• Always check reader.NextResult() before reading the next result set.
• This technique is especially useful for dashboard-style pages that need multiple sets of data.
• Use models (Product, Review, Tag) to store each dataset logically.

Benefits

• Fewer database calls = better performance
• Cleaner code with centralized query logic
• Faster UI loading for pages needing multiple datasets

Conclusion

Using SqlDataReader.NextResult() allows you to handle multiple datasets efficiently within a single database connection. It’s a powerful tool when working with dashboards, reports, or other data-heavy applications.

Want to reduce server round-trips? Master SqlDataReader with multi-select handling!