MultipleActiveResultSets = True or multiple connections?

Question

I have some C # in which I create a reader on the connection ( ExecuteReader ), then for each line in this reader execute another command (using ExecuteNonQuery ). In this case, is it better to use MultipleActiveResultSets=True in my connection or to use multiple connections?

Answer 1

Several active result sets (MARS) have been added for this type of operation, so you do not need to open two connections at the same time so that they can read from SqlDataReader and execute additional batches.

MARS is compatible with SQL Server 2005 and later. To quote from MSDN docs:

Before introducing Multiple Active Result Sets (MARS), developers had to use either multiple connections or server cursors to solve certain scenarios.

For more details see:

MSDN Library - MARS Overview

Produced example of reading and updating data:

MSDN Library - Manipulating Data (MARS) Scroll Down to "Reading and Updating Data Using MARS"

Answer 2

This is as far as I know why the MARS reason was added, so I think you should use it.

Answer 3

The best way to check this out is to run SQLServer Profiler and see what is actually happening on the server side.

I assume that since you use ExecuteNonQuery (), this will not be better. Thus, you are not working with multiple results.