MultipleActiveResultSets=True or multiple connections?

c# .net sql-server sql-server-2005 ado.net
Sprintstar picture Sprintstar · Feb 4, 2009 · Viewed 68.4k times · Source

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?

Answer

Kev picture Kev · Feb 4, 2009

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'

Related questions

Received an invalid column length from the bcp client for colid 6

I want to bulk upload csv file data to sql server 2005 from c# code but I am encountering the below error - Received an invalid column length from the bcp client for colid 6. when bulk copy write to database server

Read More
C# Equivalent of SQL Server DataTypes

For the following SQL Server datatypes, what would be the corresponding datatype in C#? Exact Numerics bigint numeric bit smallint decimal smallmoney int tinyint money Approximate Numerics float real Date and Time date datetimeoffset datetime2 smalldatetime datetime time Character Strings …

Read More
What is the equivalent of bigint in C#?

What am I supposed to use when handling a value in C#, which is bigint for an SQL Server database?

Read More