ADO 1.x reads data using SqlDataReader and requires a separate connection for each result set. Of course, you also have to manage these connections and pay for the high congestion bottlenecks in the appropriate memory and potential applications-especially in the Web applications in the data set.
A new feature of ADO 2. Majority result set (multiple Active result sets, referred to as Mars)-it allows multiple database queries or stored procedures to be executed on a single connection. The result is that you can get and manage multiple, forward-only, read-only result sets on a single connection. The database currently implementing this functionality is only SQL Server 2005. So when we target SQL Sever 2005, we need to revisit the use of DataReader objects. With SQL Server 2005, you can open multiple DataReader on a command object at the same time, saving the resources consumed by database joins, a typical scenario in which data is read and written from the database in real-world development, You can use multiple connections and now only one connection is sufficient. For example, if you have some data from several tables-they cannot be joined to a query, then you have multiple connections-each connection has a command associated with it to read the data. Similarly, if you are writing data to a table, you need another connection or connection set-if more than one table is to be updated.
For example, the following code
Multipleactiveresultsets=true opening a Join
String connstr = "server= (local);d atabase=northwind;integrated security=true; multipleactiveresultsets=true";
SqlConnection conn = new SqlConnection (CONNSTR);
Conn. Open ();
SqlCommand cmd1 = new SqlCommand ("SELECT * FROM Customers", Conn);
SqlCommand cmd2 = new SqlCommand ("SELECT * FROM Orders", Conn);
SqlDataReader RDR1 = cmd1. ExecuteReader ();
Next statement causes an error prior to SQL Server 2005
SqlDataReader RDR2 = cmd2. ExecuteReader ();
Now your can reader from RDR1 and RDR2 at the same time.
Conn. Close ();
Original link: http://www.cnblogs.com/RobotH/archive/2007/08/22/865942.html
"Go" SQL Server connection string configuration: Multipleactiveresultsets