Multiple Active Result Sets - Yet another powerful feature of SQL Server 2005

MARS [Multiple Active Result Sets ] is a new SQL Server 2005 feature that allows the user to run more than one SQL batch on an open connection at the same time.

If you for instance wanted to do some processing of the data in your data reader and updating the processed data back to the database you had to use another connection object which again hurts performance. There was no way to use the same opened connection easily for more than one batch at the time. There are of course server side cursors but they have drawbacks like performance and ability to operate only on a single select statement at the time.

SQL Server 2005 team recognized the above mentioned drawback and introduced MARS. So now it is possible to use a single opened connection for more than one batch. A simple way of demonstrating MARS in action is with this code:

string strConn = "Data Source=[DATASOURCE];Initial Catalog=[DATABASE];User ID=[UID];Password=[PWD];MultipleActiveResultSets=true";
string strSql = "select DoctorId, PatientId from [Patient] where DoctorId = {0}";
string strOutput = "<br/>DoctorId:{0} - PatientId{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 distinct DoctorId from [Doctor] where HospitalId = 8";



    //Execute the first command
    IDataReader idr1 = cmd1.ExecuteReader();

    while (idr1.Read())
    {
        //Read the first doctor from data source
        int intDoctorId = idr1.GetInt32(0);

        //create another command, which get patients of doctor
        cmd2.CommandText = string.Format(strSql, intDoctorId);

        //Execute the reader
        IDataReader idr2 = cmd2.ExecuteReader();

        while (idr2.Read())
        {
            //Read the doctor and patient
            Response.Write(string.Format(strOutput, idr2.GetInt32(0), idr2.GetInt32(1)));
        }
        //Dont forgot to close second reader, this will just close reader not connection
        idr2.Close();
    }
}

MARS is disabled by default on the Connection object. You have to enable it with the addition of MultipleActiveResultSets=true in your connection string.