Menu
I have some C# in which I create a reader on a connection (ExecuteReader), then for every row in that reader, perform another command (with ExecuteNonQuery). In this case is it better that I use MultipleActiveResultSets=True on my connection or use multiple connections?
964
Multiple Active Result Sets (MARS) is a feature that allows the execution of multiple batches on a single connection. In previous versions, only one batch could be executed at a time against a single connection. Executing multiple batches with MARS does not imply simultaneous execution of operations.
A batch or stored procedure which starts a manual or implicit transaction when MARS is enabled must complete the transaction before the batch exits. If it does not, SQL Server rolls back all changes made by the transaction when the batch finishes.
You should enable MARS if you perform operations that require MARS, otherwise those will fail. Whether you or anyone else should write code that uses such operations is subjective.
A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size specified (100 is the default).
Multiple Active Result Sets (MARS) was added specifically for this type of operation so that you don't have to have two connections open at the same time to be able to read from a SqlDataReader AND execute additional batches.
MARS is compatible with SQL Server 2005 and above. To quote from MSDN docs:
Before the introduction of Multiple Active Result Sets (MARS), developers had to use either multiple connections or server-side cursors to solve certain scenarios.
For more info see:
MSDN Library - MARS Overview
Worked example reading and updating data:
MSDN Library - Manipulating Data (MARS) scroll down to 'Reading and Updating Data with MARS'
130
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
