Multiple Active Result Sets (MARS - ADO.NET 2.0)

Multiple Active Result Sets (MARS) is a feature in ADO.NET 2.0. It allows execution of multiple batches against Database on a single connection. Preeviously, only one batch could be executed at a time against a single connection. But, execution of multiple batches with MARS does not mean like simultaneous execution of operations.
In previous versions of SQL Server; to access multiple result sets using SqlDataReader objects, a separate SqlConnection object must be used with each SqlCommand object. In ADO.NET 2.0 / SQL Server 2005; MARS feature is disabled by default. It can be enabled by adding the “MultipleActiveResultSets=True” keyword pair to your connection string, as below:

string connectionString = @”Data Source=GTL-263SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI;MultipleActiveResultSets=True“;

Example:

Without MARS, you could only run one batch per connection. some what like below Example:

private void NoMarsImplementation()
{
SqlConnection conn = new SqlConnection(@”Data Source=GTL-263SQLEXPRESS;
Initial Catalog=master;Integrated Security=SSPI;”);

string sql1 = “SELECT * FROM [Customer].[TBL_Customer]”;
string sql2 = “SELECT * FROM [Supplier].[TBL_Supplier]”;

SqlCommand cmd1 = new SqlCommand(sql1, conn);
SqlCommand cmd2 = new SqlCommand(sql2, conn);
cmd1.CommandTimeout = 500;
cmd2.CommandTimeout = 500;
conn.Open();
SqlDataReader dr1 = cmd1.ExecuteReader();
// misc. code with dr1
conn.Close();

conn.Open();
SqlDataReader dr2 = cmd2.ExecuteReader();
// misc. code with dr2
conn.Close();
}

Above Example shows that you can use the same connection with the second SqlDataReader only when you finished using the connection with first one. The connection must be closed and reopened to use same Connection with Second SqlDataReader. Otherwise you would get this error – “There is already an open DataReader associated with this Command which must be closed first”.

with MARS, it is possible to use a single opened connection for more than one batch. Check out below code:

private void MarsImplementation()
{
SqlConnection conn = new SqlConnection(@”Data Source=GTL-263SQLEXPRESS;
Initial Catalog=master;Integrated Security=SSPI;MultipleActiveResultSets=true;“);

string sql1 = “SELECT * FROM [Customer].[TBL_Customer]”;
string sql2 = “SELECT * FROM [Supplier].[TBL_Supplier]”;

In this way, using MARS, you can simply execute an SqlDataReader that will open a Read-Only Cursor; use data from that SqlDataReader and perform Insert / Update function against Database. You only need to Open the SqlConnection object once before you execure the SqlDataReader and Close the Connection (without re-openning) at last after performing Insert / Update function.

Here is another Example, in which we retrieve records from Category Table which is having ParentCategoryID as 0 thru SqlDataReader and Update the Product Table records which is having CategoryID from SqlDataReader.

private void MarsImplementation()
{
//connection string
string connectionString = @”Data Source=GTL-263SQLEXPRESS;Initial Catalog=master;
Integrated Security=SSPI;MultipleActiveResultSets=true;“;

SqlTransaction updateTran = null;
//sqlCommand to open SqlDataReader
SqlCommand catCmd = null;
//sqlCommand to update the Product table
SqlCommand updateCmd = null;

int CategoryID = 0;

//Query to fetch the categories with ParentCategoryID having 0
string catSQL =
“SELECT CategoryID, Name FROM Category ” +
“WHERE ParentCategoryID = 0”;

//update all the product’s quantity which is having CategoryID from above Query
string updateSQL =
“UPDATE Product ” +
“SET Qty = Qty + @OrderQty ” +
“WHERE CategoryID = @CategoryID”;

using (SqlConnection Connection =
new SqlConnection(connectionString))
{
Connection.Open(); //open the connection once here
updateTran = Connection.BeginTransaction();

catCmd = new SqlCommand(catSQL, Connection);
catCmd.Transaction = updateTran;

updateCmd = new SqlCommand(updateSQL, Connection);
updateCmd.Transaction = updateTran;
updateCmd.Parameters.Add(“@Qty”, SqlDbType.Int);
updateCmd.Parameters.Add(“@ProductID”, SqlDbType.Int);

//execute the sqlDataReader
using (SqlDataReader catReader = catCmd.ExecuteReader())
{
while (catReader.Read())
{
CategoryID = (int)catReader[“CategoryID”];

updateCmd.Parameters[“@OrderQty”].Value = 100;
updateCmd.Parameters[“@CategoryID”].Value = CategoryID;
//perform Update against Product table with same connection
//without closing and re-openning it again
//having SqlDataReader already openned
updateCmd.ExecuteNonQuery();
}
}
updateTran.Commit();
}

}