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 [email protected] WHERE ([email protected])";            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

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 [email protected] WHERE ([email protected])";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();