Dapper Queries F#

dapper-stuff.fsx

#r "nuget: IcedTasks"
#r "nuget: FSharp.Control.TaskSeq"
#r "nuget: Dapper"
#r "nuget: Microsoft.Data.SqlClient"

open Microsoft.Data.SqlClient
open IcedTasks
open FSharp.Control
open System
open Dapper

// Models & queries here are just for demonstration purposes

[<CLIMutable>]
type A = { Id: int }

[<CLIMutable>]
type B = { Id: int }

[<CLIMutable>]
type C = { Id: int }

let b = SqlConnectionStringBuilder()
b.DataSource <- "."
b.InitialCatalog <- "MyDatabase"
b.IntegratedSecurity <- true
b.Encrypt <- true
b.TrustServerCertificate <- true
b.MultipleActiveResultSets <- true

let aQuery = "SELECT TOP 10000 Id FROM dbo.A;"
let bQuery = "SELECT TOP 10000 Id FROM dbo.B;"
let cQuery = "SELECT TOP 10000 Id FROM dbo.C;"

let query = sprintf "%s\n%s\n%s" aQuery bQuery cQuery

// ----------------------------------------------------------------
// Query Multiple w/ Dapper
// ----------------------------------------------------------------

// Pros:
// - only one round trip to the db
// - can run multiple queries in parallel without the complexity of dealing with sql locks
// Cons:
// - need to buffer all the data in memory

let queryMultipleAsync =
    let conn = new SqlConnection(b.ToString())

    cancellableTask {
        let! ct = CancellableTask.getCancellationToken ()

        let cmdDef =
            CommandDefinition(query, cancellationToken = ct, commandTimeout = Nullable 0)

        let! results = conn.QueryMultipleAsync(cmdDef)
        let aStuff = results.Read<A>()
        let bStuff = results.Read<B>()
        let cStuff = results.Read<C>()
        return aStuff, bStuff, cStuff
    }

let (aStuff, bStuff, cStuff) =
    let cts = new Threading.CancellationTokenSource()
    queryMultipleAsync cts.Token |> Async.AwaitTask |> Async.RunSynchronously

Seq.length aStuff |> printfn "A count: %d"
Seq.length bStuff |> printfn "B count: %d"
Seq.length cStuff |> printfn "C count: %d"

// ----------------------------------------------------------------
// Query parallel with IcedTasks
// ----------------------------------------------------------------

// Pros:
// - can run multiple queries in parallel
// Cons:
// - complexity of dealing with sql locks

let queryParallel () =
    b.MultipleActiveResultSets <- true
    let conn = new SqlConnection(b.ToString())

    parallelAsync {
        let! aStuff = conn.QueryAsync<A>(aQuery) |> Async.AwaitTask
        and! bStuff = conn.QueryAsync<B>(bQuery) |> Async.AwaitTask
        and! cStuff = conn.QueryAsync<C>(cQuery) |> Async.AwaitTask
        return aStuff, bStuff, cStuff
    }

let (entities2, lists2, feeds2) = queryParallel () |> Async.RunSynchronously

Seq.length entities2 |> printfn "A count: %d"
Seq.length lists2 |> printfn "B count: %d"
Seq.length feeds2 |> printfn "C count: %d"

// ----------------------------------------------------------------
// Query unbuffered w/ Dapper & FSharp.Control.TaskSeq
// ----------------------------------------------------------------
// https://www.learndapper.com/misc/buffered-unbuffered

// Pros:
// - keeps memory minimal - while we use Seq to lazy load the data, sql will still need to store the data in memory
// Cons:
// - does make more round trips to the db

let getEntities () =
    let conn = new SqlConnection(b.ToString())

    taskSeq {
        let aStuff = conn.QueryUnbufferedAsync<A>(aQuery)

        for A in aStuff do
            // build pipeline here per item
            yield A
    }
    |> TaskSeq.toSeq

getEntities () |> Seq.length |> printfn "A stream count: %d"