Retrieving Multiple Result Sets with DataReader in C#

In C#, you can use the DataReader to retrieve multiple result sets from the same database connection by utilizing the NextResult() method. This allows you to execute a SQL query that returns multiple result sets and access each of them sequentially. Below is an explanation along with a complete source code example and the expected output:

Explanation

1. Open Connection: First, establish a connection to your database using the SqlConnection class. This connection serves as a link to your database.
2. Execute SQL Query: Create a SqlCommand and execute a SQL query that returns multiple result sets. Ensure that your query contains multiple SELECT statements, each representing a separate result set.
3. DataReader Initialization: Use the ExecuteReader() method of the SqlCommand to create a DataReader. The DataReader allows you to iterate through the rows of the first result set.
4. Iterate Through First Result Set: Use a while loop to read and process the rows of the first result set using the DataReader.
5. Access Subsequent Result Sets: To access subsequent result sets, call the NextResult() method of the DataReader. This method advances the DataReader to the next result set if one exists. You can use this method in a loop to access all the result sets.

Sample Code

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Define the connection string to your database
        string connectionString = "Data Source=SQlServerName;Initial Catalog=DbName;user id = youruserid; password = yourpassword";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                // Open the connection
                connection.Open();

                // Create a SqlCommand with a query that returns multiple result sets
                string query = "SELECT * FROM Customers; SELECT * FROM Orders";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    // Create a DataReader to read the first result set
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        // Read and process the first result set
                        Console.WriteLine("First Result Set (Customers):");
                        while (reader.Read())
                        {
                            Console.WriteLine($"{reader["CustomerID"]} - {reader["ContactName"]}");
                        }

                        // Move to the next result set
                        reader.NextResult();

                        // Read and process the second result set
                        Console.WriteLine("\nSecond Result Set (Orders):");
                        while (reader.Read())
                        {
                            Console.WriteLine($"{reader["OrderID"]} - {reader["OrderDate"]}");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

Expected Output

In this example, we execute a SQL query that returns data from both the "Customers" and "Orders" tables. Here's the expected output:

First Result Set (Customers):
ALFKI - Maria Anders
ANATR - Ana Trujillo
...

Second Result Set (Orders):
10248 - 1996-07-04 00:00:00
10249 - 1996-07-05 00:00:00
...

As you can see, we retrieve and display data from two different result sets within the same connection using the DataReader. The NextResult() method allows us to move from one result set to another sequentially.