MARS ( Multiple Active Result Sets)

ADO.NET 3.5 supports Multiple Active Result Sets meaning that we can execute multiple batches on a single connection.
Before that If we wanted to execute two command objects against a database using a single connection object, we would open the connection object, execute first command object against it and then close the connection object. Then we would open the connection object again to execute the second command object against it. We could not execute the second command object without closing and reopening the connection object again.
With MARS support enabled , considering the same above scenario we can open the connection object, execute the first command against it and then without closing it we can execute the second command object against that connection and then close the connection. We do not need to close and then reopen the connection object before executing second command object on it.

How to enable MARS support
By default MARS support is disabled you can enable it by providing 'MultipleActiveResultSets= TRUE' key/value pair in your connection string.

Senarios for using MARS
Suppose a dumb situation. You have two tables 'Product' and 'Purchase History'. You read all the products from table 'Product', if the quantity of product is greater than zero you update the quantity and log an entry in 'PurchaseHistory' table. All of these operations are performed using single connection object. As from the code below you will see that we have opened the connection object once then we have performed read operation, update operation and insert operation all on that single connection object without closing and reopening the connection again and again.


using (SqlConnection sqlconn = new SqlConnection(getConnectionString()))
{
//Open the connection
sqlconn.Open();

//Create command objects
SqlCommand readComm = new SqlCommand();
SqlCommand updateComm = new SqlCommand();
SqlCommand insertComm = new SqlCommand();

//Use same connection object for all command objects
readComm.Connection = sqlconn;
updateComm.Connection = sqlconn;
insertComm.Connection = sqlconn;

//Assign SQL queries to command objects to execute
readComm.CommandText = "Select * from Product";

updateComm.CommandText = "Update Product set Quantity = Quantity -1 where ProductID = @ProductID";

insertComm.CommandText = "Insert into PurchaseHistory values(@ProductID,@ProductName,@PurchaseDate)";

updateComm.Parameters.Add("@ProductID", SqlDbType.Int);
insertComm.Parameters.Add("@ProductID", SqlDbType.Int);
insertComm.Parameters.Add("@ProductName", SqlDbType.VarChar);
insertComm.Parameters.Add("@PurchaseDate", SqlDbType.SmallDateTime);

//Reading from table 'Product'
using (SqlDataReader productReader = readComm.ExecuteReader())
{
while (productReader.Read())
{

if ((int)productReader["Quantity"] > 0)
{

//Updating 'Product' table
updateComm.Parameters["@ProductID"].Value = (int)productReader["ProductID"];
updateComm.ExecuteNonQuery();

//Adding entry into 'PurchaseHistory' table
insertComm.Parameters["@ProductID"].Value = (int)productReader["ProductID"];
insertComm.Parameters["@ProductName"].Value = productReader["ProductName"].ToString();
insertComm.Parameters["@PurchaseDate"].Value = DateTime.Now.Date;
insertComm.ExecuteNonQuery();

}
}
}

//Closing the connection
sqlconn.Close();
}

Before running this code we will need to enable MARS support by adding 'MultpleActiveResultSets = True' key/value pair in our connection string. If we run the above snippet without enabling the MARS then we will receive the following error message:

'There is already an open DataReader associated with this Command which must be closed first.'

As these multiple operations are operating on a single connection they can share same transaction context. For example if insert in 'PurchaseHistory' table fails we can rollback the update in 'Product' table. Without MARS these operations would had to operate on reopened or new connection object and thus command objects would not been able to share the transaction context.

Points to remember

1. MARS does not means asynchronous or parallel execution of commands. It allows multiple command objects to be executed on a single connection object but they are executed synchronously not asynchronously.
2. MARS is not thread safe.
3. When connection is opened with MARS enabled, logical sessions are created for each added command object which is an overhead associated with MARS.