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.
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.