Multipleactiveresultsets=True or 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'

When should I use MultipleActiveResultSets=True when working with ASP.NET Core 3.0 and SQL Server 2019+?

Yes, MARS still have their place in modern data access frameworks because they provide the (efficient) solution of the following two major general querying problems - streaming (i.e. non buffering) (1) data with eager loaded related data collections and (2) lazy loaded related data of any type.

In both cases, executing a query is expected to provide IEnumerator<T> (or its async version) which is the object equivalent of data reader (or database forward only read only cursor). So each MoveNext{Async} should be mapped to ReadNext of the data reader and is expected provide one fully populated T, w/o buffering ahead all others. In order to achieve that, the underlying data reader must be kept open during the enumeration, and close when it is complete or aborted earlier (for instance, FirstOrDefault()) - one of the reasons IEnumerator<T> is IDisposable.

Now imagine what happens if you have lazy loading enabled. You get some entity and access some navigation property. This triggers lazy load operation, which of course needs to execute reader to get the data from the database. Since the outer reader is still open (active), w/o MARS this operation will simply fail with runtime exception. Not good. And there is nothing you or framework can do other than either buffer everything in advance (basically switching to snapshot mode) or not use lazy loading.

Let say you don't use lazy loading (it's not recommended anyway). But your entities contain related data collections and you want to eager load them. Relational database SQL provide flat result sets, i.e. does not support "nested collections" inside query result set. So how to stream such data?

There are basically two solutions.

First is based on single SQL query which contains all master + correlated table columns, and returns some sort of hybrid records where some fields apply to specific result and other are nulls. This approach is used by EF6 and EF Core 3.0+. While EF Core 1.x/2.x uses the other approach and EF Core 5.0 allows you to choose between the two. Why? Because when you have several sub collections, this tend to produce quite ineffective queries (both execution and processing the result set since it transfers a lot of unnecessary data).

Second is using separate queries - one for the main result set and one for each correlated sub collection. The idea is simple. Since usually both PKs and FKs are indexed, the database can effectively return them ordered by these columns using index (which is needed for join operations anyway), and then they can easily be merged client side by reading ahead (buffering) maximum one record.

Sounds good, isn't it? With one small, but important caveat - it needs MARS! Otherwise, it has to switch to buffering mode. Which totally defeats the idea of IEnumerator and in async version - the cancellation concept. You can see the effect of the latter in my answer to How can I abort a running EF Core Query using GetAsyncEnumerator?, and at the end the suggestion was to enable MARS.

For more info about EF Core querying, see Split queries and How Queries Work (and basically the whole Query data) sections of the official EF Core documentation.

Side note Separate connection isn't really an option, especially if one needs transaction levels like repeatable reads. MARS is providing the exact abstraction needed over connection. And AFAIK inside SP one can open simultaneously as many cursors as they want, hence not sure what's the exact problem with ADO connection layer and why MARS is considered optional feature which needs enabling, rather than just out of the box functionality. The ORM though could try utilizing separate connection behind the scenes when applicable. EF Core currently doesn't.

So to recap shortly, if you don't use lazy loading (likely) and don't have correlated collections (unlikely - one-to-many is quite common, and the correlated collection doesn't necessarily mean navigation property and Include - same applies to projections to list and similar members), then you don't need MARS. Otherwise better enable them - they are feature, so use it.

What does MultipleActiveResultSets in connection string?

According to your error message:

Keyword not supported: 'multipleactiveresultset'

and your connection string:

...database=ReactivityDB;MultipleActiveResultSet=true...

that keyword should be in plural form:

...database=ReactivityDB;MultipleActiveResultSets=true...

Getting The connection does not support MultipleActiveResultSets in a ForEach with async-await

That code starts a Task for each item in the list, but does not wait for the each task to complete before starting the next one. Inside each Task it waits for the update to complete. Try

 Enumerable.Range(1, 10).ToList().ForEach(async i => await Task.Delay(1000).ContinueWith(t => Console.WriteLine(DateTime.Now)));

Which is equivalent to

    foreach (var i in Enumerable.Range(1, 10).ToList() )
    {
        var task = Task.Delay(1000).ContinueWith(t => Console.WriteLine(DateTime.Now));
    }

If you're in a non-async method you will have to Wait(), not await each task. EG

    foreach (var i in Enumerable.Range(1, 10).ToList() )
    {
        var task = Task.Delay(1000).ContinueWith(t => Console.WriteLine(DateTime.Now));
        //possibly do other stuff on this thread
        task.Wait(); //wait for this task to complete
    }

System.Data.SqlClient and Multiple Active Result Sets (MARS )

The default value is false, per the docs SqlConnection.ConnectionString, which makes sense as MultipleActiveResultsets was a new feature in SQL Server 2005, and System.Data.SqlClient is older.

Enabling Multiple Active Result Sets using Dapper

The only slight connection Dapper has to MARS is when using the overlapped async API to issue multiple in-flight operations with ExecuteAsync, and have told it that you want to by configuring Pipelined to true on CommandDefinition. Other than that: Dapper doesn't care about MARS. If you enable it on your connection: it will be enabled; if you don't: it won't. The key thing that won't work without MARS is: using an unbuffered query (Query<T>(...buffered: false)) and then issuing additional operations inside that query. But that is usually avoidable by simply: not doing that. You can also enable MARS on your connection string, but frankly I generally advice against that.

So: Dapper has no explicit controls related to MARS except for in the case of ExecuteAsync with Pipelined enabled.

Do I need to enable Multiple Active Result Sets in app.config?

You don't need MARS for this. You only need MARS if both queries will be active at the same time. Here you are completing one batch before starting the next.

Additionally, you don't even need two ExecuteNonQuery() calls for this. You can put both of those statements into the same sql string and get this all done with one trip out to the database:

private void CreateTablesIfNotExisting()
{
    string sql =
                "IF NOT EXISTS ( "
                + " SELECT * FROM sys.Tables WHERE NAME='Vehicles')"
                + " CREATE TABLE Vehicles( "
                + " VIN varchar(20) PRIMARY KEY, "
                + " Make varchar(20), "
                + " Model varchar(20), Year int); "
               + "IF NOT EXISTS ( "
                + " SELECT * FROM sys.Tables WHERE NAME='Repairs')"
                + " CREATE TABLE Repairs( "
                + " ID int IDENTITY PRIMARY KEY, "
                + " VIN varchar(20), "
                + " Description varchar(60), "
                + " Cost money);";
    try
    {
        using(var cn=new SqlConnection(cnString))
        using(var cmd=new SqlCommand(sql,cn))
        {
            cn.Open();
            cmd.ExecuteNonQuery();
        }
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

How to enable MultipleActiveResultSets

It is really simple, just add

MultipleActiveResultSets=true;

so change, in your web.config, the connection string in this way:

connectionString="Data Source=MATT-PC\SQLEXPRESS;" + 
                 "Initial Catalog=Raise;Integrated Security=True;" + 
                 "MultipleActiveResultSets=true;"

MultipleActiveResultSets for SQL Server and VB.NET application

If all you are going to do is show a message box in a Catch, don't do it in the database code. Let the error bubble up to the user interface code and put the Try around where the method is called.

Do not declare variables without a DataType. The button code with Option Infer on sets the type of obj and body.

Private ConStr As String = "Your connection string"

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim obj = TextBox1.Text
    Dim body = TextBox2.Text
    Dim emails As New List(Of String)
    Try
        emails = getemail()
    Catch ex As Exception
        MessageBox.Show(ex.Message.ToString, "Error retrieving email list")
        Exit Sub
    End Try
    For Each email In emails
        Try
            Send_mail(email, obj, body, getattachment(email))
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error getting attachments")
        End Try
    Next
    MessageBox.Show("Traitement effectué")
End Sub

Parameters used by Sub and Function must have a DataType.

I don't know what you are doing here.

While sqLdr.Read
        mat = sqLdr.GetValue(sqLdr.GetOrdinal("MatriculeSalarie"))
 End While

Each iteration will overwrite the previous value of mat. I can only assume that you expect only a single value, in which case you can use ExecuteScalar to get the first column of the first row of the result set. Don't do anything with the data until after the connection is closed. Just get the raw data and close (End Using) the connection. Manipulate the data later.

Always use Parameters. Parameters are not treated as executable code by the database server. They are simply values. An example of executable code that could be inserted is "Drop table [USER];" where the value of a parameter belongs. Oops!

Function getemail() As List(Of String)
    Dim SQL As String = "Select EMail  FROM  [USER]  
                            WHERE EMail Is Not NULL 
                            And MatriculeSalarie Is Not NULL 
                            And [EMail] <> '' 
                            And EtatPaie = 3 
                            And BulletinDematerialise = 1;"
    Dim dt As New DataTable
    Using con As New SqlConnection("Your connection string"),
            cmd As New SqlCommand(SQL, con)
        con.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Dim strMailTo As New List(Of String)
    strMailTo = (From row As DataRow In dt.AsEnumerable
                 Select row.Field(Of String)(0)).ToList
    Return strMailTo
End Function

Function getattachment(email As String) As String()
    Dim SQL As String = "Select MatriculeSalarie  FROM [USER] WHERE [EMail]='" & email & "'"
    Dim mat As String
    Using con As New SqlConnection(ConStr),
            cmd As New SqlCommand(SQL, con)
        cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email
        con.Open()
        mat = cmd.ExecuteScalar().ToString()
    End Using
    Dim Dir As String = ConfigurationManager.AppSettings("path1").ToString
    'Your original code was fine, no need for searchPattern.
    'I added this so you could see if your search pattern was what you expected.
    Dim searchPattern = mat.Substring(1) & "*.pdf"
    Debug.Print(searchPattern) 'Appears in the Immediate window
    Dim file = IO.Directory.GetFiles(Dir, searchPattern)
    Return file
End Function

Localdb with the Configuration MultipleActiveResultSets = True

Yes, it's possible. We use LocalDB with MultipleActiveResultSets (MARS) enabled in our application at dozens of sites, both test and production. In fact, we found it absolutely necessary to enable MARS in order to successfully use Entity Framework.

Multipleactiveresultsets=True or Multiple Connections