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

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read