ADO.NET Review: Multiple Active Result Set (MARS)

Quoting from the SQL Programmability and API Development Team Blog: Multiple Active Result Set or MARS is defined as

a new programming interface introduced in SQL Server 2005. It
enables multiple result sets to exist at the same time for a given
connection. To say it in a simple way, you can make a connection to
server and then submit multiple requests to serve

To illustrate, [more]say we have this code which simply retrieves ProductCategory from the AdventureWorks database, loop through the resulting data reader and for each loop retrieve subcategories for the current category

* (if you don't have AdventureWorks DB you can get it from here, install and attach the MDF file).

    1 using System;

    2 using System.Data;

    3 using System.Data.SqlClient;

    4 

    5 namespace ConsoleSample

    6 {

    7     class Program

    8     {

    9         static void Main(string[] args)

   10         {

   11             // connection string (without MARS enabled)

   12             string connectionString =

   13                 @"Data Source=.sql2005;Initial Catalog=AdventureWorks;Integrated Security=SSPI;";

   14 

   15             using (SqlConnection conn = new SqlConnection(connectionString))

   16             {

   17                 conn.Open();

   18 

   19                 string selectCategories =

   20                     "SELECT ProductCategoryID, Name FROM [Production].[ProductCategory]";

   21 

   22                 string selectSubCategories =

   23                     "SELECT ProductSubcategoryID, Name FROM [Production].[ProductSubcategory] WHERE ProductCategoryID = @ProductCategoryID";

   24 

   25                 SqlCommand cmdCategory = new SqlCommand(selectCategories, conn);

   26                 SqlCommand cmdSubCategory = new SqlCommand(selectSubCategories, conn);

   27 

   28                 cmdSubCategory.Parameters.Add("@ProductCategoryID", SqlDbType.Int);

   29 

   30                 // retrieve categories

   31                 using (SqlDataReader rdrCategory = cmdCategory.ExecuteReader())

   32                 {

   33                     while (rdrCategory.Read())

   34                     {

   35                         int categoryID = rdrCategory.GetInt32(0);

   36                         string categoryName = rdrCategory.GetString(1);

   37                         Console.WriteLine(categoryName); // display category name

   38 

   39                         // now we retrieve subcategories for the current category

   40                         cmdSubCategory.Parameters["@ProductCategoryID"].Value = categoryID;

   41                         using (SqlDataReader rdrSubCategory = cmdSubCategory.ExecuteReader())

   42                         {

   43                             while (rdrSubCategory.Read())

   44                             {

   45                                 int subCategoryID = rdrSubCategory.GetInt32(0);

   46                                 string subCategoryName = rdrSubCategory.GetString(1);

   47                                 Console.WriteLine("SubCategory – " + subCategoryID + " : " + subCategoryName);

   48                             }

   49                         }

   50 

   51                         // just a separator between displayed categories

   52                         Console.WriteLine("-".PadLeft(20, '-'));

   53                     }

   54                 }

   55             }

   56 

   57             Console.WriteLine(" ** Query Succesful ** ");

   58             Console.ReadKey();

   59         }

   60     }

   61 }

If you run the sample you would get an error (see image below) at line 41 

This is because without MARS enabled (and in earlier versions of SQL Server) the programming model is restricted such that at any point in time there would be at most one pending request on a given session (or effectively the opened connection).

Without much further explanation to enable MARS all you have to do is add a keyvalue pair in your connection string as in our example should look something like

@"Data Source=.sql2005;Initial Catalog=AdventureWorks;Integrated Security=SSPI;MultipleActiveResultSets=true;";

Notice the MultipleActiveResultSets=true; that was appended at the end

And with the modified connection string, if we run the code it should result to 

 

Cool isn't it. Common uses include the master detail relationships similar to above (e.g. Products under a particular Category)

However, this is not to say that MARS is suitable for all situations so I would recommend reading more from the following:

http://blogs.ittoolbox.com/c/coding/archives/mars-multiple-active-result-set-16003

http://blogs.msdn.com/sqlprogrammability/search.aspx?q=mars&p=1

Hope it helps and coming up next, two/more queries (and thus two/more results sets) in one SqlCommand (not related to MARS)

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I will never give away, trade or sell your email address. You can unsubscribe at any time.