Wednesday, July 11, 2007

Want to Execute Multiple Queries on a Single Connection? Go to MARS

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:

string northwindConnectionString =
"Server=localhost;Database=Northwind;" +

Similarly, you also can disable MARS for a particular connection by specifying "MultipleActiveResultSets=False" in your connection string:

string northwindConnectionString =
"Server=localhost;Database=Northwind;" +

Now that you have had an introduction to MARS, here are the steps for using it from ADO.NET 2.0:

The following sections provide an example that will help you understand the implementation process.

MARS Implementation

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.

First, create a new Web site named MARS in Visual Studio 2005. Next, add a Web page named MarsExample.aspx to it. Now, modify the code in the MarsExample.aspx file to look like the following:

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
void Page_Load(Object e, EventArgs sender)
string connectionString =
int categoryID;
SqlDataReader productReader = null;
string categorySql = "SELECT CategoryID, CategoryName
FROM Categories";
string productSQL = "SELECT ProductID, ProductName,
QuantityPerUnit FROM Products " +
"WHERE CategoryID = @CategoryID";
using (SqlConnection northwindConnection = new
//Check if the SQL Server supports MARS
if (northwindConnection.ServerVersion.StartsWith("09"))
SqlCommand categoryCommand = new
SqlCommand(categorySql, northwindConnection);
SqlCommand productCmd = new
SqlCommand(productSQL, northwindConnection);
using (SqlDataReader categoryReader =
while (categoryReader.Read())
LiteralControl("<b>" +
categoryReader["CategoryName"] +
categoryID =
productCmd.Parameters["@CategoryID"].Value =
// Executing Multiple Commands using a
// single Connection
productReader = productCmd.ExecuteReader();
using (productReader)
if (productReader.HasRows)
GridView productView =
new GridView();
productView.ID = "ProductView" +
productView.DataSource =
productView.Visible = true;
productView.ForeColor =
productView.BackColor =
LiteralControl("No Products
Found in this

Response.Write("MARS is not supported in this
version of SQL Server");
<html xmlns="" >
<head runat="server">
<title>MARS Example</title>
<form id="form1" runat="server">
<asp:PlaceHolder ID="PlaceHolder1"

Take a closer look at what the lines in the above code do. First, the code retrieves the connection string from the web.config file by using the following line:

string connectionString =

The above line of code retrieves the connection string from the ConnectionStrings section of the web.config file. The connection string is stored in the web.config as follows:

<add name="northwindConnection"

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."

If you browse to the above Web form using a browser, you will see the output displayed in Figure 1.

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.

Statement Interleaving

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.

MARS Session Cache

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.

Thread Safety

MARS operations are not thread safe.

Connection Pooling

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.

Top-level Temporary Tables

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.

Multiple Connections

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.

Get Grounded in 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.

About the Author

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

No comments: