So far in this series, we've created a simple domain and defined what that would look like in SQL.

Today I want to demonstrate a simple insert and read using ADO.NET.

What is ADO.NET?

"ADO.NET provides the most direct method of data access within the .NET Framework."

As I'm relatively new to the .NET ecosystem, I was overwhelmed with all the data access technologies out there. A big "aha" moment for me was learning that ADO.NET is often at the heart of these other technologies.

The following code snippets assume you have a running instance of SQL Server and a published database with the tables shown in the previous blog.

Seeding data

Before we start trying to insert a Pokemon or a PokemonTrainer we need to seed our PokemonTypes into the table, due to the constraints we set up in the last post.

let getConnection () = new SqlConnection "<CONNECTION_STRING>"

module PokemonTypes =
    let seedAllPokemonTypes () =
        use conn = getConnection ()
        let sqlQuery =
            "
            INSERT INTO PokemonTypes (PokemonTypeId, PokemonTypeName) 
            VALUES (@PokemonTypeId, @PokemonTypeName)
            "
        conn.Open ()
        let types = [ Rock; Grass; Poison; Fire; Psychic; Ghost; Ice ]

        for pokemonType in types do
            use cmd = new SqlCommand (sqlQuery, conn)
            cmd.Parameters.AddWithValue("@PokemonTypeId", pokemonType.Id) |> ignore
            cmd.Parameters.AddWithValue("@PokemonTypeName", pokemonType.Serialise ()) |> ignore 
            cmd.ExecuteNonQuery () |> ignore

The following snippets follow a similar structure so let's walkthrough the above function:

• Instead of a let binding, I chose a use. This is because the getConnection function returns a SqlConnection which implements IDisposable.
• I've defined a typical SQL query that has two parameters denoted with the @.
• I then open a connection to the database.
• I define a PokemonType list which is what I use to determine what the PokemonTypeId and PokemonTypeName should be.

The PokemonType has static and normal members that act as helper functions they aren't shown for brevity

• Within the for loop I again have a use binding as SqlCommand also implements IDisposable
• This is where I then supply the two parameters that I defined in the sqlQuery (@PokemonTypeId, @PokemonTypeName)
• Finally on each iteration I execute the cmd which will insert each PokemonType into the database

As I have bound conn with use, the connection to the database will be closed when the function returns without having to explictly call conn.Close ()

With the PokemonType table now populated we can begin to insert Pokemon 🎉

Inserting Pokemon

In my app I only worry about those Pokemon which are assigned to a PokemonTrainer, hence the following insert function:

module Pokemon =

    let addPokemon (trainerId: PokemonTrainerId) (pokemon: Pokemon) =
        use conn = getConnection ()
        let pokemonSqlQuery = 
            "
            INSERT INTO Pokemon (PokeIndex, Name, EvolutionName, PokemonTrainerId, Level) 
            VALUES (@PokeIndex, @Name, @EvolutionName, @PokemonTrainerId, @Level)
            "

        use cmd = new SqlCommand (pokemonSqlQuery, conn)

        conn.Open ()

        let evolutionName = 
            match pokemon.EvolutionName with
            | Some name -> box name
            | None -> box DBNull.Value

        let pokeIndex = pokemon.PokeIndex.Serialise ()

        cmd.Parameters.AddWithValue ("@PokeIndex", pokeIndex) |> ignore
        cmd.Parameters.AddWithValue ("@Name", pokemon.Name) |> ignore
        cmd.Parameters.AddWithValue ("@EvolutionName", evolutionName) |> ignore
        cmd.Parameters.AddWithValue ("@PokemonTrainerId", trainerId.Serialise ()) |> ignore
        cmd.Parameters.AddWithValue ("@Level", pokemon.Level) |> ignore

        cmd.ExecuteNonQuery () |> ignore

        let pokemonTypeSqlQuery = 
            "
            INSERT INTO PokemonType_Pokemon (PokemonTypeId, PokeIndex) 
            VALUES (@PokemonTypeId, @PokeIndex)
            "

        pokemon.PokemonTypes
        |> List.iter (fun pokemonType ->
            use pokemonTypeCmd = new SqlCommand (pokemonTypeSqlQuery, conn)
            pokemonTypeCmd.Parameters.AddWithValue("@PokemonTypeId", pokemonType.Id) |> ignore
            pokemonTypeCmd.Parameters.AddWithValue("@PokeIndex", pokeIndex) |> ignore 
            pokemonTypeCmd.ExecuteNonQuery () |> ignore )

One of the trickiest things I learnt was how to deal with the Option type. My instinct was to just use null if the evolutionName was None.
But that returned a big fat ❌
That's when I found out that SQL has it's own null type DBNull.Value. So once I replaced null with this my function immediately worked!

Inserting a PokemonTrainer

Again following a similar pattern:

module PokemonTrainer =
    let addPokemonTrainer trainer = 
        use conn = getConnection ()
        let pokemonTrainerSqlQuery = 
            "
            INSERT INTO PokemonTrainer (Id, Name, Wins, Losses) 
            VALUES (@Id, @Name, @Wins, @Losses)
            "

        use cmd = new SqlCommand (pokemonTrainerSqlQuery, conn)

        conn.Open ()

        cmd.Parameters.AddWithValue ("@Id", (trainer.Id.Serialise ())) |> ignore
        cmd.Parameters.AddWithValue ("@Name", trainer.Name) |> ignore
        cmd.Parameters.AddWithValue ("@Wins", int trainer.Record.Wins) |> ignore
        cmd.Parameters.AddWithValue ("@Losses", int trainer.Record.Losses) |> ignore
        cmd.ExecuteNonQuery () |> ignore

        trainer.Pokemon |> List.iter (Pokemon.addPokemon trainer.Id)

My biggest learning from this "group" insert was that order matters! Moving the line...

 trainer.Pokemon |> List.iter (Pokemon.addPokemon trainer.Id)

...before inserting the PokemonTrainer values will cause an error! This will be due to the foreign key constraints that we've set up in the last post.

For example if we try to insert the Pokemon that belong to a PokemonTrainer first, the PokemonTrainerId column in the Pokemon table will complain because in that moment in time a PokemonTrainer with that Id does not exist!

Now that we're able to insert into our database the next thing is to be able to retrieve a PokemonTrainer

Reading a PokemonTrainer

There are a few ways to do this:

• Define a single query that would return us all the information we need.

  You can go one step further and put this into a SQL view!

• Have two functions, one that gets all information from the PokemonTrainer table and the other that gets the relevant Pokemon from the Pokemon table, and handle the join in F#.

• Define two queries with one SqlCommand and return multiple data sets in one go.

I chose the third as it made use of a feature on the DataReader that I had not seen before!

    let tryGetPokemonTrainer (trainerId: PokemonTrainerId) = 
        use conn = getConnection ()
        let sqlQuery = 
            "
            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
            "

        use cmd = new SqlCommand (sqlQuery, conn)

        conn.Open ()

        cmd.Parameters.AddWithValue ("@Id", trainerId.Serialise ()) |> ignore

        let reader = cmd.ExecuteReader ()

        let pokemonTrainer = 
            seq {
                while reader.Read () do  
                let name =
                    "TrainerName"
                    |> reader.GetOrdinal
                    |> reader.GetString

                let wins =
                    "Wins"
                    |> reader.GetOrdinal
                    |> reader.GetInt32
                    |> uint32

                let losses =
                    "Losses"
                    |> reader.GetOrdinal
                    |> reader.GetInt32
                    |> uint32

                let record = 
                    { 
                        Wins = wins 
                        Losses = losses
                    }

                {
                    Id = trainerId
                    Name = name
                    Record = record
                    Pokemon = []
                }
            } 
            |> Seq.tryHead 

        reader.NextResult () |> ignore   

        let pokemon = 
                seq {
                    while (reader.Read()) do 
                        let pokeIndex = 
                            "PokeIndex"
                            |> reader.GetOrdinal
                            |> reader.GetInt32
                            |> PokeIndex 

                        let pokemonName = 
                            "Name"
                            |> reader.GetOrdinal
                            |> reader.GetString 

                        let level = 
                            "Level"
                            |> reader.GetOrdinal
                            |> reader.GetInt32

                        let pokemonType = 
                            "PokemonTypeName"
                            |> reader.GetOrdinal
                            |> reader.GetString 
                            |> PokemonType.Deserialise

                        let evolutionName =
                            let canEvolve = 
                                "EvolutionName"
                                |> reader.GetOrdinal
                                |> reader.IsDBNull
                                |> not 

                            if canEvolve then
                                "EvolutionName"
                                |> reader.GetOrdinal
                                |> reader.GetString
                                |> Some 
                            else
                                None

                        { PokeIndex = pokeIndex
                          Name = pokemonName 
                          Level = level
                          PokemonTypes = [ pokemonType ]
                          EvolutionName = evolutionName}
                }
                |> Seq.groupBy (fun pokemon -> pokemon.PokeIndex)
                |> Seq.map (fun (_, pokemon) -> 
                    pokemon 
                    |> Seq.reduce (fun acc pokemon -> 
                        { acc with PokemonTypes = acc.PokemonTypes @ pokemon.PokemonTypes }
                        )
                    )
                    |> Seq.toList

        pokemonTrainer
        |> Option.map (fun trainer -> { trainer with Pokemon = pokemon })

The coolest learning here was the ability to retrieve multiple data sets in a single swoop using reader.NextResult

We had to write a lot of code to be able to do a simple insert/read and we haven't even gone through the update or delete!

ADO.NET is a powerful tool and let's you get into the low level details. I highly recommend using it for anyone new to .NET before reaching for any other data access technology. You can always create your own helper functions to avoid repetitive code but before you decide on that let's see what's coming up next in our SQL series:

We'll be looking at Donald, a simple F# interface for ADO.NET! 💪