Techie BabajiLearn to Code. Build Real Skills.
ADO.NET · Advanced

Multiple Result Sets

One command can return several SELECT results. Reading them together saves round trips to the database.

Intermediate 5 min Lesson 20 of 32Examples use Microsoft.Data.SqlClient on .NET 9 with SQL Server 2022.
Before you start:SqlDataReader
0/32 in ADO.NET
On this page · 5 min · Intermediate

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Code example
  5. Step by step
  6. Real-world use
  7. Common mistakes
  8. Best practices
  9. Remember this
  10. Practice exercise
  11. Mini quiz
  12. Related topics
  13. My notes
Reading time
5 min
Difficulty
Intermediate
Updated
Oct 29, 2026

Related tutorials

Interview questions

In simple words#

One phone call, three answers: “product details, related products and order count”. NextResult moves to the next answer.

Result set
The rows from one SELECT.
NextResult()
Move to the next result set.
Benefit
Fewer round trips to the database.

Learning objectives#

  • Return several result sets from one batch or procedure
  • Move between them with NextResult
  • Build a page model from one call
  • Understand MARS

Explanation#

A batch or stored procedure with several SELECT statements returns several result sets.

Read all rows of the first, then call NextResult (or NextResultAsync) to move to the next. It returns false when there are no more.

MultipleActiveResultSets=True (MARS) allows several open readers on one connection. It is rarely needed — reading sequentially is simpler and faster.

Code example: A product page in one round trip#

C#ProductPage.cs Illustrative
const string sql = """
    SELECT Id, Name, Price FROM Products WHERE Id = @id;
    SELECT TOP (3) Id, Name FROM Products
      WHERE CategoryId = (SELECT CategoryId FROM Products WHERE Id = @id) AND Id <> @id;
    SELECT COUNT(*) FROM Orders WHERE ProductId = @id;
    """;
await using var cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;
await using var r = await cmd.ExecuteReaderAsync();


await r.ReadAsync();
Console.WriteLine(
quot;{r.GetString(1)} - ₹{r.GetDecimal(2)}");


await r.NextResultAsync();
while (await r.ReadAsync()) Console.WriteLine(
quot;  related: {r.GetString(1)}");


await r.NextResultAsync();
await r.ReadAsync();
Console.WriteLine(
quot;  sold {r.GetInt32(0)} times");
Expected output
Laptop - ₹55000.00
  related: Mouse
  related: Keyboard
  sold 12 times

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. Three SELECTs are sent in one batch.
  2. The first result set has the product.
  3. NextResultAsync moves to related products.
  4. The final result set has the order count.

Real-world use case#

A dashboard API loads totals, recent orders and low-stock alerts in one database round trip.

Common mistakes#

Calling NextResult before finishing the current set

Remaining rows are skipped. That is fine if intended, but be deliberate.

Enabling MARS to fix “There is already an open DataReader”

Usually the real fix is to finish or dispose the first reader.

Best practices#

  • Group related queries into one batch for fewer round trips
  • Read result sets in a fixed order
  • Document the result-set order of procedures
  • Avoid MARS unless truly necessary

Remember this#

Several SELECTs → several result sets.

NextResult moves to the next one.

MARS = rarely needed.

Practice exercise#

Your task: Write a loop that prints the number of rows in each result set, whatever the count of sets.

Show solution
C# Illustrative
int set = 1;
do
{
    int rows = 0;
    while (await r.ReadAsync()) rows++;
    Console.WriteLine(
quot;Set {set++}: {rows} rows");
} while (await r.NextResultAsync());

Mini quiz#

Multiple Result Sets quizMultiple choice Question 1 of 1

Which method moves a reader to the next result set?

Score so far: 0/0

Want more? Practise all ADO.NET MCQs · ADO.NET interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.