MultipleActiveResultSets=True or multiple connections?
Solution 1
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:
Worked example reading and updating data:
MSDN Library - Manipulating Data (MARS) scroll down to 'Reading and Updating Data with MARS'
Solution 2
This is as far as I know the reason MARS was added, so yeah I think you should use it.
napster
Updated on June 06, 2020Comments
-
napster almost 4 years
I have some C# in which I create a reader on a connection (
ExecuteReader
), then for every row in that reader, perform another command (withExecuteNonQuery
). In this case is it better that I useMultipleActiveResultSets=True
on my connection or use multiple connections? -
napster about 15 yearsThat's what I thought when I wrote the code, but if I don't have MultipleActiveResultSets=True then I still get an "There is already an open DataReader associated with this Command which must be closed first." exception on ExecuteNonQuery.