In the last post of the series I went over how we could use ADO.NET to go to and from our rich F# domain. As we saw, ADO.NET provides us with the lowest level of data access in .NET.

Before we dive in, a reminder of the big picture and what we're going to cover:

Heads up 😬

I'll only be giving a small preview of what each library is capable of. My goal with this post and the next few is to help show where they sit relative to the big picture and what data access looks like using them.

Introducing ADO.NET Extensions 📯

ADO.NET Extensions are libraries that offer low-level control but provide helpful abstractions to avoid repetitive code.

We'll be exploring two libraries in this post:

Shared SQL 🤝

let pokemonTrainerQuery =
    "
    SELECT PokemonTrainer.Id, PokemonTrainer.Name as TrainerName, PokemonTrainer.Wins, PokemonTrainer.Losses 
    FROM PokemonTrainer
    WHERE PokemonTrainer.Id = @Id;

    SELECT Pokemon.PokeIndex, Name, EvolutionName, Level, PokemonTypeName 
    FROM Pokemon
    JOIN PokemonType_Pokemon ON Pokemon.PokeIndex = PokemonType_Pokemon.PokeIndex
    JOIN PokemonTypes ON PokemonType_Pokemon.PokemonTypeId = PokemonTypes.PokemonTypeId
    WHERE Pokemon.PokemonTrainerId = @Id
    "

This is the same SQL from the last post from the tryGetPokemonTrainer function

Donald 🦆

I found Donald very intuitive having just used ADO.NET. I could easily drop down into the low levels of the SqlDataReader and I wrote a lot less code!
The immediate benefit was that I no longer had to use GetOrdinal from ADO.NET and had nice helper functions on top of SqlDataReader which I would otherwise have to hand write:

ReadGuid
ReadStringOption
...
I also really liked being able to use a pipeline for building up the query - there is also an alternative solution that uses computation expressions!

let ofDataReader (rd: SqlDataReader) : PokemonTrainer =
    let trainer =
        {
            Id = "Id" |> rd.ReadGuid |> PokemonTrainerId
            Name = rd.ReadString "TrainerName"
            Record = 
                {
                    Wins = (rd.ReadInt32 "Wins") |> uint
                    Losses = (rd.ReadInt32 "Losses") |> uint
                }
            Pokemon = []
        }

    rd.NextResult () |> ignore // This is to move onto the next SQL query

    let pokemon =
        seq {
            while rd.Read () do
            {
                PokeIndex = "PokeIndex" |> rd.ReadInt32 |> PokeIndex
                Name = rd.ReadString "Name"
                EvolutionName = rd.ReadStringOption "EvolutionName"
                Level = rd.ReadInt32 "Level"
                PokemonTypes = [ "PokemonTypeName" |> rd.ReadString |> PokemonType.Deserialise ] 
            }
        }
        |> Pokemon.concatPokemonTypes // this function isn't shown but can be seen in the last post
        |> Seq.toList

    { trainer with Pokemon =  pokemon }

From the above function you can see how Donald reduces the amount of ADO.NET code you need to write through the helper functions (ReadXXX) but the moment you need to drop back (rd.NextResult ()) there's no resistance.

let tryGetPokemonTrainer (PokemonTrainerId trainerId) : DbResult<PokemonTrainer option> =
    use conn = getConnection ()
    let sql = pokemonTrainerQuery

    let param = [ "Id", SqlType.Guid trainerId ]

    conn
    |> Db.newCommand sql
    |> Db.setParams param
    |> Db.querySingle ofDataReader

Our function to tryGetPokemonTrainer is dramatically simpler now! Having split out the function that handles the SQL -> F# conversion and passing that to our Donald pipeline.

A few gotchas worth noting while I was experimenting:

I had to access values in the same order that they are selected in the query. After some digging this is because the default CommandBehaviour in Donald is SequentialAccess which is not the default in ADO.NET, this means that the rows are read as a stream so if you access a column like Wins which is the third column you can't go back to Id which is the first.
The type DbResult is just an alias for Result so all the methods that exist on the Result type can be used

DustyTables 🪑

I was excited to try this library as it was created by Zaid who has done a lot of amazing work for the F# community and is always happy to lend a helping hand!
Again giving us handy helpers that exist on the reader:

uniqueidentifier
stringOrNone

Similar to Donald, you use a pipeline to build up your SQL!

let ofDataReader (rd: RowReader) : PokemonTrainer = 
    let trainer = 
        {
            Id = "Id" |> rd.uniqueidentifier |> PokemonTrainerId
            Name = rd.string "TrainerName"
            Record = 
                {
                    Wins = (rd.int "Wins") |> uint
                    Losses = (rd.int "Losses") |> uint
                }
            Pokemon = []
        }

    rd.Reader.NextResult () |> ignore

    let pokemon = 
        ... // Have to drop back down to ADO.NET to retrieve pokemon the code is the same as: https://www.compositional-it.com/news-blog/querying-with-ado-net-and-f/

    { trainer with Pokemon = pokemon }

Again DustyTables gives us great extension methods on the reader and we can drop back to ADO.NET easily. Currently with DustyTables it is only possible to read the first result set (from the first SELECT statement).

let getPokemonTrainer (PokemonTrainerId trainerId) : PokemonTrainer = 
    let sql = pokemonTrainerQuery

    let param = [ "Id", Sql.uniqueidentifier trainerId ]

    "<connectionString>"
    |> Sql.connect
    |> Sql.query sql
    |> Sql.parameters param
    |> Sql.executeRow ofDataReader

Some differences worth noting:

With DustyTables the order in which you access values does not matter - which is the default behaviour of ADO.NET
Rather than passing a SqlConnection like we do in Donald as the first step in the pipeline we pass the connection string
Everything comes from DustyTables whether it's Sql or RowReader, which I liked as someone new to both .NET and SQL. I didn't have to worry about what types came from Microsoft.Data.SqlClient and what the library provided/expected.
The return type is no longer an Option but a PokemonTrainer

Conclusion 🎉

To wrap up, both libraries offer very similar solutions and it comes down to your preference. What both libraries do exceptionally well is handling Options on the DataReader, which for me is already worth the switch from ADO.NET! As you can see, you can easily drop back down to ADO.NET at any moment.

Next up! 🚀

Before continuing on our journey and looking at Micro ORMs like Dapper/Dapper.FSharp, I'd like to go over the difficulties we faced when mapping F# to SQL and back in some more detail. I found that this really made me appreciate the problem these libraries are trying to solve.

F# ADO.NET extensions