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 Donald

I 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 ex

In 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 ex

The 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 >> getDbConnectionWithConnStr

But 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 connectDB

That’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

Leave a Reply

Your email address will not be published. Required fields are marked *