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)