Using CommandBehavior.SingleResult in ADO.NET

In ADO.NET in C#, the CommandBehavior.SingleResult flag is used to optimize data retrieval when executing a SQL command that returns multiple result sets from a database. This flag indicates that you are interested in only the first result set and want to discard any subsequent result sets. It can help minimize resource usage and improve performance by avoiding the processing or caching of unnecessary data.

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 = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";

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

                // Create a SqlCommand with CommandBehavior.SingleResult
                string query = "SELECT * FROM Customers; SELECT * FROM Orders";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    // Use CommandBehavior.SingleResult to retrieve only the first result set
                    using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult))
                    {
                        // Check if there is a result set
                        if (reader.HasRows)
                        {
                            Console.WriteLine("First Result Set (Customers):");
                            while (reader.Read())
                            {
                                Console.WriteLine($"{reader["CustomerID"]} - {reader["ContactName"]}");
                            }
                        }
                        else
                        {
                            Console.WriteLine("No data in the first result set.");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

Expected Output

In this example, we use the CommandBehavior.SingleResult flag to fetch only the first result set from a SQL query that returns data from the "Customers" and "Orders" tables.

By specifying CommandBehavior.SingleResult as a parameter when calling the ExecuteReader() method, we instruct ADO.NET to create a DataReader that only considers the first result set from the executed command. Any subsequent result sets are ignored.

Inside the using block, we process the first result set using the Read() method of the DataReader.

Here's the expected output:

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

As you can see, we only retrieved and displayed the data from the "Customers" table, even though the query returned data from both "Customers" and "Orders." This optimization can be especially useful when dealing with large or complex queries, as it reduces unnecessary data processing and memory consumption by excluding subsequent result sets.