MARS Concept in SqlDataReader

Note that while a DataReader is open, the Connection is in use exclusively by that DataReader. You will not be able to execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.
This is over come in .Net 2005 by a concept called MARS(MultipleActiveResources).
If MultipleActiveResources=True is added to connection string then more that one data reader can be opened at the same time in that single connection object. It’s a new feature in ADO.NET 2.0.

Example Data Reader with MARS Concept:
SqlConnection sqlConn = new SqlConnection("Data Source=Server1;Initial Catalog=XXXXDB;Persist Security Info=True;User ID=XXXXX;Password=XXXXX;MultipleActiveResultsets=True");

// In above connection string if you did not write MultipleActiveResultsets=True will get Exception as {There is already an open DataReader associated with this Command which must be closed first.}

SqlCommand cmdDept = new SqlCommand();
cmdDept.Connection=sqlConn;
cmdDept.CommandText="select DeptID,DeptName from Department";
sqlConn.Open();
SqlDataReader DRDept=cmdDept.ExecuteReader();
string tempStr=string.Empty ;

while (DRDept.Read())
{
SqlCommand cmdEmp=new SqlCommand("select * from Employee where Deptid=" + DRDept["DeptID"],sqlConn);
SqlDataReader DREmp = cmdEmp.ExecuteReader();

while (DREmp.Read())
{
tempStr = tempStr + DREmp["EmpID"] + "\t" + DREmp["EmpName"] + "\t" + DREmp["EmpSalary"] + "\t"+ DRDept["DeptName"] + "\n";
}

DREmp.Close();
}
DRDept.Close();
sqlConn.Close();

MessageBox.Show(tempStr);

Note: The result of one select statement should not overlap with the other result of another statement, Means both the select statements should not perform on Same Table.