Menu
I'm using Dapper with asp.net core and I'm trying to enable Multiple Active Result
Did anyone managed to enable MARS using Dapper?
I tried to append to the connection "MultipleActiveResultSets=True" but with no effect.
Enabling Multiple Active Result Sets
The error that I'm getting is
InvalidOperationException: The connection does not support MultipleActiveResultSets
967
Multiple Active Result Sets (MARS) is a feature that allows the execution of multiple batches on a single connection. In previous versions, only one batch could be executed at a time against a single connection. Executing multiple batches with MARS does not imply simultaneous execution of operations.
Multiple Active Result Sets (MARS) is a feature that works with SQL Server to allow the execution of multiple batches on a single connection. When MARS is enabled for use with SQL Server, each command object used adds a session to the connection.
A batch or stored procedure which starts a manual or implicit transaction when MARS is enabled must complete the transaction before the batch exits. If it does not, SQL Server rolls back all changes made by the transaction when the batch finishes.
You should enable MARS if you perform operations that require MARS, otherwise those will fail. Whether you or anyone else should write code that uses such operations is subjective.
The multiple ResultSets could either be related or unrelated. To do this, rather than making multiple round trips to the database, we can actually use Dapper to retrieve the results in one database call itself and then map the results to the desired objects in our code.
Enabling Multiple Active Result Sets. Multiple Active Result Sets (MARS) is a feature that works with SQL Server to allow the execution of multiple batches on a single connection. When MARS is enabled for use with SQL Server, each command object used adds a session to the connection.
If you're in a non-async method you will have to Wait (), not await each task. EG You need to add attribute MultipleActiveResultSets in connection string and set it to true to allow multiple active result sets.
As you can see, the code is substantially smaller than any other implementation so far. Dapper handles the multiple results query and also maps our results directly to our objects. I’m personally a big fan of [NPoco] [].
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.
70
MultipleActiveResultSets=True is an ADO.Net configuration, it has nothing to do with the Dapper, it is meant to carry out overlapped batch operation on a Single live connection, check link for more information.
In case your aim is to receive multiple result sets in a single execution, then that has nothing to do with MARS and is enabled by default. Doing it using Dapper you need to use QueryMultiple to fetch the GridReader, which can help fetch multiple result sets, check Dapper Query Multiple. In plain vanilla ADO.Net for same purpose, you need to use DataReader.NextResult for the connected architecture and Dataset for disconnected architecture for fetching multiple result sets
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
