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 have the following code using Dapper.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));

With this code, I am getting following exception:

InvalidOperationException: The connection does not support MultipleActiveResultSets

I don't understand I am awaiting each update so why am I getting this error.

Note: I have no control on the connection string so I can't turn MARS on.

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

Accepted Answer

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
    }
8
9/11/2017 8:51:52 PM

Popular Answer

You need to add attribute MultipleActiveResultSets in connection string and set it to true to allow multiple active result sets.

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

Read more 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