In Dapper, why I get the error "The connection does not support MultipleActiveResultSets" with async / await?

async-await c# dapper foreach sql-server

Question

I'm using Dapper and have the following code. SimpleCRUD:

var test = new FallEnvironmentalCondition[] {
    new FallEnvironmentalCondition {Id=40,FallId=3,EnvironmentalConditionId=1},
    new FallEnvironmentalCondition {Id=41,FallId=3,EnvironmentalConditionId=2},
    new FallEnvironmentalCondition {Id=42,FallId=3,EnvironmentalConditionId=3}
};
test.ToList().ForEach(async x => await conn.UpdateAsync(x));

I am receiving the following error while using this code:

InvalidOperationException: The connection does not support MultipleActiveResultSets

I don't know why I amawait then why am I receiving this problem with every update.

Because I have no control over the connection string, I am unable to activate MARS.

1
13
12/3/2019 10:11:56 AM

Accepted Answer

For each item in the list, the code in question begins a Job, but it doesn't wait for each task to finish before beginning the next one. It waits for the update to finish within each Task. Try

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

What is the same as

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

When using a non-async function, you must use Wait() rather than await each job. 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
    }
8
9/11/2017 8:51:52 PM

Popular Answer

You must include an attribute.MultipleActiveResultSets to true in the connection string to enable multiple active result sets.

 "Data Source=MSSQL1;" & _  
    "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" & _  
    "MultipleActiveResultSets=True"  

Continue reading at: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow