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 ause
. This is because thegetConnection
function returns aSqlConnection
which implementsIDisposable
. - 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 thePokemonTypeId
andPokemonTypeName
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 asSqlCommand
also implementsIDisposable
- 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 eachPokemonType
into the database
As I have bound
conn
withuse
, the connection to the database will be closed when the function returns without having to explictly callconn.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 relevantPokemon
from thePokemon
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 to do that, in my next post I'll be looking at what I call "ADO .NET++" libraries that give you helpers and extensions on top of ADO .NET to make your life easier!