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

Multiple Active Result Sets (MARS) is a new feature released with ADO.NET 2.0 that allows you to execute multiple queries or stored procedures on a single connection. The result is multiple forward-only, read-only result sets. In previous versions of ADO.NET, you could execute only one query at any given time using a single connection. Now, with the introduction of SQL Server 2005 and ADO.NET 2.0, you very easily can execute multiple logical batches in a single connection. This article demonstrates how to utilize MARS in an ASP.NET Web page. It also discusses the situations in which MARS is appropriate and can provide performance benefits.

Welcome 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;" +
    "Trusted_Connection=True;MultipleActiveResultSets=True";

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

string northwindConnectionString =
    "Server=localhost;Database=Northwind;" +
    "Trusted_Connection=True;MultipleActiveResultSets=False";

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

  • 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 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" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
          "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
    void Page_Load(Object e, EventArgs sender)
    {
        string connectionString =
            System.Configuration.ConfigurationSettings.
            ConnectionStrings["northwindConnection"].
                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 
        SqlConnection(connectionString))
        {
            northwindConnection.Open();
            //Check if the SQL Server supports MARS
            if (northwindConnection.ServerVersion.StartsWith("09"))
            {
                SqlCommand categoryCommand = new
                    SqlCommand(categorySql, northwindConnection);
                SqlCommand productCmd = new 
                    SqlCommand(productSQL, northwindConnection);
                productCmd.Parameters.Add("@CategoryID",
                                          SqlDbType.Int);
                using (SqlDataReader categoryReader = 
                categoryCommand.ExecuteReader())
                {
                    while (categoryReader.Read())
                    {
                        PlaceHolder1.Controls.Add(new
                            LiteralControl("<b>" +
                        categoryReader["CategoryName"] + 
                            "</b><br>"));
                        categoryID =
                            (int)categoryReader["CategoryID"];
                        productCmd.Parameters["@CategoryID"].Value =
                        categoryID;
                        // Executing Multiple Commands using a
                        // single Connection
                        productReader = productCmd.ExecuteReader();
                        using (productReader)
                        {
                            if (productReader.HasRows)
                            {
                                GridView productView =
                                    new GridView();
                                productView.ID = "ProductView" +
                                categoryID.ToString();
                                productView.DataSource =
                                    productReader;
                                productView.DataBind();
                                productView.Visible = true;
                                productView.ForeColor =
                                System.Drawing.Color.DarkCyan;
                                productView.BackColor =
                                System.Drawing.Color.Snow;
                                PlaceHolder1.Controls.
                                    Add(productView);
                            }
                            else
                                PlaceHolder1.Controls.Add(new
                                    LiteralControl("No Products
                                                    Found in this
                                                    category<br>"));
                        }
                        PlaceHolder1.Controls.Add(new 
                        LiteralControl("<br>"));
                    }
                    
                }
            }
            else
                Response.Write("MARS is not supported in this
                                version of SQL Server");
        }
    }
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>MARS Example</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:PlaceHolder ID="PlaceHolder1" 
                         Runat="Server"></asp:PlaceHolder>
    </div>
    </form>
</body>
</html>

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 =
     System.Configuration.ConfigurationSettings.
     ConnectionStrings["northwindConnection"].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:

<connectionStrings>
   <add name="northwindConnection"
        connectionString="server=(local)\SqlExpress;Integrated
        Security=True;Database=Northwind;"/>
</connectionStrings>

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

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

Advantages of MARS

MARS provides the following advantages:

  • It provides a lighter weight alternative for applications that use multiple connections to overcome lack-of-MARS limitations. However, this is not always the case because multiple connections do provide parallel execution in the server (provided they're not enlisted in the same transaction).
  • It enables multiple SqlDataReaders. Prior to MARS, only one command or resultset could be active at one time on a connection. This required the use of two or more connections to execute multiple queries. As the code example showed, MARS allows you to execute multiple queries against a single connection. However, note that MARS does not enable parallel execution of queries; it enables only sequential execution of multiple queries.

Factors to Consider When Using MARS

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 thiruthangarathinam@yahoo.com.



Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • IBM Worklight is a mobile application development platform that lets you extend your business to mobile devices. It is designed to provide an open, comprehensive platform to build, run and manage HTML5, hybrid and native mobile apps.

  • Live Event Date: November 20, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Are you wanting to target two or more platforms such as iOS, Android, and/or Windows? You are not alone. 90% of enterprises today are targeting two or more platforms. Attend this eSeminar to discover how mobile app developers can rely on one IDE to create applications across platforms and approaches (web, native, and/or hybrid), saving time, money, and effort and introducing apps to market faster. You'll learn the trade-offs for gaining long …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds