Multiple Active Result Sets (MARS) in ADO.NET 2.0 and SQL Server 2005

Posted by Rickie Lee, http://rickie.cnblogs.com

Multiple Active Result Sets (MARS) is a new feature of ADO.NET 2.0 that provides the capability to open more than one result set over the same connection and lets you access them all concurrently. Prior to MARS, each result set required a separate connection. Currently, the first commercial database to support MARS is SQL Server 2005.

1. Enable MARS by setting MultipleActiveResultSets=True in the connection string

<connectionStrings>

      <add name="Northwind" connectionString="Server=localhost; Database=Northwind; User ID=sa; Password=developer; MultipleActiveResultSets=True" />

</connectionStrings>

Otherwise, you will get the following exception.

"Systerm.InvalidOperationException: There is already an open DataReader associated with this connection which must be closed first".

This setting only has an effect when used with SQL Server 2005 or a later version.

2. Follow these steps to create a demo web page.

(1) Retrieve the Order result set using a SqlDataReader object and binds it to a GridView control.

(2) Set up the OnRowDataBound property of the GridView control.

        <asp:GridView ID="gvOrders" Runat="server" AutoGenerateColumns="False"

                OnRowDataBound="gvOrders_RowDataBound" Width="100%">

When the GridView control starts to bind the DataReader, it starts firing the OnRowDataBound event for each record.

(3) Create an OnRowDataBound event handler.

In the method, we can get reference to each DataReader record by using the IDataRecord interface, then access the specified column and get the value. Finally, we retrieve the database again over the same SQL connection.

        IDataRecord OrderRecord;

        // Retrieving the currently bound record from the Data Reader

        // using the IDataRecord interface

        OrderRecord = e.Row.DataItem as IDataRecord;

        // Retrieving reference to the Label Control inside the current

        // GridView row. This Label will be populated with Order Details

        lblOrderDetail = e.Row.FindControl("lblOrderDetail") as Label;

        if ((OrderRecord == null) || (lblOrderDetail == null))

            return;

      ………………………………………        

The following full code of the ASPX page is abstracted from the reference 1. Please get more detail information in the book.

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>

<script runat="server">
    // Declaring connection here allows us to use it inside all methods 
    // of this class
    SqlConnection DBCon;
     
    protected void Page_Load(object sender, EventArgs e)
    {

        SqlCommand Command = new SqlCommand();
        SqlDataReader OrdersReader;

        DBCon = new SqlConnection();
        DBCon.ConnectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

        Command.CommandText =
                " SELECT TOP 100 Customers.CompanyName, Customers.ContactName, " +
                " Orders.OrderID, Orders.OrderDate, " +
                " Orders.RequiredDate, Orders.ShippedDate " +
                " FROM Orders, Customers " +
                " WHERE Orders.CustomerID = Customers.CustomerID " +
                " ORDER BY Customers.CompanyName, Customers.ContactName ";

        Command.CommandType = CommandType.Text;
        Command.Connection = DBCon;

        // Opening the connection and executing the SQL query. 
        DBCon.Open();
        OrdersReader = Command.ExecuteReader(CommandBehavior.CloseConnection);

        /**//*
        DataTable myTable = new DataTable();
        myTable.Load(OrdersReader);
        */
         
        // Binding the Data Reader to the GridView control
        gvOrders.DataSource = OrdersReader;
        gvOrders.DataBind();

        // Closing connection after we are done processing all order records
        DBCon.Close();
    }

    protected void gvOrders_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        IDataRecord OrderRecord;
        Label lblOrderDetail;

        // Retrieving the currently bound record from the Data Reader
        // using the IDataRecord interface
        OrderRecord = e.Row.DataItem as IDataRecord;

        // Retrieving reference to the Label Control inside the current 
        // GridView row. This Label will be populated with Order Details
        lblOrderDetail = e.Row.FindControl("lblOrderDetail") as Label;

        if ((OrderRecord == null) || (lblOrderDetail == null))
            return;
        
        SqlCommand Command = new SqlCommand();
        SqlDataReader OrderDetailReader;

        // Creating an SQL query to retrieve details 
        // for the currently processed order
        Command.CommandText = 
                "SELECT Products.ProductName, [Order Details].UnitPrice, " +
                " [Order Details].Quantity, [Order Details].Discount " +
                " FROM [Order Details], Products " +
                " WHERE [Order Details].ProductID = Products.ProductID " +
                " AND [Order Details].OrderID = " +
                Convert.ToString(OrderRecord["OrderID"]);

        Command.CommandType = CommandType.Text;

        // Reusing the same connection object that was used in retrieving 
        // allorder records from the Orders table
        Command.Connection = DBCon;

        // Executing SQL query without passing CommandBehavior.CloseConnection 
        // as parameter to ExecuteReader. We don't want the connection
        // to automatically close because we want to reuse it for more operations
        OrderDetailReader = Command.ExecuteReader();

        while (OrderDetailReader.Read())
        {
            // Populating the lable control with the product name field
            lblOrderDetail.Text += OrderDetailReader[0].ToString() + " " + OrderDetailReader[1].ToString() + "<Br>";
        }
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Multiple Active Result Sets</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblCounter" Runat="server"></asp:Label>
        <br />
        <asp:GridView ID="gvOrders" Runat="server" AutoGenerateColumns="False" 
                OnRowDataBound="gvOrders_RowDataBound" Width="100%">
            <Columns>
        <asp:BoundField HeaderText="Company Name"                 
                DataField="CompanyName"></asp:BoundField>
        <asp:BoundField HeaderText="Contact Name" 
                DataField="ContactName"></asp:BoundField>
        <asp:TemplateField>
        <HeaderTemplate>
                Order Detail
        </HeaderTemplate>
        <ItemTemplate>
                <asp:Label ID="lblOrderDetail" runat="server"></asp:Label>
        </ItemTemplate>
                    
        </asp:TemplateField>
                <asp:BoundField HeaderText="Order Date" DataField="orderdate" 
                        DataFormatString="{0:d}"></asp:BoundField>
                <asp:BoundField HeaderText="Required Date" DataField="requireddate" 
                        DataFormatString="{0:d}"></asp:BoundField>
                <asp:BoundField HeaderText="Shipped Date" DataField="shippeddate" 
                        DataFormatString="{0:d}"></asp:BoundField>
            </Columns>
        </asp:GridView><br />
        <br />    
    </div>
    </form>
</body>
</html>

References:

1. Professional ASP.NET 2.0, by Bill Evjen, Scott Hanselman, Farhan Muhammad, Srinivasa Sivakumar, Devin Rader. Wrox - Wiley Publishing Company 2005