CODEWITHSUNDEEP
c#.netsql-serversql-server-2005ado.net
Sprintstar
Sprintstar

Reputation: 8169

MultipleActiveResultSets=True or multiple connections?

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?

Upvotes: 108

Views: 116968

Answers (3)

Kev
Kev

Reputation: 119846

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'

Upvotes: 123

dmajkic
dmajkic

Reputation: 3488

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.

Upvotes: -1

Rune Grimstad
Rune Grimstad

Reputation: 36330

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

Upvotes: 25

Related Questions