Getting QueryMultipleAsync in Dapper to work with F#

Question

We are trying to use dapper to return multiple result sets in F#. However I keep getting an Invalid operation Exception. This is what we have set up:

let query<'T, 'T1> connStr sql =
        async {
            use connection = new SqlConnection(connStr)
            do! connection.OpenAsync() |> Async.AwaitTask
            use transaction = connection.BeginTransaction()
            try
                use! result =
                    connection.QueryMultipleAsync(sql.Query, dict sql.Parameters, transaction)
                    |> Async.AwaitTask
                transaction.Commit()
                let r1 = result.Read<'T>()
                let r2 = result.Read<'T1>()
                return (r1, r2)|> Ok
            with 
            | ex ->
                transaction.Rollback()
                return ex |> DataException |> Error
        }

The expected result would be a tuple of seqs, but currently I just keep getting

System.InvalidOperationException: 'There is already an open DataReader associated with this Command which must be closed first.'

Answer 1

The issue appears to be related to calling transaction.commit before read. Here is the correct implementation for this method:

let query<'T, 'T1> connStr sql =
        async {
            use connection = new SqlConnection(connStr)
            do! connection.OpenAsync() |> Async.AwaitTask
            use transaction = connection.BeginTransaction()
            try
                use! result =
                    connection.QueryMultipleAsync(sql.Query, dict sql.Parameters, transaction)
                    |> Async.AwaitTask

                let r1 = result.Read<'T>()
                let r2 = result.Read<'T1>()

                // Move the commit after the reads
                // Was before orignally
                transaction.Commit()
                return (r1, r2)|> Ok
            with 
            | ex ->
                transaction.Rollback()
                return ex |> DataException |> Error
        }