Multiple Active Result Sets (MARS)

One of the fastest methods to retrieve data from the database is the DbDataReader object, but one of the problems with DbDataReader object is that it keeps an open server-side cursor while you are looping through the results of your query. If you try to execute another command while the first command is still executing, you will receive an InvalidOperationException, stating, "There is already an open DataReader associated with this Connection which must be closed first." You can avoid this exception by setting the MultipleActiveResultSets connection string option to true when connecting to Multiple Active Result Sets (MARS)–enabled hosts such as SQL Server 2005 and later.

 

The following example shows MARS enabled connection string named ConStrMARS

 

Application Configuration File

<connectionStrings>
    <clear />
    <add name="ConStr" providerName="System.Data.SqlClient" connectionString="Data Source=localhost;Integrated security=SSPI;Initial Catalog=MyDatabase;" />
    <!--By default, MARS is disabled when connecting to a MARS-enabled host.

      It must be enabled in the connection string. -->
    <add name="ConStrMARS" providerName="System.Data.SqlClient" connectionString="Data Source=localhost;Integrated security=SSPI;Initial Catalog=MyDatabase;MultipleActiveResultSets=True" />
</connectionStrings>

The following example shows how to use two SqlDataReader objects with two SqlCommand objects and a single SqlConnection object with MARS enabled. It opens a single connection to the MyDatabase Database. Using a SqlCommand object, a SqlDataReader is created. As the reader is used, a second SqlDataReader is opened, using data from the first SqlDataReader as input to the WHERE clause for the second reader.

 

Example:

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

namespace MultipleActiveResultSets {
    class Program {
        static void Main(string[] args) {
            int CustomerID;
            SqlDataReader OrderReader = null;
            string connectionString = "Data Source=(local);Integrated Security=SSPI;" +
                "Initial Catalog=MyDatabase;MultipleActiveResultSets=True";

            string CustomerSQL = "SELECT CustomerID, CustomerName FROM Customers";

            string OrderSQL = "SELECT * FROM Orders WHERE CustomerID = @CustomerID";
            using(SqlConnection connection = new SqlConnection(connectionString)) {
                SqlCommand CustCmd = new SqlCommand(CustomerSQL, connection);
                SqlCommand OrderCmd = new SqlCommand(OrderSQL, connection);
                OrderCmd.Parameters.Add("@CustomerID", SqlDbType.Int);

                connection.Open();
                using(SqlDataReader CustomerReader = CustCmd.ExecuteReader()) {
                    while (CustomerReader.Read()) {
                        Console.WriteLine(CustomerReader["CustomerName"]);

                        CustomerID = (int) CustomerReader["CustomerID"];
                        OrderCmd.Parameters["@CustomerID"].Value = CustomerID;

                        OrderReader = OrderCmd.ExecuteReader();
                        using(OrderReader) {
                            while (OrderReader.Read()) {
                                //Console.WriteLine(
                            }
                        }
                    }
                }
                Console.WriteLine("Press any key to continue");
                Console.ReadLine();
            }
        }
    }
}

On a database server without MARS, you could first collect the list of customers into a collection and close the connection. After that, you can loop through the collection to get each customer ID and execute a query to get the list of Orders made by that customer. Another solution is simply to create two connections: one for the customer list and another one for orders.