What is MARS (Multiple Active Resultsets)

Posted by: Sahil Malik [MVP], on 18 Jul 2005 | View original | Bookmarked: 0 time(s)

MARS is the 4th planet from sun - very funny !!!

Anyhow, MARS in ADO.NET refers to "Multiple Active Resultsets". You gotta have SQL Server 2005/ADO.NET 2.0 to make use of this. (Allright Oracle has it too - so sit tight in your seat before you call shenanigans, that is described towards the end).

MARS lets you have two parallel resultsets on the very same connection. Two resultsets that originated due to the execution of two commands. There are two important things to note about this however.

1. The commands are not executing in parallel, they are executing in an interleaved fashion - only the resultsets are maintained in parallel.
2. There can only be one transaction at the maximum. Parallel resultsets does not mean multiple transactions.

So what does this mean This means, the following code will work -

.....
SqlCommand cmd1 = testConn.CreateCommand();
SqlCommand cmd2 = testConn.CreateCommand();
.....
testConn.Open() ;
SqlDataReader dr1 = cmd1.ExecuteReader();
SqlDataReader dr2 = cmd2.ExecuteReader();
....

Before you jump in your seat and hit the roof, yes you still have to close the connection, dispose it, add the necessary parameters, close data readers, all that bullcrap that comes with the proper usage of ADO.NET.

There is one additional thing you need to do to make MARS work - modify your connection string. By default, MARS is disabled in the framework (Rightfully so, because some idiot will misuse it, come to the newsgroups and some guy with a TightSkirt will confuse him further). So to enable MARS, you need to add the following to your connection string -

"Asynchronous Processing = true"

Allrighty, once you put all the concepts from above together, your final code looks like as below -

private static string connectionString = 
   "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;Asynchronous Processing=true;";
static void Main(string[] args)
{
   
using (SqlConnection testConn = new SqlConnection(connectionString))
   {
      SqlCommand cmd1 = testConn.CreateCommand();
      SqlCommand cmd2 = testConn.CreateCommand();

      cmd1.CommandText = "Select top 5 * from HumanResources.Employee";
      cmd2.CommandText = "Select * from HumanResources.EmployeeAddress where employeeID = @EmployeeID";

      testConn.Open();

      SqlDataReader dr1 = cmd1.ExecuteReader();
      cmd2.Parameters.AddWithValue("@EmployeeID", ");
      
if (dr1.HasRows)
      {
         
while (dr1.Read())
         {
            cmd2.Parameters[0].Value = dr1["EmployeeID"];
            SqlDataReader dr2 = cmd2.ExecuteReader();
            
if (dr2.HasRows)
            {
               
while (dr2.Read())
               {
                  Console.WriteLine("EmployeeID: " + dr1["EmployeeID"] + " has AddressID: " + dr2["AddressID"]);
               }
            }
            dr2.Close();
         }
      }
      dr1.Close();
   }
}

Oh very nice !! So I can execute two SQL commands, even one based on another, on the very same connection (just remember to close your DataReaders).

Now what if one of the commands was an Update statement What if there were three commands What if one was an update, one was an insert, and one was a select So does my select reflect the results of my update, or my insert Oh my, these questions are making me jump in excitement. I will blog about them some other day, because to tell you the truth dude, I am bit burnt out after typing more than 800+ pages in my upcoming book - all this is in my book in Chapter 11 anyway, but let me quickly touch up upon how multiple resultsets may work in Oracle.

Well multiple resultsets in oracle is acheived .. well it ain't as straightforward as above. U gotta make the following changes.

a) Your command must be a stored procedure that returns multiple ref cursors.
b) Your command must have parameters of type OracleDbType.RefCursor with parameter direction out.
c) Then on the cursors after command execution, you can run GetDataReader (of course you need to cast them to OracleRefCursor first) - and bingo you have parallel data readers running on Oracle.

Of course, you can also do multiple resultsets using a single OracleDataReader and the NextResult method.

.. Now let me focus the discussion back on MARS in terms of Sql Server.

So SQL Server has MARS - Woohoo .. !! But you gotta be careful of this weirdass thing called Session pools. Session pool is like .. umm .. a pool of maximum 10 interleaved commands per connection. And guess what - that is shared between DataAdapters and DataReaders. In other words, if you try running more than 10 commands, the 11th will simply have to sit on a side and wait. This again goes to prove that don't share a single instance of SqlConnection, don't try to reinvent the wheel - let connection pool do it's magic.

Another thing to watch out as far as MARS goes is that don't use MARS with OleDB. Yup. (I am curious, why the heck would you want to, but anyway, don't do it). This is because OleDb doesn't really support MARS, it just makes it look like it does. A bit like the waitress I met who gave me the impression that she understood what I had asked for, but she brought me what she thought she understood. What OleDb will do underneath the seams is it will open a brand new physical database connection for every subsequent command. (YUCK). This means

a) Your app will come grinding on it's knees when you least expect it (in production). and
b) You may even cause deadlocks between the various running commands - SQL Server will detect and resolve the deadlock, but you still don't get the results you had expected right ;-).

So just don't do it - Just don't do OleDb & MARS - AWRITE !!

Now, don't think that using MARS on SqlConnection is completely problem free and you can do whatever the heck you wish. You have to be very very careful of certain rules regarding transactional behavior, and certain commands overriding others. .. Whew .. I'm tired .. of typing this longass post, but if you are still reading it .. someday in the future I will blog about MARS in the light of Transactions, MARS in light of DML commands, MARS in light of savepoints, nested transactions, sequential reads, interleaved reading (not execution), MARS with System.Transactions, MARS with DML and System.Transactions .. so on so forth .. or you can simply grab a copy of my upcoming ADO.NET 2.0 book and flip over to Chapter #11.

Category: ASP.NET | Other Posts: View all posts by this blogger | Report as irrelevant | View bloggers stats | Views: 5369 | Hits: 22

Similar Posts

  • Microsoft and the Apache Stonehenge Project more
  • DarkRoom Macros for Microsoft Word more
  • Building Browser Helper Objects using Managed Code more
  • Getting the MARS Sample working in the June CTP more
  • ADO.NET 2 Presentation - June 29th more
  • Webcast on using MARS (ADO.NET 2.0/SQL Server 2005) more

News Categories

.NET | Agile | Ajax | Architecture | ASP.NET | BizTalk | C# | Certification | Data | DataGrid | DataSet | Debugger | DotNetNuke | Events | GridView | IIS | Indigo | JavaScript | Mobile | Mono | Patterns and Practices | Performance | Podcast | Refactor | Regex | Security | Sharepoint | Silverlight | Smart Client Applications | Software | SQL | VB.NET | Visual Studio | W3 | WCF | WinFx | WPF | WSE | XAML | XLinq | XML | XSD