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!

Coding a Sample with MARS and C#
This sample uses the AdventureWorks sample database that comes with SQL Server 2005, and uses the Developer edition of the database. Note that it will also change the contents of some of the fields in the database, so if you use the sample DB for other purposes, be forewarned.

This example will demonstrate how to read a single SalesOrder from the database, and to use that to reduce the inventory of the item that is sold. Typically this would require two sequential connections to the database, one to read the amount of items sold and one to update the inventory with the reduced amount.

The first code snippet shows how you would do it without MARS:

ArrayList ids = new ArrayList();ArrayList qtys = new ArrayList();string connectionString = "Data Source=MEDIACENTER;" +  "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +  "MultipleActiveResultSets=False";string strSQLGetOrder = "Select * from Sales.SalesOrderDetail" +   "WHERE SalesOrderID = 43659";SqlConnection readConnection = new SqlConnection(connectionString);readConnection.Open();SqlCommand readCommand =         new SqlCommand(strSQLGetOrder, readConnection);using (SqlDataReader rdr = readCommand.ExecuteReader()){  while (rdr.Read())  {    ids.Add(rdr["ProductID"]);    qtys.Add(rdr["OrderQty"]);  } } readConnection.Close();            string strSQLUpdateInv = "UPDATE Production.ProductInventory " +  "SET Quantity=Quantity-@amt WHERE (ProductID=@pid)";            SqlConnection writeConnection = new SqlConnection(connectionString);writeConnection.Open();SqlCommand writeCommand = new SqlCommand(strSQLUpdateInv,writeConnection);writeCommand.Parameters.Add("@amt",SqlDbType.Int);writeCommand.Parameters.Add("@pid",SqlDbType.Int);for(int lp=0;lp

This example reads a single sales order from the database (order 43659 is hardcoded), which has a number of items listed on it. These items should be deducted from the inventory, and this is achieved through a second connection. However, to support the proper set up of the queries for the second connection?deducting the correct amounts from the correct products?requires that the results of the first query are cached in memory. And in this example that is achieved using two arraylists. There is an obvious cost here: if we assume this is a high-traffic web site, large amounts of memory would be needed to cache these ultimately throw-away values.

You could also achieve this end by having two connections open concurrently and using the results of the data read from one passed directly to the update command on the second, but there is still memory and database costs in having multiple connections open. As, typically, database connections are more expensive to an application than RAM, the sequential connections approach is used here.

MARS gives you a best-of-both-worlds approach to solving this problem. You can keep a single connection open, reducing the overall connections to the database. Thus, you don't need to have in-memory variables storing the results of the read.

The MARS code is also shorter and therefore easier to read and maintain. The same operation, performed using MARS, is shown in the following snippet:

string connectionString = "Data Source=MEDIACENTER;" +  "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +  "MultipleActiveResultSets=True";string strSQLGetOrder = "Select * from Sales.SalesOrderDetail" +     "WHERE SalesOrderID = 43659";            string strSQLUpdateInv = "UPDATE Production.ProductInventory " +  "SET Quantity=Quantity-@amt WHERE (ProductID=@pid)";SqlConnection marsConnection = new SqlConnection(connectionString);marsConnection.Open();SqlCommand readCommand =     new SqlCommand(strSQLGetOrder, marsConnection);SqlCommand writeCommand =     new SqlCommand(strSQLUpdateInv, marsConnection);writeCommand.Parameters.Add("@amt", SqlDbType.Int);writeCommand.Parameters.Add("@pid", SqlDbType.Int);using (SqlDataReader rdr = readCommand.ExecuteReader()){  while (rdr.Read())  {    writeCommand.Parameters["@amt"].Value = rdr["OrderQty"];    writeCommand.Parameters["@pid"].Value = rdr["ProductID"];    writeCommand.ExecuteNonQuery();  }}marsConnection.Close();

As you can see, this is much easier to read and manage as well as being more efficient with respect to memory and database connections than the previous sample. And, in this case, the code has only one read followed by one write; in a typical circumstance your pre-MARS code is likely to be much more complicated, and thus the savings that MARS can give you will be much more pronounced.

While MARS facilitates multiple active result sets on the same connection, operations on these result sets will still be sequential in nature; if you require parallel processing of data, then multiple connections are still necessary. Also, note that a connection on which you have enabled MARS costs slightly more resources than one that doesn't. Of course, you save in the long run, as you can have multiple commands on this connection, but if you use MARS where you don't need to (i.e. if you only need a single result set), you will negatively impact system performance. Therefore if you are building an application with multiple connections to the database you'll want to think carefully about which ones should have MARS, and which ones shouldn't, in order to maximize efficiency of resource usage.

Overall, this is an excellent addition to ADO.NET that will allow you to code applications much more efficiently. Welcome to MARS!