CommandBehavior.SequentialAccess causes error with SqlHydra readers

[pimbrouwers]

CommandBehavior.SequentialAccess causes error with SqlHydra readers

[pimbrouwers]

Fantastic!

This is done and deployed to nuget as part of 6.2.2.

Thanks for your help on this!

[pimbrouwers]

Hey Jordan! Thanks for doing this.

I like the notion of sequential access being the default, since the performance gains are just to great. But I just realized that even though the underyling extension method allows for this to be configured at runtime. I never expose this in the outer API.

So here's my thought, we add two sister funcs for query and querySingle. The question is, do we fully expose the underlying Enum? Or do we assume most people want the "CommandBehaviour.Default"?

let queryConfigured (cmdBehaviour : CommandBehaviour) (map : IDataReader -> 'a) (cmd : IDbCommand) : DbResult<'a list> =
    tryDo (fun cmd ->     
        use rd = cmd.ExecReader(cmdBehaviour)
        let results = [ while rd.Read() do yield map rd ]
        rd.Close() |> ignore
        results) 
        cmd

// vs

let queryDefault (map : IDataReader -> 'a) (cmd : IDbCommand) : DbResult<'a list> = 
    tryDo (fun cmd ->     
        use rd = cmd.ExecReader(CommandBehaviour.Default)
        let results = [ while rd.Read() do yield map rd ]
        rd.Close() |> ignore
        results) 
        cmd

[JordanMarr]

That's a tough one (but aren't they all?)! 🤔

Another option could be to leave all that as-is and have a executeReader() that returns a 'TReader`:

use reader = 
   conn
   |> Db.newCommand "SELECT ..."
   |> Db.executeReader

let p = SalesLT.ProductReader(reader)
let c = SalesLT.ProductCategoryReader(reader)

return [
    while reader.Read() do
        p.Read(), 
        c.Read()
    ]

or Async:

```F#
use! reader = 
   conn
   |> Db.newCommand "SELECT ..."
   |> Db.Async.executeReader

let p = SalesLT.ProductReader(reader)
let c = SalesLT.ProductCategoryReader(reader)

return [
    while reader.Read() do
        p.Read(), 
        c.Read()
    ]

You may not like that one as much since it makes the user responsible for iterating and disposing the reader as opposed to it being handled within Donald, which is a con. One pro is that the generated readers can be initialized before the looping, as opposed to the c'tor being called each time -- which actually may not be that big of a deal either way.. IDK.

Decisions, decisions! I'll be fine for whatever you think is best for Donald. 😁

[pimbrouwers]

Indeed! It's funny you suggest that, there was originally a read func which I had thought would be useful in multi-result set cases. Perhaps I add it again? Something like:

/// Execute paramterized query and return IDataReader
let read (cmd : IDbCommand) : IDataReader =
    cmd.ExecReader(CommandBehavior.Default)

/// Execute paramterized query and return IDataReader
let read (cmd : IDbCommand) : Task<IDataReader> =
    let dbCmd = cmd :?> DbCommand
    dbCmd.ExecReaderAsync(CommandBehavior.Default)
    |> continueWith (fun rd -> rd.Result :> IDataReader)

[JordanMarr]

Oh yeah, that would provide the ideal extension point for this workflow!