ADO - Working with Multiple Result Sets in ADO.NET

Working with multiple result sets in ADO.NET is a technique that allows a single SQL query or stored procedure to return more than one set of records. Instead of sending separate database requests for different pieces of information, multiple queries can be combined into one command, reducing communication between the application and the database server. This approach improves performance, minimizes network traffic, and simplifies data retrieval for applications that need related data from different tables.

For example, an e-commerce application may need to display customer details, recent orders, and payment history on the same page. Instead of executing three separate database queries, a single SQL command can retrieve all three result sets, making the application more efficient.

Why Multiple Result Sets Are Used

Applications often require data from multiple tables at the same time. Executing individual queries for each table increases the number of database connections and network requests. Multiple result sets solve this problem by allowing one command to retrieve all the required data.

Some common situations include:

  • Displaying employee information along with department details.

  • Retrieving customer profiles, orders, and invoices together.

  • Generating reports that include summary and detailed information.

  • Loading dashboard data from different database tables.

  • Fetching master-detail records in a single database call.

How Multiple Result Sets Work

A SQL command can contain multiple SELECT statements separated by semicolons.

Example:

SELECT * FROM Customers;
SELECT * FROM Orders;
SELECT * FROM Products;

When this command is executed, SQL Server returns three separate result sets.

ADO.NET uses the SqlDataReader object to process these result sets one after another. The NextResult() method moves the reader from the current result set to the next available result set.

Components Used

Several ADO.NET objects work together when handling multiple result sets.

SqlConnection

Establishes a connection with the SQL Server database.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand

Contains the SQL query or stored procedure that returns multiple result sets.

SqlCommand cmd = new SqlCommand(sqlQuery, con);

SqlDataReader

Reads each result set one row at a time.

SqlDataReader reader = cmd.ExecuteReader();

NextResult()

Moves to the next result set after the current one has been completely read.

reader.NextResult();

Example Database

Assume the database contains three tables.

Customers

CustomerID CustomerName
1 Rahul
2 Priya

Orders

OrderID CustomerID Amount
101 1 500
102 2 750

Products

ProductID ProductName
1 Laptop
2 Printer

SQL Query Returning Multiple Result Sets

SELECT * FROM Customers;
SELECT * FROM Orders;
SELECT * FROM Products;

The database sends three separate result sets in the order they appear.

Reading Multiple Result Sets in ADO.NET

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string";

        SqlConnection con = new SqlConnection(connectionString);

        string query = @"SELECT * FROM Customers;
                         SELECT * FROM Orders;
                         SELECT * FROM Products";

        SqlCommand cmd = new SqlCommand(query, con);

        con.Open();

        SqlDataReader reader = cmd.ExecuteReader();

        Console.WriteLine("Customers");

        while (reader.Read())
        {
            Console.WriteLine(reader["CustomerID"] + " " +
                              reader["CustomerName"]);
        }

        if (reader.NextResult())
        {
            Console.WriteLine("\nOrders");

            while (reader.Read())
            {
                Console.WriteLine(reader["OrderID"] + " " +
                                  reader["Amount"]);
            }
        }

        if (reader.NextResult())
        {
            Console.WriteLine("\nProducts");

            while (reader.Read())
            {
                Console.WriteLine(reader["ProductID"] + " " +
                                  reader["ProductName"]);
            }
        }

        reader.Close();
        con.Close();
    }
}

Explanation of the Program

Step 1

A connection to SQL Server is created.

SqlConnection con = new SqlConnection(connectionString);

Step 2

The SQL command contains three SELECT statements.

SELECT * FROM Customers;
SELECT * FROM Orders;
SELECT * FROM Products;

Step 3

The command is executed using ExecuteReader().

SqlDataReader reader = cmd.ExecuteReader();

The first result set (Customers) becomes available.

Step 4

The first result set is processed.

while(reader.Read())
{
    // Read customer records
}

Step 5

NextResult() moves to the second result set.

reader.NextResult();

The Orders table becomes the active result set.

Step 6

The Orders records are processed.

while(reader.Read())
{
    // Read order records
}

Step 7

NextResult() moves to the third result set.

reader.NextResult();

The Products table becomes active.

Step 8

The Products records are processed until all rows are read.

Using Stored Procedures with Multiple Result Sets

Stored procedures can also return multiple result sets.

Example:

CREATE PROCEDURE GetCompanyData
AS
BEGIN
    SELECT * FROM Employees;

    SELECT * FROM Departments;

    SELECT * FROM Projects;
END

ADO.NET code:

SqlCommand cmd = new SqlCommand("GetCompanyData", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlDataReader reader = cmd.ExecuteReader();

The NextResult() method is then used exactly as before.

Advantages of Multiple Result Sets

Improved Performance

Only one database request is required, reducing communication overhead.

Reduced Network Traffic

Sending one request instead of multiple requests minimizes data transfer between the application and the database.

Faster Application Response

The application receives all required information in a single operation, improving responsiveness.

Better Resource Utilization

Fewer database connections and commands reduce server workload.

Simpler Data Retrieval

Related information can be fetched together, making the application logic easier to manage.

Efficient Dashboard Loading

Dashboards often display information from multiple tables. Multiple result sets allow all dashboard data to be loaded at once.

Limitations

Sequential Processing

Result sets must be processed in order. You cannot directly skip to the third result set without moving through the previous ones using NextResult().

Forward-Only Reading

SqlDataReader reads data in a forward-only manner. Once a row has been passed, it cannot be revisited without executing the query again.

Connection Remains Open

The database connection stays open until the SqlDataReader is closed. Applications should close the reader as soon as processing is complete.

Increased Complexity

When many result sets are returned, the application code becomes more complex because each result set must be handled separately.

Best Practices

  • Return only the result sets that the application actually needs.

  • Always process each result set completely before calling NextResult().

  • Close the SqlDataReader immediately after use.

  • Use stored procedures for frequently executed multiple-result-set operations.

  • Handle exceptions using try-catch-finally or using statements to ensure connections are properly closed.

  • Validate that another result set exists before calling NextResult().

  • Retrieve only the necessary columns instead of using SELECT * whenever possible to improve performance.

Real-World Applications

  • E-commerce websites displaying customer information, orders, and shopping cart details.

  • Banking systems retrieving account information, transactions, and loan details in a single request.

  • Hospital management systems loading patient details, appointments, and medical history together.

  • School management systems retrieving student information, attendance, and examination results simultaneously.

  • Human resource management systems displaying employee records, payroll information, and department details in one database call.

  • Business intelligence dashboards that combine sales, inventory, customer statistics, and financial summaries from different tables.

Summary

Working with multiple result sets in ADO.NET enables developers to retrieve several groups of related data through a single database command. By using SqlDataReader along with the NextResult() method, applications can process each result set sequentially without executing multiple queries. This technique improves performance, reduces network traffic, and enhances the efficiency of data-driven applications, making it particularly useful for dashboards, reporting systems, enterprise applications, and other scenarios where multiple datasets are required simultaneously.