5

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

Edgar
  • 6,022
  • 8
  • 33
  • 66
Gerald Hughes
  • 5,771
  • 20
  • 73
  • 131
  • https://stackoverflow.com/questions/46163437/getting-the-connection-does-not-support-multipleactiveresultsets-when-using-dapp Any help? – Matt Evans Aug 21 '19 at 09:28
  • @MatthewEvans thanks, I've already checked that answer. The difference is that instead of a foreach I'm using a middleware that is logging all the request and response details. I'm not sure if Task.Delay(1000) is a good solution. – Gerald Hughes Aug 21 '19 at 09:34
  • 1
    Are you able to make MARS work, it has nothing to do with Dapper but still why was it failing, why can't you enable it, did you discover, are you using Sql Server as database or something else – Mrinal Kamboj Aug 22 '19 at 04:30

2 Answers2

12

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • thanks for the clarification, I have got a [link](https://stackoverflow.com/a/511021/1559611), which explains the same concept well. The way I understood this functionality is primarily meant for connected architecture to enable execution of more queries on every unit of result fetched. On other hand it has nothing to do with Multiple result sets, which is a default behavior – Mrinal Kamboj Aug 22 '19 at 04:24
3

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

Mrinal Kamboj
  • 11,300
  • 5
  • 40
  • 74
  • Note: `GridReader` is for querying multiple **consecutive** result sets on a single operation, which is completely orthogonal to MARS; you can use either, neither, or both – Marc Gravell Aug 21 '19 at 09:49
  • @MarcGravell if a query / proc execution generates multiple consecutive result set then to fetch it we need MARS enabled and `QueryMultiple`, what is other use case beside it. Especially since multiple results can have completely different schema, so none of the other API fits in – Mrinal Kamboj Aug 21 '19 at 09:58
  • 3
    " then to fetch it we need MARS enabled" - no, completely incorrect, sorry; that is **not** what MARS means. MARS is for issuing two *overlapped* queries, i.e. you issue query A, fetch 1 row (of 96, say), and then issue another query/command B, then look at row 2 of A, etc - the key point here: you issue a second command while an existing command *is still being iterated* – Marc Gravell Aug 21 '19 at 10:14
  • @MarcGravell thanks for the clarification this means, we don't need to do anything special for the fetching multiple result sets just use the correct API, MARS enabled / disabled as no role. Is there a good example to review ? – Mrinal Kamboj Aug 21 '19 at 10:19