MultipleActiveResultSets=True or multiple connections?


Answers

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

Question

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?




MultipleActiveResultSets=True or multiple connections?

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'




You cannot have two open DataReaders on the same connection at the same time.

A common solutions to this problem would be to load the contents of the first DataReader into a DataTable or List<> before opening the next DataReader. You could also open a new connection for the nested query.

Edit Or, as @Mithrandir mentioned, you can use the MultipleActiveResultSets=true on the connection string.




Best way to test this is to fire SQLServer Profiler, and see what really happens on the server side.

My guess is that it will not be better since you are using ExecuteNonQuery(). So, in fact, you don't work with multiple results.




why I can't use two datareader in one procedure?

I've not tried this before but it should be possible.

Read Using Multiple Active Result Sets and,
Enabling Multiple Active Result Sets on MSDN

Do note this is for SQL2005 and above.

Excerpt from one of the articles:

To access multiple result sets on previous versions of SQL Server using SqlDataReader objects, a separate SqlConnection object must be used with each SqlCommand object.




This article explains what the problem is, and introduces the solution if you are using SQL Server. The solution is called MARS, or Multiple Active Record Sets, and is available in SQL Server 2005 and later versions.




Tags