Piotr Rodak

MARS - does anyone use it?

I read recently about MARS - Multiple Active Result Sets, functionality that came with SQL Server 2005. I tried to find some 'real life' example of using MARS. Most of the resources I found showed examples on AdventureWorks  database and they were, to say the least, showing how NOT to access the database. For example this article by Lawrence Moroney, shows two ways of updating inventory on AdventureWorks database. The first way requires opening the connection twice, once to read order details, second time to update inventory. The second way allows to save one connection by interleaving read operation with updates.

string connectionString = "Data Source=MEDIACENTER;" +
  "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +
  "MultipleActiveResultSets=True";
string strSQLGetOrder = "Select * from Sales.SalesOrderDetail" + 
    "WHERE SalesOrderID = 43659";
            
string strSQLUpdateInv = "UPDATE Production.ProductInventory " +
  "SET Quantity=Quantity-@amt WHERE (ProductID=@pid)";

SqlConnection marsConnection = new SqlConnection(connectionString);
marsConnection.Open();

SqlCommand readCommand = 
    new SqlCommand(strSQLGetOrder, marsConnection);
SqlCommand writeCommand = 
    new SqlCommand(strSQLUpdateInv, marsConnection);

writeCommand.Parameters.Add("@amt", SqlDbType.Int);
writeCommand.Parameters.Add("@pid", SqlDbType.Int);
using (SqlDataReader rdr = readCommand.ExecuteReader())
{
  while (rdr.Read())
  {
    writeCommand.Parameters["@amt"].Value = rdr["OrderQty"];
    writeCommand.Parameters["@pid"].Value = rdr["ProductID"];
    writeCommand.ExecuteNonQuery();
  }
}
marsConnection.Close();

Now Lawrence writes that this is cleaner code comparing to 'classic' approach. While there are fewer lines, this is not what I would like developers to do when it comes to updating tables. This is client side RBAR (read Jeff Moden's articles- they are excellent!)

 The cleaner approach, besides using proper stored procedure is this:

string connectionString = "Data Source=MEDIACENTER;" +
  "Initial Catalog=AdventureWorks;Integrated Security=SSPI;";
           
string strSQLUpdateInv = "update Production.ProductInventory"+
       " 	set Quantity = Quantity - OrderQty" +
       " 	from Sales.SalesOrderDetail a inner join" +
       " 		Production.ProductInventory b" +
       " 		on a.ProductID = b.ProductId" +
       " 		where a.SalesOrderID = 43659";

SqlConnection aConnection = new SqlConnection(connectionString);
aConnection.Open();

SqlCommand writeCommand = 
    new SqlCommand(strSQLUpdateInv, marsConnection);
    writeCommand.ExecuteNonQuery();
}
aConnection.Close();

What happened to the principle that tables should not be 'touched' directly from client code? Ok, I know this is a sample only. But why the sample has to be built around completely wrong concept? Even worse sample can be found in the Training Kit book for exam 70-442 (Desigining and Optimizing Data Access by Using SQL Server 2005). Authors unfortunately didn't bother even to use parameters in sample queries - plain concatenation of strings is being taught there. 

I found more plausible example of using MARS. It's a good article of Thiru Thangarathinam. This is usage I could imagine, although I would rather be unlikely to actually use it. In my view, there is a tier lost somewhere in multi tier architecture in this case - if a website is going to be heavy loaded, one could benefit more from a  proper cache (in business layer) than from MARS connections. 

I  am not against MARS. I believe that there are scenarios which are much simpler to implement using MARS connections than without them or on a database level. I would like to know what are these scenarios. Have you come across such use cases?