If you use DataReader in your applications, you already might have seen the dreaded error message: "There is already an open DataReader associated with this Connection which must be closed first." MARS allows you to avoid this message by allowing you to open multiple SqlDataReader objects on a single connection. MARS enables an application to have more than one SqlDataReader open on a connection when each instance of SqlDataReader is started from a separate command. Each SqlCommand object you add adds an additional session to the connection.
By default, MARS is available only on MARS-enabled hosts. SQL Server 2005 is the first SQL Server version to support MARS. By default, MARS is enabled whenever you use the classes in the System.Data.SqlClient namespace to connect to SQL Server. However, you also can explicitly control this feature by using a keyword pair in your connection string. For example, you can explicitly set the MultipleActiveResultSets attribute in the connection string to True as follows:
- Create a SqlConnection object and initialize it with the appropriate connection string.
- Open the connection by using the Open method of the SqlConnection object.
- Create individual SqlCommand objects with the required parameters to execute the query. While creating the SqlCommand objects, remember to associate them with the previously created SqlConnection object.
- Once you have created the SqlConnection object, you then can invoke the ExecuteReader method of the SqlCommand object to execute the queries.
- Finally, close the SqlConnection object by executing the Close method.
The example this article presents demonstrates how to utilize MARS in a Web form. The example displays categories and product information from the Northwind database. For each category you retrieve from the categories table, you then will query the products table (with the category ID as an argument) to return all the products that belong in that category. You will learn how to implement the MARS functionality using ADO.NET and SQL Server 2005 for this scenario.
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
void Page_Load(Object e, EventArgs sender)
string connectionString =
SqlDataReader productReader = null;
string categorySql = "SELECT CategoryID, CategoryName
string productSQL = "SELECT ProductID, ProductName,
QuantityPerUnit FROM Products " +
"WHERE CategoryID = @CategoryID";
using (SqlConnection northwindConnection = new
//Check if the SQL Server supports MARS
SqlCommand categoryCommand = new
SqlCommand productCmd = new
using (SqlDataReader categoryReader =
// Executing Multiple Commands using a
// single Connection
productReader = productCmd.ExecuteReader();
GridView productView =
productView.ID = "ProductView" +
productView.Visible = true;
Found in this
Response.Write("MARS is not supported in this
version of SQL Server");
<html xmlns="http://www.w3.org/1999/xhtml" >
<form id="form1" runat="server">
Then, the code initializes the categorySql and productsSql variables with the appropriate SQL statements. After that, it creates an instance of the SqlConnection object by utilizing a "using" statement. Then, it opens the connection string by calling the Open method of the SqlConnection object. Next, it checks the ServerVersion property of the SqlConnection to see whether the SQL Server supports MARS. If the ServerVersion property returns a major version of "09", you safely can assume that SQL Server supports MARS.
The code next creates instances of the SqlCommand object and assigns them to the categoryCommand and productCmd variables, respectively. Apart from that, it also adds the CategoryID parameter to the productCmd variable. Then, it executes the query contained in the categoryCommand object by invoking the ExecuteReader method of the categoryCommand object. It captures the results of the SQL query execution in a SqlDataReader variable and then loops through all the records in that SqlDataReader object.
The code adds the category name to the PlaceHolder control through a LiteralControl and then retrieves the products for each category by invoking the ExecuteReader method of the productCmd object. If the productReader object contains any valid rows, it simply data binds that object with a dynamically created GridView control. While creating the GridView control, it also sets various properties of the GridView control, such as Visible, BackColor, and ForeColor. If the productReader object has no valid rows, it simply displays the message: "No Products Found in this category."
Figure 1. The MarsExample.aspx Web Form Output
In general, you should not need to modify existing applications when using a MARS-enabled connection. If you want to use MARS features in your applications, you should carefully evaluate some of the following factors.
As you already know, MARS operations execute synchronously on the server. Even though statement interleaving of SELECT and BULK INSERT statements are allowed, data manipulation language (DML) and data definition language (DDL) statements execute atomically. Any statements attempting to execute while an atomic batch is executing will be blocked.
For example, if you submit two batches under a MARS connection, with one containing a SELECT statement and the other containing a DML statement, the DML can begin execution within the execution of the SELECT statement. However, the DML statement must be completed before the SELECT statement can continue its execution. If both statements are running under the same transaction, any changes made by a DML statement after the SELECT statement has started execution are not visible to the read operation.
When you open a connection with MARS enabled (which is the default case), a logical session is created; this adds additional overhead. SqlClient will cache a MARS session within a connection to minimize the overhead and enhance performance. The cache and sessions contained in it are per connection—they are not shared across connections. The cache will contain at most 10 MARS sessions. This value is not configurable. If the session limit is reached, a new session will be automatically created without throwing an error. When a session is released, it is returned to the pool so that it can be re-used. However, the session will be released to the pool only when the pool's upper limit is not reached. Otherwise, the session is closed.
MARS-enabled connections also support connection pooling and are pooled like any other connection. However, MARS-enabled connections and MARS-unaware connections are not considered the same. Because of that, they are kept in separate connection pools. For example, if an application opens two connections, one with MARS enabled and one with MARS disabled as the only distinction, the two connections will be in separate pools.
In SQL Server 2000, all the batches executed under the same connection share the same batch environment. When you make a change to the batch environment, that change is visible to all subsequent batches. With MARS, a default execution environment is associated with a connection. Every new batch that starts executing under a given connection receives a copy of the default environment. Whenever code is executed under a given batch, all changes made to the environment are scoped to the specific batch. Once execution finishes, the execution settings are copied into the default environment.
As previously mentioned, MARS does not support parallel execution of multiple commands against the database. Also, MARS is not designed to remove all need for multiple connections in an application. If your application needs true parallel execution of commands against a server, you should consider using multiple connections instead of MARS.
As you can see, MARS provides a number of improvements when it comes to executing multiple queries against the database using a single connection. By appropriately using MARS in relevant places, you can not only produce cleaner looking code that is easy to maintain but also see performance improvements in some areas. The example in this article represents just the tip of the iceberg, but it should give you a kick start on this new feature. Once you get familiar with it, you will have one more option when it comes to issuing multiple queries against a database connection.
Thiru Thangarathinam has six years of experience in architecting, designing, developing, and implementing applications using object-oriented application development methodologies. He also possesses a thorough understanding of the software life cycle (design, development, and testing). He holds several certifications, including MCAD for .NET, MCSD, and MCP. Thiru is an expert with ASP.NET, .NET Framework, Visual C# .NET, Visual Basic .NET, ADO.NET, XML Web services, and .NET Remoting. Thiru also has authored numerous books and articles. Contact him at firstname.lastname@example.org.