My First F# Application Part 2
In the fist part of this series, I introduced the time tracker application. In this I delved into some of the domain models. In this series I am go to look at some of the data layer of the application. It is a database layer using Donald. Remember this is my first F# application, I am learning here, my code is probably pretty crap. You will find the full source for this application as it stands at github.
Database Layer Using Donald – What is Donald?
Yes ‘Donald’. This is an SQL data access library written in F# for use in F# that is based on similar principals to the ‘Dapper’ library. Take a look at the Donald repository. You will find the design repository and the reasoning behind the name. A database layer using Donald is fairly straight forward. It requires you to set up the SQL statements and then map the results to your domain objects. I find it works fairly well and is straight forward. Once I started using it, I got the hang of it fairly quickly.
Let’s See The Code!
To get started, add the package as per the instructions on the Donald repository and then get coding. I tried to set up my database layer so that I could switch out how the database is connected to. Before we look at the database connection logic I used let’s let at data repository function to all the customers from the database. First the start of the AppDataSQL.fs file.
namespace TrackTime
open System
open System.Linq
open System.Text
open System.IO
open DataInterfaces
open System.Data
open FSharp.Data
open FirebirdSql.Data.FirebirdClient
//The building blocks of the data access - which we can unit test
module AppDataDonaldSql =
open DataModels
open DonaldI then have the database connection logic where I look up a settings file for the connections string. But I shall come back to that.
Get a Customer!
Lets look at the function to get a customer.
let private customerSelectSQl =
"SELECT a.CUSTOMER_ID, a.CUSTOMER_NAME, a.PHONE, a.EMAIL, a.CUSTOMER_STATE,
a.NOTES
FROM CUSTOMER a "
let private customerOrderByClause = "a.CUSTOMER_NAME"
let private customerFromDataReader (rd: IDataReader) : Customer =
{ CustomerId = "CUSTOMER_ID" |> rd.ReadInt64
Name = "CUSTOMER_NAME" |> rd.ReadString |> CustomerName.Create
Phone = "PHONE" |> rd.ReadStringOption |> PhoneNoOptional.Create
Email = "EMAIL" |> rd.ReadStringOption |> EmailAddressOptional.Create
CustomerState = "CUSTOMER_STATE" |> rd.ReadInt32 |> enum<CustomerState>
Notes = "NOTES" |> rd.ReadStringOption }
let getOneCustomerFromDB (getDbConnection: GetDbConnection) (id: CustomerId) =
try
use conn = getDbConnection ()
let sql = customerSelectSQl + " WHERE a.CUSTOMER_ID = @CUSTOMER_ID"
let qRes =
conn
|> Db.newCommand sql
|> Db.setParams [ "CUSTOMER_ID", SqlType.Int64 id ]
|> Db.querySingle customerFromDataReader
match qRes with
| Ok item ->
match item with
| Some m -> Ok m
| None -> $"Unable to find customer with Id {id}" |> exn |> Error
| Error dberror -> dbErrorResult $"Error occured attempting load customer with Id {id}" dberror
with
| ex -> Error exIn the above, I have passed in the getDBConnection function and the Id. For the purpose of the application this method gets curried with the default application connection function. So this function is only called directly from testing functions. I have set up the SQL in a separate let binding and the mapping in a separate function because I reuse them in my ‘get all customers’ function.
What about adding a customer?
To add a customer, the steps are very similar. We set up the SQL, then create the list of parameters, execute the sql and examine the results to get the id of the added record. Let me demonstrate:
let addCustomerToDB (getDbConnection: GetDbConnection) (model: Customer) =
try
let idOfDataReader (rd: IDataReader) : int64 = rd.ReadInt64 "CUSTOMER_ID"
let sql =
"INSERT INTO CUSTOMER (CUSTOMER_NAME, PHONE, EMAIL, CUSTOMER_STATE, NOTES)
VALUES (
@CUSTOMER_NAME,
@PHONE,
@EMAIL,
@CUSTOMER_STATE,
@NOTES
)
RETURNING CUSTOMER_ID"
use conn = getDbConnection ()
let newIdResult =
conn
|> Db.newCommand sql
|> Db.setParams [ "CUSTOMER_NAME", SqlType.String model.Name.Value
"PHONE",
match model.Phone.Value with
| Some ph -> SqlType.String ph
| None -> SqlType.Null
"EMAIL",
match model.Email.Value with
| Some eml -> SqlType.String eml
| None -> SqlType.Null
"CUSTOMER_STATE",
SqlType.Int16(
match model.CustomerState with
| CustomerState.Active -> 1s
| _ -> 0s
)
"NOTES",
match model.Notes with
| Some notes -> SqlType.String notes
| None -> SqlType.Null ]
|> Db.querySingle idOfDataReader
let res =
match newIdResult with
| Ok idOption ->
match idOption with
| Some id -> Ok id
| None -> "Error inserting and returning new customer id." |> exn |> Error
| Error dberror -> dbErrorResult "Error inserting customer and returning new id." dberror
res
with
| ex -> Error exThe principal is the same for updating and deleting etc. You can look at the full code in the github repository.
The database connection.
As promised let us look at how I set up the connection. It is no big deal. It is just normal stuff when opening up a Ado.net library returning an IDBConnecton. As stated earlier, I am using the Firebird database as my back end so I am using the Firebird database provider which is pulled in with a nuget package.
Curried Connections Anybody?
So the final joining together of this is done in AppDataService.fs. This is where I curry and partially apply all the functions together so that the main code doesn’t need to care how the database connection came about. Like a sort of F# version of IOC. I say sort, because I am sure I have done a bad job it! First of in I set up the default connectDb function as
let connectDB = connStr >> getDbConnectionWithConnStrBut getDbConnectionWithConnStr is also a putting together the function that reads the settings etc. The final currying / partical application to arrive at the interface that the main application uses is:
namespace TrackTime
//AppData is meant to be used within the application - simply substitution for the app connectDB - for testing use the ..ForDB or ..ToDB .. withDB
module AppDataService =
open AppDataDonaldSql
let addCustomer = addCustomerToDB connectDB
let addWorkItem = addWorkItemToDB connectDB
let addTimeEntry = addTimeEntryToDB connectDB
let updateCustomer = updateCustomerToDB connectDB
let updateWorkItem = updateWorkItemToDB connectDB
let updateTimeEntry = updateTimeEntryToDB connectDB
let deleteCustomer = deleteCustomerFromDB connectDB
let deleteWorkItem = deleteWorkItemFromDB connectDB
let deleteTimeEntry = deleteTimeEntryFromDB connectDB
let getOneCustomer = getOneCustomerFromDB connectDB
let getOneWorkItem = getOneWorkItemFromDB connectDB
let getOneTimeEntry = getOneTimeEntryFromDB connectDB
let getCustomers = getCustomersFromDB connectDB
//let getCustomerCount = getCustomerCountFromDB (connectDB())
let getWorkItems = getWorkItemsFromDB connectDB
//let getWorkItemCount = getWorkItemCountFromDB (connectDB())
let getTimeEntries = getTimeEntriesFromDB connectDB
//let getTimeEntryCount = getTimeEntryCountFromDB (connectDB())
let getAllUnbilledBillingSummary() = getAllUnbilledBillingSummaryFromDb connectDB
let getAllUnbilledBillingDetails() = getAllUnbilledBillingDetailsFromDb connectDBThat’s all for me today. We’ll start looking at the UI code in part 3. Till next time. Keep smilling and Keep Looking Up! Psalm 121:1,2