Multiple Active Result Sets (MARS) in SQL Server

Before few days, one of my colleague came with one issue - he was getting error while opening multiple DataReader with single Connection object i.e. he is trying to open 2nd datareader with same connection object in Loop. And he got error - There is already an open DataReader associated with this Command which must be closed first. I search and get way to open multiple DataReader connections with SQLServer and like to share with all.

Sql Server supports MARS (Multiple Active Result Sets) and we need to set MultipleActiveResultSets=true in connection string. Below is Connection string syntax and Code to demonstrate opening datareader in while loop (multiple datareader). MARS is supported from SQL Server 2005 Version.

Connection String:

<add name ="DBConnstr" connectionString ="Data Source=xxx.yyy.zzz.aa;Initial Catalog=name;User Id=id;Integrated Security=False;Password=pwd;MultipleActiveResultSets=true"  providerName ="System.Data.SqlClient" />

Code:

string strConn = ConfigurationManager.ConnectionStrings["DBConnstr"].ConnectionString;
string strSql = "select * From OrderDetail where OrderID = {0}";
string strOutput = "OrdNumber: {0} - Desc: {1}";

using (SqlConnection con = new SqlConnection(strConn))
{
 //Opening Connection   
 con.Open();

 //Creating two commands form current connection   
 SqlCommand cmd1 = con.CreateCommand();
 SqlCommand cmd2 = con.CreateCommand();

 //Set the comment type   
 cmd1.CommandType = CommandType.Text;
 cmd2.CommandType = CommandType.Text;

 //Setting the command text to first command   
 cmd1.CommandText = "select OrderID From [OrderMaster]";

 //Execute the first command   
 IDataReader idr1 = cmd1.ExecuteReader();
 while (idr1.Read())
 {
  //Read the first Tree value from data source       
  int intTree = Convert.ToInt32(idr1.GetInt16(0));

  //create another command, which get values based on Tree value       
  cmd2.CommandText = string.Format(strSql, intTree);

  //Execute the reader       
  IDataReader idr2 = cmd2.ExecuteReader(); // If MARS is not enable in Conn string ; error will come at this line.
  while (idr2.Read())
  {
   //Read the values from Reader 2
   Console.WriteLine(string.Format(strOutput, idr2.GetString(1), idr2.GetString(4)));
  }
  //Dont forgot to close second reader, this will just close reader not connection       
  idr2.Close();
 }
 idr1.Close();
}