ultiple Active Result Sets (MARS) is a new feature of ADO.NET 2.0 that allows multiple queries or stored procedures to be performed on a single connection. The result of this is that you can get and manage multiple, forward-only, read-only result sets on a single connection. Prior to MARS, each result set required a separate connection. Of course, you'd have to manage those connections and resign yourself to the obvious costs in memory and the potential for bottlenecks in high-traffic applications—particularly in data-intensive Web applications.

The first commercial database to support MARS is SQL Server 2005, and in this article you'll get a primer on how to use this powerful, yet easy addition to the ADO.NET arsenal.

When accessing resultsets on previous version of SQL Server, you would need to create additional SqlConnection objects to use with your SqlCommand. Keeping track of all your resultsets and their SqlConnections made for less efficient programs and the 'There is already an open DataReader associated with this Connection' error. Now, using MARS, these are things of the past.

Getting Started
To get started you need a new setting in your connection string to enable the multiple active connections. Unsurprisingly, this setting is called 'MultipleActiveResultSets' and is used like this:

String connectionString = "Data Source=DBSERVER;" +
  "Initial Catalog=AdventureWorlds;IntegratedSecurity=SSPI;" +
  "MultipleActiveResultSets=True";

It's default setting is 'False,' and you can explicitly disable it if you so desire by passing 'False' with the MultipleActiveResultSets setting.

Follow these steps to get up and running with MARS and ADO.NET 2.0:

• Create a connection string like the one above.
• Create a SqlConnection object and initialize it with the connection string.
• Open the SqlConnection object, using its Open() method.
• For each query that you want to perform, create a new SqlCommand object. Associate them with the SqlConnection object that you created and opened in the above steps.
• Execute the queries using the appropriate commands (for example using ExecuteReader() if you want to read the results of the query, or ExecuteNonQuery() for updates and the like).
• When you're done, close the SqlConnection object.

In Practice
A typical scenario where you may use multiple connections, and can now suffice with only one, is where you need to read and write data to the database. So, for example, if you have data that comes from a number of tables, which cannot be joined into a single query, you would have multiple connections, each with an associated command to read the data. Likewise, if you were writing data to a table (usually derived somehow from what you read) you would need another connection or set of connections if there were a few tables to be updated.

Consider the situation where you need to read data from two queries, A and B, derive values from this data that would be written to table C, and keep an audit log of your actions that would be written to table D. In such a case, you might have code that looks like this:

• Open a connection for A
• Execute Query A, and populate a dataset
• Close connection for A
• Open a connection for B
• Execute Query B, and populate a dataset
• Close connection for B
• Open Connection for C
• Open Connection for D
• Update C with derived details from A and B
• Update D with audit stamp of what you've done
• Close D
• Close C

This is pretty complicated!

When using MARS, you would do the following:

• Open the connection with MultipleActiveResultSets=true
• Execute A and populate a dataset
• Execute B and populate a dataset
• Update C with derived details from A and B
• Update D with audit stamp of what you've done
• Close the connection

Much simpler!